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.
It is pretty easy.
Here is 3 steps to do it:
public class SPModel { public int Id {get;set;} public DateTime? CreatedDateTime {get;set;} etc.. }
public class YourDbContext: DbContext { public virtual DbSet<SPModel> YourDbSet { get; set; } .... }
Do not to map this class to any sql tables
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