Entity Framework core stored procedure

entity-framework-core stored-procedures

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.

1
3
5/21/2016 8:10:40 PM

Popular Answer

It is pretty easy.

Here is 3 steps to do it:

  1. 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..
    }

  1. 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

  1. 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

6
6/2/2017 5:11:14 PM


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