Issue with entity framework core 2.0 using stored proc

Question

I am using Entity Framework Core 2.0 in my WebAPI project and I have written multiple sotred procedures to perform database operations. Now for accessing the database, I am using EF Code First. Also I have defined the entities according to database tables I have. Now when I am trying to call stored procedures, I am facing issue with EF core 2.0.

 

My sotred procedures are written in a way that they are returning either subset of my database table, or related data generated using Joins. But I could not find way to call these stored procedures and get the result data using EF Core 2.0. I found that EF 2.0 does not support mapping of related data, which is not bound to some entity. Is there any workaround for it? 

Answer 1

I think that the limitation in 2.0 where the return must be a type of an entity. You can try use SqlCLient, here is an example https://blog.bitscry.com/2017/09/14/sql-stored-procedure-in-net-core/ .

 

Or Create a new Entity that match the return from the Stored Procedure, I haven't try this before, but I think it should work. Example.

var books = context.YourCustomEntityForSp
  .FromSql("EXEC GetBooksByAuthor @AuthorId" , authorId)
  .ToList();