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:
When I run same function on Pgadmin , result is :
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)