Generating and accessing stored procedures using Entity framework core

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.

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

If you need to execute stored procedure in MySQL database from EntityFramework Core, the following code should work.

var blogTagId = 1;
var tags = await _dbContext.BlogTags.FromSqlRaw("CALL SP_GetBlogTags({0})", blogTagId).ToListAsync();