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?
Accepted Answer
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.
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
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow