[asp.net-core] How to run stored procedures in Entity Framework Core?

I am using EF Core 1.0 in an ASP.NET Core App. Can you please point me to the proper way of executing stored procedures? The old method with ObjectParameters and ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction is not working.

The answer is


Create the special class according the fields in your Select query of your stored procedure. For example I will call this class ResulData

Add to context of you EF

modelBuilder.Entity<ResultData>(e =>
        {
            e.HasNoKey();
        });

And this a sample function to get data using the store procedure

public async Task<IEnumerable<ResultData>> GetDetailsData(int id, string name)
{
    var pId = new SqlParameter("@Id", id);
  var pName = new SqlParameter("@Name", name);
    return await _context.Set<ResultData>()
             .FromSqlRaw("Execute sp_GetDeailsData  @Id @Name", parameters: new[] { pId, pName })
            .ToArrayAsync();
}

I used StoredProcedureEFCore nuget package by https://github.com/verdie-g/StoredProcedureEFCore,EnterpriseLibrary.Data.NetCore,EFCor.SqlServer,EFCore.Tools

I tried DbFirst approach with {Repository pattern}.. i think so

startup.cs

ConfigureServices(IServiceCollection services){
    services.AddDbContext<AppDbContext>(opt => opt
                   .UseSqlServer(Configuration.GetConnectionString("SampleConnectionString")));
    services.AddScoped<ISomeDAL, SomeDAL>();

}
            
    public  class AppDbContext : DbContext{
public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {}
}

ISomeDAl Interface has {GetPropertiesResponse GetAllPropertiesByCity(int CityId);}

public class SomeDAL : ISomeDAL
{
     private readonly AppDbContext context;

     public SomeDAL(AppDbContext context)
         {
             this.context = context;
         }
     public  GetPropertiesResponse GetAllPropertiesByCity(int CityId)
     {
         //Create Required Objects for response 
         //wont support ref Objects through params
         context.LoadStoredProc(SQL_STATEMENT)
            .AddParam("CityID", CityId).Exec( r =>
             {
                  while (r.Read())
                  {

                       ORMapping<GenericRespStatus> orm = new  ORMapping<GenericRespStatus>();
                       orm.AssignObject(r, _Status);
                  }

                  if (r.NextResult())
                  {

                       while (r.Read())
                       {
                           Property = new Property();
                           ORMapping<Property> orm = new ORMapping<Property>();
                           orm.AssignObject(r, Property);
                           _propertyDetailsResult.Add(Property);
                       }
                  }    
           });
    return new GetPropertiesResponse{Status=_Status,PropertyDetails=_propertyDetailsResult}; 
    }
}

public class GetPropertiesResponse
{
     public GenericRespStatus Status;
     public List<Property> PropertyDetails;
     public GetPropertiesResponse()
         {
             PropertyDetails = new List<Property>();
         }
}
public class GenericRespStatus
{
     public int ResCode { get; set; }
     public string ResMsg { get; set; }
}
internal class ORMapping<T>
{
    public void AssignObject(IDataReader record, T myClass)
    {
        PropertyInfo[] propertyInfos = typeof(T).GetProperties();
        for (int i = 0; i < record.FieldCount; i++)
        {
            if (propertyInfos.Any(obj => obj.Name == record.GetName(i))) //&& record.GetValue(i) != DBNull.Value
            {
                propertyInfos.Single(obj => obj.Name == record.GetName(i)).SetValue(myClass, Convert.ChangeType(record.GetValue(i), record.GetFieldType(i)));
            }
        }
    }
}

I had a lot of trouble with the ExecuteSqlCommand and ExecuteSqlCommandAsync, IN parameters were easy, but OUT parameters were very difficult.

I had to revert to using DbCommand like so -

DbCommand cmd = _context.Database.GetDbConnection().CreateCommand();

cmd.CommandText = "dbo.sp_DoSomething";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.Add(new SqlParameter("@firstName", SqlDbType.VarChar) { Value = "Steve" });
cmd.Parameters.Add(new SqlParameter("@lastName", SqlDbType.VarChar) { Value = "Smith" });

cmd.Parameters.Add(new SqlParameter("@id", SqlDbType.BigInt) { Direction = ParameterDirection.Output });

I wrote more about it in this post.


The support for stored procedure in EF Core is similar to the earlier versions of EF Code first.

You need to create your DbContext class by inherting the DbContext class from EF. The stored procedures are executing using the DbContext.

First step is to write a method that create a DbCommand from the DbContext.

