Generating and accessing stored procedures using Entity framework core

Question

I am implementing Asp.Net core Web API , entity framework core, database first approach using Visual Studio 2017. I have managed to generate the context and class files based on an existing database. I need to access stored procedures using my context. In earlier version of entity framework it was simple by selecting the stored procedure objects in the wizard and generating an edmx that contains those objects. I could then access stored procedures via the complex type objects exposed by entity framework. How do I do a similar thing in entity framework core. An example would help ?

Answer 1

Database first approach is not there in EF Core with edmx files.Instead you have to use Scaffold-DbContext

Install Nuget packages Microsoft.EntityFrameworkCore.Tools and Microsoft.EntityFrameworkCore.SqlServer.Design

Scaffold-DbContext "Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models 

but that will not get your stored procedures. It is still in the works,tracking issue #245

But, To execute the stored procedures, use FromSql method which executes RAW SQL queries

e.g.

var products= context.Products     .FromSql("EXECUTE dbo.GetProducts")     .ToList(); 

To use with parameters

var productCategory= "Electronics";  var product = context.Products     .FromSql("EXECUTE dbo.GetProductByCategory {0}", productCategory)     .ToList(); 

or

var productCategory= new SqlParameter("productCategory", "Electronics");  var product = context.Product     .FromSql("EXECUTE dbo.GetProductByName  @productCategory", productCategory)     .ToList(); 

There are certain limitations to execute RAW SQL queries or stored procedures.You can’t use it for INSERT/UPDATE/DELETE. if you want to execute INSERT, UPDATE, DELETE queries, use the ExecuteSqlCommand

var categoryName = "Electronics"; dataContext.Database            .ExecuteSqlCommand("dbo.InsertCategory @p0", categoryName); 

Answer 2

The above examples work fine when executing a Stored Procedure if you are expecting the result set to be the same as any object already defined. But what if you want a resultset that is not supported? According to the developers of EF Core 2, this is a feature that will come, but there is already today an easy solution.

Create the model you want to use for your output. This model will represent the output, not a table in the database.

namespace Example.EF.Model {     public class Sample     {         public int SampleID { get; set; }         public string SampleName { get; set; }     } } 

Then add to your context a new DBSet with your model:

public virtual DbSet<Sample> Sample { get; set; } 

And then do as above, and use your model for the output:

var products = _samplecontext.Sample       .FromSql($"EXEC ReturnAllSamples {id}, {startdate}, {enddate}").ToList(); 

I hope this helps anyone out.