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