public static DbCommand LoadStoredProc(
  this DbContext context, string storedProcName)
{
  var cmd = context.Database.GetDbConnection().CreateCommand();
  cmd.CommandText = storedProcName;
  cmd.CommandType = System.Data.CommandType.StoredProcedure;
  return cmd;
}

To pass parameters to the stored procedure use the following method.

public static DbCommand WithSqlParam(
  this DbCommand cmd, string paramName, object paramValue)
{
  if (string.IsNullOrEmpty(cmd.CommandText))
    throw new InvalidOperationException(
      "Call LoadStoredProc before using this method");
  var param = cmd.CreateParameter();
  param.ParameterName = paramName;
  param.Value = paramValue;
  cmd.Parameters.Add(param);
  return cmd;
}

Finally for mapping the result into a list of custom objects use the MapToList method.

private static List<T> MapToList<T>(this DbDataReader dr)
{
  var objList = new List<T>();
  var props = typeof(T).GetRuntimeProperties();

  var colMapping = dr.GetColumnSchema()
    .Where(x => props.Any(y => y.Name.ToLower() == x.ColumnName.ToLower()))
    .ToDictionary(key => key.ColumnName.ToLower());

  if (dr.HasRows)
  {
    while (dr.Read())
    {
      T obj = Activator.CreateInstance<T>();
      foreach (var prop in props)
      {
        var val = 
          dr.GetValue(colMapping[prop.Name.ToLower()].ColumnOrdinal.Value);
          prop.SetValue(obj, val == DBNull.Value ? null : val);
      }
      objList.Add(obj);
    }
  }
  return objList;
}

Now we’re ready for execute the stored procedute with the ExecuteStoredProc method and maps it to the a List whose type that’s passed in as T.

public static async Task<List<T>> ExecuteStoredProc<T>(this DbCommand command)
{
  using (command)
  {
    if (command.Connection.State == System.Data.ConnectionState.Closed)
    command.Connection.Open();
    try
    {
      using (var reader = await command.ExecuteReaderAsync())
      {
        return reader.MapToList<T>();
      }
    }
    catch(Exception e)
    {
      throw (e);
    }
    finally
    {
      command.Connection.Close();
    }
  }
}

For example, to execute a stored procedure called “StoredProcedureName” with two parameters called “firstparamname” and “secondparamname” this is the implementation.

List<MyType> myTypeList = new List<MyType>();
using(var context = new MyDbContext())
{
  myTypeList = context.LoadStoredProc("StoredProcedureName")
  .WithSqlParam("firstparamname", firstParamValue)
  .WithSqlParam("secondparamname", secondParamValue).
  .ExecureStoredProc<MyType>();
}

If you are executing a stored procedure from Informix using EntityFrameworkCore you need to include the command EXECUTE PROCEDURE

var spresult = _informixContext.procdata.FromSql("EXECUTE PROCEDURE dummyproc ({0},{1},{2})", parameters: new[] { p0, p1,p2 }).ToList();

I'm using Entity Framework Core with my ASP.Net Core 3.x WebAPI. I wanted one of my end points just to execute a particular Stored Procedure, and this is the code I needed:

namespace MikesBank.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class ResetController : ControllerBase
    {
        private readonly MikesBankContext _context;

        public ResetController(MikesBankContext context)
        {
            _context = context;
        }

        [HttpGet]
        public async Task<ActionResult> Get()
        {
            try
            {
                using (DbConnection conn = _context.Database.GetDbConnection())
                {
                    if (conn.State != System.Data.ConnectionState.Open)
                        conn.Open();
                    var cmd = conn.CreateCommand();
                    cmd.CommandText = "Reset_Data";
                    await cmd.ExecuteNonQueryAsync();
                }
                return new OkObjectResult(1);
            }
            catch (Exception ex)
            {
                return new BadRequestObjectResult(ex.Message);
            }
        }
    }
}

Notice how I need to get my DbContext which has been injected, but I also need to Open() this connection.


Using MySQL connector and Entity Framework Core 2.0

My issue was that I was getting an exception like fx. Ex.Message = "The required column 'body' was not present in the results of a 'FromSql' operation.". So, in order to fetch rows via a stored procedure in this manner, you must return all columns for that entity type which the DBSet is associated with, even if you don't need to access all of it for your current request.

var result = _context.DBSetName.FromSql($"call storedProcedureName()").ToList(); 

OR with parameters

var result = _context.DBSetName.FromSql($"call storedProcedureName({optionalParam1})").ToList(); 

