Postgresql stored procedure on .net Core()

.net-core entity-framework entity-framework-core postgresql

Question

I am trying to move our current SQL Server database to PostgreSQL with .net Core 2.2.

I converted all tables (constraints, index), views and stored procedures to Postgre SQL. I have keep all Dbset configuration(except for Connection string) for data models on backend.

I have a problem about running stored procedure (in Postgres) within EF Core. Stored procedure returns a view. Try to map this view to DbSet with FromSql() method from Ef Core. It was running with SQL Server before

The stored procedure in Postgre SQL:

 CREATE OR REPLACE FUNCTION public.getbyusername_vw_sys_menu_lang()
 RETURNS SETOF "VW_SYS_MENU_LANG"
 LANGUAGE sql
AS $$

    --return query
    SELECT M.* FROM "VW_SYS_MENU_LANG" M
    where M."USERNAME" = 'Admin' -- parameters will be added
    limit 10;

$$;

I got error in .net Core :

The required column 'ID' was not present in the results of a 'FromSql' operation.

 var sql = "SELECT GETBYUSERNAME_VW_SYS_MENU_LANG()";
//var sql = "SELECT * FROM VW_SYS_MENU_LANG" //it works with view
 var result1 = dbSet.FromSql(sql) //error : The required column 'ID' was not present in the results of a 'FromSql' operation.

MyDbcontext : DbContext
....
....

 protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            modelBuilder.Entity<VW_SYS_MENU_LANG>(e => { e.HasKey(t => new { t.ID, t.LANG }); });

In postgres, I can run stored procedure without any error and it return the result as expected and ID column does exist with capital characters. I have converted all tables and column names as Capital letters.

I think something should be edited on DbContext class on EF Core or Postgres stored procedure.

------------ UPDATE -------------

Confused a bit , I was using Dbeaver to create and test PostgreSQL tables , functions etc.

When I run SELECT GETBYUSERNAME_VW_SYS_MENU_LANG() function on Dbeaver it returns table with all columns and data:

Bbeaver

When I run same function on Pgadmin , result is :

enter image description here

1
1
4/26/2019 12:14:31 PM

Popular Answer

It should be

SELECT * from GETBYUSERNAME_VW_SYS_MENU_LANG()

instead of

SELECT GETBYUSERNAME_VW_SYS_MENU_LANG()

It was hard to find the differences as a rookie PostgreSQL dev.
2nd returns one column with all data, comma separated. 1st returns all columns of table(or view)

2
4/25/2019 7:27:43 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow