Generating and accessing stored procedures using Entity framework core

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 !!!

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);

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.

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);