Stored procedure support is not yet (as of 7.0.0-beta3) implemented in EF7. You can track the progress of this feature using issue #245.

For now, you can do it the old fashioned way using ADO.NET.

var connection = (SqlConnection)context.Database.AsSqlServer().Connection.DbConnection;

var command = connection.CreateCommand();
command.CommandType = CommandType.StoredProcedure;
command.CommandText = "MySproc";
command.Parameters.AddWithValue("@MyParameter", 42);

command.ExecuteNonQuery();

I found this extention very usefull: StoredProcedureEFCore

Then the usage is like this

List<Model> rows = null;

ctx.LoadStoredProc("dbo.ListAll")
   .AddParam("limit", 300L)
   .AddParam("limitOut", out IOutParam<long> limitOut)
   .Exec(r => rows = r.ToList<Model>());

long limitOutValue = limitOut.Value;

ctx.LoadStoredProc("dbo.ReturnBoolean")
   .AddParam("boolean_to_return", true)
   .ReturnValue(out IOutParam<bool> retParam)
   .ExecNonQuery();

bool b = retParam.Value;

ctx.LoadStoredProc("dbo.ListAll")
   .AddParam("limit", 1L)
   .ExecScalar(out long l);

We should create a property with DbQuery not DbSet in the model for the db context like below...

public class MyContextContext : DbContext
{
    public virtual DbQuery<CheckoutInvoiceModel> CheckoutInvoice { get; set; } 
}

After than a method that can be used to return result

public async Task<IEnumerable<CheckoutInvoiceModel>> GetLabReceiptByReceiptNo(string labReceiptNo)
{
    var listing = new List<CheckoutInvoiceModel>();
    try
    {
        var sqlCommand = $@"[dbo].[Checkout_GetLabReceiptByReceiptNo] {labReceiptNo}";
        
        listing = await db.Set<CheckoutInvoiceModel>().FromSqlRaw(sqlCommand).ToListAsync();                       
                        
    }
    catch (Exception ex)
    {
        return null;
    }
    return listing;
}

From above example, we can use any one option you like.

Hope this helpful for you!


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

Nothing have to do... when you are creating dbcontext for code first approach initialize namespace below the fluent API area make list of sp and use it another place where you want.

public partial class JobScheduleSmsEntities : DbContext
{
    public JobScheduleSmsEntities()
        : base("name=JobScheduleSmsEntities")
    {
        Database.SetInitializer<JobScheduleSmsEntities>(new CreateDatabaseIfNotExists<JobScheduleSmsEntities>());
    }

    public virtual DbSet<Customer> Customers { get; set; }
    public virtual DbSet<ReachargeDetail> ReachargeDetails { get; set; }
    public virtual DbSet<RoleMaster> RoleMasters { get; set; }

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        //modelBuilder.Types().Configure(t => t.MapToStoredProcedures());

        //modelBuilder.Entity<RoleMaster>()
        //     .HasMany(e => e.Customers)
        //     .WithRequired(e => e.RoleMaster)
        //     .HasForeignKey(e => e.RoleID)
        //     .WillCascadeOnDelete(false);
    }
    public virtual List<Sp_CustomerDetails02> Sp_CustomerDetails()
    {
        //return ((IObjectContextAdapter)this).ObjectContext.ExecuteFunction<Sp_CustomerDetails02>("Sp_CustomerDetails");
        //  this.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails");
        using (JobScheduleSmsEntities db = new JobScheduleSmsEntities())
        {
           return db.Database.SqlQuery<Sp_CustomerDetails02>("Sp_CustomerDetails").ToList();

        }

    }

}

}

public partial class Sp_CustomerDetails02
{
    public long? ID { get; set; }
    public string Name { get; set; }
    public string CustomerID { get; set; }
    public long? CustID { get; set; }
    public long? Customer_ID { get; set; }
    public decimal? Amount { get; set; }
    public DateTime? StartDate { get; set; }
    public DateTime? EndDate { get; set; }
    public int? CountDay { get; set; }
    public int? EndDateCountDay { get; set; }
    public DateTime? RenewDate { get; set; }
    public bool? IsSMS { get; set; }
    public bool? IsActive { get; set; }
    public string Contact { get; set; }
}

"(SqlConnection)context" -- This type-casting no longer works. You can do: "SqlConnection context;

".AsSqlServer()" -- Does not Exist.

"command.ExecuteNonQuery();" -- Does not return results. reader=command.ExecuteReader() does work.

