Entity Framework Core Stored Proc without DbSet

Question

I am calling a stored proc from EF Core 1.1, following the advice on https://learn.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?

Answer 1

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

Answer 2

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:

https://github.com/verdie-g/StoredProcedureEFCore

https://github.com/snickler/EFCore-FluentStoredProcedure

Please give credit to the package authors.