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.

Answer 3

The workaround we use in EF Core to execute stored procedures to get the data is by using FromSql method and you can execute stored procedure this way:

List<Employee> employees = dbcontext.Employee
                    .FromSql("GetAllEmployees").ToList();

But for Create, Update, and Delete, we use ExecuteSqlCommand like the one below:

var employee = "Harold Javier";
dbcontext.Employee
           .ExecuteSqlCommand("InsertEmployee @emp", employee);

Answer 4

My original post - https://stackoverflow.com/a/57224037/1979465

To call a stored procedure and get the result into a list of model in EF Core, we have to follow 3 steps.

Step 1. You need to add a new class just like your entity class. Which should have properties with all the columns in your SP. For example if your SP is returning two columns called Id and Name then your new class should be something like

public class MySPModel
{
    public int Id {get; set;}
    public string Name {get; set;}
}

Step 2.

Then you have to add one DbQuery property into your DBContext class for your SP.

public partial class Sonar_Health_AppointmentsContext : DbContext
{
        public virtual DbSet<Booking> Booking { get; set; } // your existing DbSets
        ...

        public virtual DbQuery<MySPModel> MySP { get; set; } // your new DbQuery
        ...
}

Step 3.

Now you will be able to call and get the result from your SP from your DBContext.

var result = await _context.Query<MySPModel>().AsNoTracking().FromSql(string.Format("EXEC {0} {1}", functionName, parameter)).ToListAsync();

I am using a generic UnitOfWork & Repository. So my function to execute the SP is

/// <summary>
/// Execute function. Be extra care when using this function as there is a risk for SQL injection
/// </summary>
public async Task<IEnumerable<T>> ExecuteFuntion<T>(string functionName, string parameter) where T : class
{
    return await _context.Query<T>().AsNoTracking().FromSql(string.Format("EXEC {0} {1}", functionName, parameter)).ToListAsync();
}

Hope it will be helpful for someone !!!

Answer 5

The solution Rohith / Harold Javier / Sami provided works. I would like to add that you can create a separate EF6 project to generate the C# classes for resultsets and then copy the files to your EFCore project. If you change a stored proc, you can update the result file using the methods discussed here: Stored Procedures and updating EDMX

If you need corresponding typescript interfaces, you can install this VS2017 extension typescript definition generator: https://marketplace.visualstudio.com/items?itemName=MadsKristensen.TypeScriptDefinitionGenerator

There are still be a couple of copying, but it is less tedious than creating the classes manually.

Edit: there is a VS2017 extension for generating the dbconext https://marketplace.visualstudio.com/items?itemName=ErikEJ.EFCorePowerTools. It does not do stored procedures, but it provides a right click menu item from VS project instead of the command line Scaffold-DbContext.