Entity Framework core stored procedure

Question

Entity Framework core stored procedure

Question

I am working with EF Core (code-first), and want to grab the data from a stored procedure. The returned result should be the class I defined based on the result return.

I could make it work if the return type is one of the entities. ex.

_context.Set<Entity>().FromSql("dbo.Stored_Proc").ToList();

but not if my return values are not an entity in the context.

Any help would be appreciated.

GSH

View more on Stack Overflow

Semionoff · Accepted Answer · 6/2/2017 5:11:14 PM

It is pretty easy.

Here is 3 steps to do it:

Create model class with the same properties your SP returns e.g.


    public class SPModel
    {
      public int Id {get;set;}
      public DateTime? CreatedDateTime {get;set;}
      etc..
    }

Insert this model class into your dbContext class like:


    public class YourDbContext: DbContext
    {
       public virtual DbSet<SPModel> YourDbSet { get; set; }
       ....
    }

Do not to map this class to any sql tables

Use it in calling SP like:

 
    var collection = await dbContext.YourDbSet.FromSql("EXECUTE yourStoredProcedure {0},{1}", param1, param2).ToListAsync();

Some useful stuffs:
- You can use try/catch block to see if you missed some properties
- You can expand your model class to add new properties, but avoid 'set' accessor like: public bool IsLoaded { get;}
- be careful if your SP returns some nullable type, in this case model has to have nullable type as well

Entity Framework core stored procedure

Question

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related