With dt.load(reader)... then you have to switch the framework out of 5.0 and back to 4.51, as 5.0 does not support datatables/datasets, yet. Note: This is VS2015 RC.


I tried all the other solutions but didn't worked for me. But I came to a proper solution and it may be helpful for someone here.

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


Currently EF 7 or EF Core does not support the old method of importing Stored procedures in designer and calling them directly. You can have a look at the roadmap to see what is going to be supported in the future: EF core roadmap.

So for now it is better to use SqlConnection to call stored procedures or any raw query, since you do not need the entire EF for this job. Here are two examples:

Call stored procedure that return single value. String in this case.

CREATE PROCEDURE [dbo].[Test]
    @UserName nvarchar(50)
AS
BEGIN
    SELECT 'Name is: '+@UserName;
END

Call stored procedure that return a list.

CREATE PROCEDURE [dbo].[TestList]
AS
BEGIN
    SELECT [UserName], [Id] FROM [dbo].[AspNetUsers]
END

To call these stored procedure it is better to create static class that holds all of these functions, for example, I called it DataAccess class, as follows:

public static class DataAccess

    {
        private static string connectionString = ""; //Your connection string
        public static string Test(String userName)
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("dbo.Test", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // 3. add parameter to command, which will be passed to the stored procedure
                cmd.Parameters.Add(new SqlParameter("@UserName", userName));

                // execute the command
                using (var rdr = cmd.ExecuteReader())
                {
                    if (rdr.Read())
                    {
                        return rdr[0].ToString();
                    }
                    else
                    {
                        return null;
                    }
                }
            }
        }

        public static IList<Users> TestList()
        {
            using (SqlConnection conn = new SqlConnection(connectionString))
            {
                conn.Open();

                // 1.  create a command object identifying the stored procedure
                SqlCommand cmd = new SqlCommand("dbo.TestList", conn);

                // 2. set the command object so it knows to execute a stored procedure
                cmd.CommandType = CommandType.StoredProcedure;

                // execute the command
                using (var rdr = cmd.ExecuteReader())
                {
                    IList<Users> result = new List<Users>();
                    //3. Loop through rows
                    while (rdr.Read())
                    {
                        //Get each column
                        result.Add(new Users() { UserName = (string)rdr.GetString(0), Id = rdr.GetString(1) });
                    }
                    return result;
                }
            }

        }
    }

And Users class is like this:

public class Users
{
     public string UserName { set; get; }
     public string Id { set; get; }
}

By the way you do not need to worry about the performance of opening and closing a connection for every request to sql as the asp.net is taking care of managing these for you. And I hope this was helpful.


Examples related to asp.net-core

dotnet ef not found in .NET Core 3 How to use Bootstrap 4 in ASP.NET Core ASP.NET Core - Swashbuckle not creating swagger.json file Getting value from appsettings.json in .net core .net Core 2.0 - Package was restored using .NetFramework 4.6.1 instead of target framework .netCore 2.0. The package may not be fully compatible Automatically set appsettings.json for dev and release environments in asp.net core? Get ConnectionString from appsettings.json instead of being hardcoded in .NET Core 2.0 App Unable to create migrations after upgrading to ASP.NET Core 2.0 EF Core add-migration Build Failed ASP.NET Core form POST results in a HTTP 415 Unsupported Media Type response

Examples related to asp.net-core-mvc

Getting value from appsettings.json in .net core Unable to create migrations after upgrading to ASP.NET Core 2.0 ASP.NET Core form POST results in a HTTP 415 Unsupported Media Type response ASP.NET Core Get Json Array using IConfiguration ASP.NET Core Dependency Injection error: Unable to resolve service for type while attempting to activate How do I access Configuration in any class in ASP.NET Core? How to register multiple implementations of the same interface in Asp.Net Core? ASP.NET Core Identity - get current user How to get current url in view in asp.net core 1.0 How to return HTTP 500 from ASP.NET Core RC2 Web Api?

Examples related to entity-framework-core

dotnet ef not found in .NET Core 3 How to update record using Entity Framework Core? Unable to create migrations after upgrading to ASP.NET Core 2.0 Error: the entity type requires a primary key Entity Framework Core: DbContextOptionsBuilder does not contain a definition for 'usesqlserver' and no extension method 'usesqlserver' auto create database in Entity Framework Core Entity Framework Core add unique constraint code-first How to unapply a migration in ASP.NET Core with EF Core The term "Add-Migration" is not recognized Auto-increment on partial primary key with Entity Framework Core