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?

Not possible as of EF Core 2.0 See https://github.com/aspnet/EntityFramework/issues/1862 — Smit, Aug 15 '17 at 22:26
@Smit If you want to place that in an answer I'll accept. Thanks — Bryan, Sep 13 '17 at 17:52

antoninod · Answer 1 · 2018-12-06T08:36:56.047

12

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

edited Dec 06 '18 at 08:36

answered Dec 04 '18 at 08:39

antoninod

339
3
8

To anyone reading this answer, there is a new way to do it from .NET Core 3 onwards. As pointed out by antoninod, please read SO answer from @Ivan Stoev following the link. – SamyCode Sep 22 '21 at 18:59

score 3 · Accepted Answer · answered Sep 13 '17 at 23:45

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.

score 3 · Answer 3 · answered May 28 '20 at 06:28

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.

Entity Framework Core Stored Proc without DbSet

3 Answers3

Linked