Entity Framework core stored procedure

Question

I am working with EF Core (code-first), and want to grab the data from a stored procedure. The returned result should be the class I defined based on the result return.

I could make it work if the return type is one of the entities. ex.

_context.Set<Entity>().FromSql("dbo.Stored_Proc").ToList(); 

but not if my return values are not an entity in the context.

Any help would be appreciated.

Answer 1

It is pretty easy.

Here is 3 steps to do it:

1. Create model class with the same properties your SP returns e.g.

    public class SPModel
    {
        public int Id { get; set; }
        public DateTime? CreatedDateTime { get; set; }
        etc...
    }

2. Insert this model class into your dbContext class like:

    public class YourDbContext : DbContext
    {
        public virtual DbSet<SPModel> YourDbSet { get; set; }
        ....
    }

Do not map this class to any SQL tables!

3. Use it in calling SP like:

    var spModels = await dbContext.YourDbSet
        .FromSqlRaw("EXECUTE yourStoredProcedure {0},{1}", param1, param2)
        .ToListAsync();

Some useful things:

• You can use try/catch block to see if you missed some properties
  
• You can expand your model class to add new properties, but avoid 'set' accessor like: public bool IsLoaded { get; }
  
• Be careful if your SP returns some nullable type, in this case model has to have nullable type as well

Answer 2

Entity Framework Net Core 2.0: execute stored procedures and mapping result into a list of custom objects

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.

I’ve decided to make some methods to help me for the execution of the stored procedure and the object mapping of its result. If you have a stored procedure for select all the rows in a table, this is the implementation.

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

I hope that's what you need.

Answer 3

This can be achieved without defining any DbQuery or DbSet, but with the help of below extension. Efcore 3 and above

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

public static class DbContextExtensions
{
    public static IList<T> SqlQuery<T>(this DbContext context, string sql, params object[] parameters) where T : class
    {
        using (var dbcontext = new ContextForQueryType<T>(context.Database.GetDbConnection()))
        {
            return dbcontext.Set<T>().FromSqlRaw(sql, parameters).AsNoTracking().ToList();
        }
    }

    public static async Task<IList<T>> SqlQueryAsync<T>(this DbContext context, string sql, params object[] parameters) where T : class
    {
        using (var dbcontext = new ContextForQueryType<T>(context.Database.GetDbConnection()))
        {
            return await dbcontext.Set<T>().FromSqlRaw(sql, parameters).AsNoTracking().ToListAsync();
        }
    }

private class ContextForQueryType<T> : DbContext where T : class
{
    private readonly System.Data.Common.DbConnection connection;

    public ContextForQueryType(System.Data.Common.DbConnection connection)
    {
        this.connection = connection;
    }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        optionsBuilder.UseSqlServer(connection, options => options.EnableRetryOnFailure());

        base.OnConfiguring(optionsBuilder);
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<T>().HasNoKey();
        base.OnModelCreating(modelBuilder);
    }
  } 
}

and execute like this:

var param = new SqlParameter("@IdParam", SqlDbType.VarChar, 10);
param.Value = Id.ToString();

string sqlQuery = "Exec [dbo].[usp_get_custom_type] @IdParam";

await context.SqlQueryAsync<CustomType>(sqlQuery);

Answer 4

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.

My original answer - 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 !!!