I am calling a stored proc from EF Core 1.1, following the advice on
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?
3 Answers
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:
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.
I was stuck in the same problem, having to add DTOs as DbSets when using Entity Framework Core with stored procedures that returns nested objects. After some research, I found these packages:
Please give credit to the package authors.
