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?

Do you have a using Microsoft.EntityFrameworkCore.Infrastructure statement? — stuartd
– stuartd, Commented Aug 24, 2021 at 15:23
Yes but it is useless, I tried this _context.Cities.FromSqlRaw and no error but since Cities is a table from Context and I do not want to use a table for mapping, just a custom DTO. SInce I do not have a DTO so I used .Database. Maybe I do now know what I am doing. — EthernalHusky
– EthernalHusky, Commented Aug 24, 2021 at 15:32

Serge · Accepted Answer · 2021-08-24 16:27:19Z

4

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();

edited Aug 24, 2021 at 16:27

answered Aug 24, 2021 at 15:42

Serge

44.4k4 gold badges26 silver badges53 bronze badges

Sign up to request clarification or add additional context in comments.

7 Comments

EthernalHusky Over a year ago

gives me this error 'Cannot create a DbSet for 'SpCityResult' because this type is not included in the model for the context.' and I included it int he dbcontext, maybe it is bout not mapped issue?

Serge Over a year ago

@Bluares Try to remove [NotMapped] and let me know the result. and add to db context modelBuilder.Entity<SpCityResult>().HasNoKey().ToView(null);

EthernalHusky Over a year ago

ok ready, worked after trying to figure some stuff out and ur solution worked good.

EthernalHusky Over a year ago

.HasNoKey prevents creating table to Database right?

Pavel Stehule Over a year ago

@Bluares - when you call function that returns table, then usually call it from from clause should be preferred: select * from public.show()

|

Collectives™ on Stack Overflow

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

1 Answer 1

7 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

7 Comments

Your Answer

Sign up or log in

Post as a guest

Related