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.
Popular Answer
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
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow