Entity Framework Core Stored Proc without DbSet

entity-framework entity-framework-core

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?

1
3
8/15/2017 5:37:13 PM

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.

3
9/13/2017 11:45:42 PM

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





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow