How to get results from a Postgresql function using EF Core?

Question

My issue is very very very simple but I cannot accomplish this with EF Core using ASP.NET Core MVC. I am just trying to query using raw sql with EF core and return a set of rows.

I have a function created in my Postgresql called show() (for testing purposes)

I created the function with this code:

CREATE OR REPLACE FUNCTION public.show()
    RETURNS SETOF city
    LANGUAGE 'plpgsql'
    VOLATILE
    PARALLEL UNSAFE
    COST 100    ROWS 1000 
    
AS $BODY$
BEGIN
       RETURN QUERY SELECT * FROM City;                                                     
 END;
$BODY$;

I have this in my C# Code:

var listOfCities = _context.Database.FromSqlRaw("SELECT public.show()").ToList();

Gives me error in the part .FromSqlRaw:

'DatabaseFacade' does not contain a definition for 'FromSqlRaw' and no accessible extension method 'FromSqlRaw' accepting a first argument of type 'DatabaseFacade' could be found (are you missing a using directive or an assembly reference?)

-I do not want to use any DbContext since it is a custom query that can return any object from a function.

• I know this can be accomplished just by using LINQ _context.Cities.ToList() but I am trying to test and learn how to use functions using raw sql WITH parameters and no parameters.

How do I solve this? can this be accomplished with EF core?

Answer 1

You can not use dbcontext.Database.FromSqlRaw since FromSqlRaw returns item list, you can only use Database.ExecuteSqlCommand with database.

You will have to create a special DTO for your function result, it should include ALL data properties that are selected in your db function

public class SpCityResult
{
        public int Id { get; set; }
      
        public string Name { get; set; }
       
         ....... and so on

     
}

All properties in this class should be the same name and type as it is in your function

add SpCityResult to db context

public virtual DbSet<SpCityResult> SpCityResults { get; set; }
......

modelBuilder.Entity<SpCityResult>().HasNoKey().ToView(null);

run code

var cities= _context.SpCityResults.FromSqlRaw("SELECT public.show()").ToList();

if your existing City model has the same properties as db function, you don't need to create an extra DTO.

you can try this

var cities= _context.Cities.FromSqlRaw("SELECT public.show()").ToList();