Entity Framework Core Stored Proc without DbSet

Question

Entity Framework Core Stored Proc without DbSet

Question

I am calling a stored proc from EF Core 1.1, following the advice on https://docs.microsoft.com/en-us/ef/core/querying/raw-sql

But I created a class specifically that matches the shape of data returned from the stored proc

List<MyStoredProcResultType> results = context.MyStoredProcResultType
    .FromSql("EXECUTE dbo.MyStoredProc {0}", someParam)
    .ToList();

But this means I have to create a DbSet for MyStoredProcResultType in the Context and now it is legal to code this

context.MyStoredProcResultType.Where(..)

but of course this does not work.

Is there a way to call the stored proc against the Context rather than a DbSet and put the results into a type I create?

Bryan

Popular Answer

Since EF Core 2.1 you can use Query Types

You have to register your result class as a Query type this way:

modelBuilder.Query<MyStoredProcResultType>();

Then, simply use:

var results = Context.Query<MyStoredProcResultType>()
                     .FromSql("EXECUTE dbo.MyStoredProc {0}", someParam)
                     .ToList()

Credits go to @Ivan Stoev for this answer: https://stackoverflow.com/a/52003734/8358565

antoninod

View more on Stack Overflow

Smit · Accepted Answer · 9/13/2017 11:45:42 PM

Presently, in EF Core 2.0 RTM version, FromSql can be used only with the types which are defined as EntityType in your model. The restriction is there because, EF Core has metadata about the EntityType and knows how to materialize them. So for any other type (like custom DTO/result type), FromSql does not work.

This is tracking issue on EF Core GitHub repository which is tracking exactly what you are looking for.

EF Core 2.1.0 is planning to add some support for querying views. Tracking issue. Which could enable scenario like yours, if you are ok with letting EF Core know about the custom type you want to use during model building. It wouldn't mapped as EntityType/Table but EF Core would still compute metadata around it to support queries like above.

Entity Framework Core Stored Proc without DbSet

Question

Fastest Entity Framework Extensions

Accepted Answer

Popular Answer

Performance

Expression Evaluator

Related