asp.net-mvc MVC 5 实体框架 6 执行存储过程

MVC 5 Entity Framework 6 Execute Stored Procedure

提问by clockwiseq

I'm stuck. I have an existing application with an extremely large database and extensive library of stored procedures and functions. All I want to do is use a DbContext to execute a stored procedure and return a set of data or map to one of the entities in the context. Is that something magical I haven't discovered on the net somewhere? Someone, anyone, please help. Here's what I've got so far (and it doesn't return anything, the result is -1):

我被困住了。我有一个现有的应用程序，它有一个非常大的数据库和大量的存储过程和函数库。我想要做的就是使用 DbContext 来执行存储过程并返回一组数据或映射到上下文中的一个实体。这是我在网上某处没有发现的神奇东西吗？有人，任何人，请帮助。这是我到目前为止所得到的（它没有返回任何东西，结果是 -1）：

var contacts = db.Database.ExecuteSqlCommand("Contact_Search @LastName, @FirstName",
    new SqlParameter("@LastName", GetDataValue(args.LastName)),
    new SqlParameter("@FirstName", GetDataValue(args.FirstName)));

Executing that returns -1. I also tried something to the effect of this with no success:

执行返回-1。我也尝试了一些没有成功的方法：

DbRawSqlQuery<Contact> data = db.Database.SqlQuery<Contact>
                                   ("EXEC Contact_Search @LastName, @FirstName",
                                       GetDataValue(args.LastName), 
                                       GetDataValue(args.FirstName));

I understand that I could add an edmx and map to a stored procedure that way, but that is not the preferred method. Again, our database contains nearly 450 million records and a library of almost 3,000 stored procedures and functions. It would be a nightmare to maintain. Am I even starting in the right direction? Is Entity Framework the right choice?

我知道我可以通过这种方式添加 edmx 并映射到存储过程，但这不是首选方法。同样，我们的数据库包含近 4.5 亿条记录和一个包含近 3,000 个存储过程和函数的库。维持下去将是一场噩梦。我什至朝着正确的方向开始了吗？实体框架是正确的选择吗？

回答by clockwiseq

Wow, it seems right after I give up, I somehow stumble upon the answer. I found a FANTASTIC postabout executing stored procedures and after reading up, this was my solution:

哇，似乎就在我放弃之后，我不知何故偶然发现了答案。我找到了一篇关于执行存储过程的精彩帖子，阅读后，这是我的解决方案：

var contacts = db.Database.SqlQuery<Contact>("Contact_Search @LastName, @FirstName",

So, many thanks to Anuraj for his excellent post! The key to my solution was to first use SqlQueryinstead of ExecuteSqlCommand, and also to execute the method mapping to my entity model (Contact).

所以，非常感谢 Anuraj 的出色帖子！我的解决方案的关键是首先使用SqlQuery而不是ExecuteSqlCommand，并且还要执行映射到我的实体模型 ( Contact) 的方法。

回答by Geerry Drakerama

This code is better than SqlQuery() because SqlQuery() doesn't recognise the [Column] attribute. Here it is on a silver platter.

此代码比 SqlQuery() 更好，因为 SqlQuery() 无法识别 [Column] 属性。这是在一个银盘子上。

public static class StoredProcedureExtensions {   
/// <summary>
/// Execute Stored Procedure and return result in an enumerable object.
/// </summary>
/// <typeparam name="TEntity">Type of enumerable object class to return.</typeparam>
/// <param name="commandText">SQL query.</param>
/// <param name="parameters">SQL parameters.</param>
/// <param name="readOnly">Determines whether to attach and track changes for saving. Defaults to true and entities will not be tracked and thus a faster call.</param>
/// <returns>IEnumerable of entity type.</returns>
public static IEnumerable<TEntity> GetStoredProcedureResults<TEntity>(this DbContext dbContext, string query, Dictionary<string, object> parameters, bool readOnly = true) where TEntity : class, new()
{
  SqlParameter[] sqlParameterArray = DbContextExtensions.DictionaryToSqlParameters(parameters);

  return dbContext.GetStoredProcedureResults<TEntity>(query, sqlParameterArray, readOnly);
}

/// <summary>
/// Execute Stored Procedure and return result in an enumerable object.
/// </summary>
/// <typeparam name="TEntity">Type of enumerable object class to return.</typeparam>
/// <param name="commandText">SQL query.</param>
/// <param name="parameters">SQL parameters.</param>
/// <param name="readOnly">Determines whether to attach and track changes for saving. Defaults to true and entities will not be tracked and thus a faster call.</param>
/// <returns>IEnumerable of entity type.</returns>
public static IEnumerable<TEntity> GetStoredProcedureResults<TEntity>(this DbContext dbContext, string commandText, SqlParameter[] sqlParameterArray = null, bool readOnly = true) where TEntity : class, new()
{
  string infoMsg = commandText;
  try
  {
    //---- For a better error message
    if (sqlParameterArray != null)
    {
      foreach (SqlParameter p in sqlParameterArray)
      {
        infoMsg += string.Format(" {0}={1}, ", p.ParameterName, p.Value == null ? "(null)" : p.Value.ToString());
      }
      infoMsg = infoMsg.Trim().TrimEnd(',');
    }

    ///////////////////////////
    var reader = GetReader(dbContext, commandText, sqlParameterArray, CommandType.StoredProcedure);
    ///////////////////////////

    ///////////////////////////
    List<TEntity> results = GetListFromDataReader<TEntity>(reader);
    ///////////////////////////

    if(readOnly == false)
    {
      DbSet entitySet = dbContext.Set<TEntity>(); // For attaching the entities so EF can track changes
      results.ForEach(n => entitySet.Attach(n));  // Add tracking to each entity
    }

    reader.Close();
    return results.AsEnumerable();
  }
  catch (Exception ex)
  {
    throw new Exception("An error occurred while executing GetStoredProcedureResults(). " + infoMsg + ". Check the inner exception for more details.\r\n" + ex.Message, ex);
  }
}

//========================================= Private methods
#region Private Methods

private static DbDataReader GetReader(DbContext dbContext, string commandText, SqlParameter[] sqlParameterArray, CommandType commandType)
{
  var command = dbContext.Database.Connection.CreateCommand();
  command.CommandText = commandText;
  command.CommandType = commandType;
  if (sqlParameterArray != null) command.Parameters.AddRange(sqlParameterArray);

  dbContext.Database.Connection.Open();
  var reader = command.ExecuteReader(CommandBehavior.CloseConnection);
  return reader;
}

private static List<TEntity> GetListFromDataReader<TEntity>(DbDataReader reader) where TEntity : class, new()
{
  PropertyInfo[]                entityProperties = typeof(TEntity).GetProperties();
  IEnumerable<string>           readerColumnNames = (reader.GetSchemaTable().Select()).Select(r => r.ItemArray[0].ToString().ToUpper()); // uppercase reader column names. 
  List<MappingPropertyToColumn> propertyToColumnMappings = GetPropertyToColumnMappings<TEntity>(); // Maps the entity property names to the corresponding names of the columns in the reader

  var entityList = new List<TEntity>(); // Fill this
  while (reader.Read())
  {
    var element = Activator.CreateInstance<TEntity>();
    foreach (var entityProperty in entityProperties)
    {
      MappingPropertyToColumn mapping = propertyToColumnMappings._Find(entityProperty.Name);
      if (mapping == null) // This property has a [Not Mapped] attribute
      {
        continue; // Skip this one
      }

      var o = (object)reader[mapping.ColumnName]; // mapping must match all mapped properties to columns. If result set does not contain a column, then throw error like EF would.

      bool hasValue = o.GetType() != typeof(DBNull);

      if (mapping.IsEnum && hasValue) // Enum
      {
        entityProperty.SetValue(element, Enum.Parse(mapping.UnderlyingType, o.ToString()));
      }
      else
      {
        if (hasValue)
        { 
          entityProperty.SetValue(element, ChangeType(o, entityProperty.PropertyType)); 
        }
      }
    }
    entityList.Add(element);
  }

  return entityList;
}

public static object ChangeType(object value, Type conversion)
{
  var t = conversion;

  if (t.IsGenericType && t.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
  {
    if (value == null)
    {
      return null;
    }

    t = Nullable.GetUnderlyingType(t);
  }

  return Convert.ChangeType(value, t);
}

private static List<MappingPropertyToColumn> GetPropertyToColumnMappings<TEntity>() where TEntity : new()
{
  var type = typeof(TEntity);
  List<MappingPropertyToColumn> databaseMappings = new List<MappingPropertyToColumn>();

  foreach (var entityProperty in type.GetProperties())
  {
    bool isEnum = entityProperty.PropertyType.IsEnum;

    // [Not Mapped] Not Mapped Attribute
    var notMapped = entityProperty.GetCustomAttributes(false).FirstOrDefault(attribute => attribute is NotMappedAttribute);
    if (notMapped != null) // This property has a [Not Mapped] attribute
    {
      continue; // Skip this property 
    }

    // Determine if property is an enum
    Type underlyingType = null;
    if (entityProperty.PropertyType.IsGenericType && entityProperty.PropertyType.GetGenericTypeDefinition().Equals(typeof(Nullable<>)))
    {
      underlyingType = Nullable.GetUnderlyingType(entityProperty.PropertyType); ;
      if (underlyingType != null && underlyingType.IsEnum)
      {
        isEnum = true;
      }
    }

    // [Column("tbl_columnname")] Column Name Attribute for mapping
    var columnMapping = entityProperty.GetCustomAttributes(false).FirstOrDefault(attribute => attribute is ColumnAttribute);

    if (columnMapping != null)
    {
      databaseMappings.Add(new MappingPropertyToColumn { PropertyName = entityProperty.Name, ColumnName = ((ColumnAttribute)columnMapping).Name.ToUpper(), IsEnum = isEnum, UnderlyingType = underlyingType }); // SQL case insensitive
    }
    else
    {
      databaseMappings._AddProperty(entityProperty.Name, entityProperty.Name, isEnum); // C# case sensitive
    }
  }

  return databaseMappings;
}

//====================================== Class for holding column mappings and other info for each property
private class MappingPropertyToColumn
{
  private string _propertyName;
  public string PropertyName
  {
    get { return _propertyName; }
    set { _propertyName = value; }
  }

  private string _columnName;
  public string ColumnName
  {
    get { return _columnName; }
    set { _columnName = value; }
  }

  private bool _isNullableEnum;
  public bool IsEnum
  {
    get { return _isNullableEnum; }
    set { _isNullableEnum = value; }
  }

  private Type _underlyingType;
  public Type UnderlyingType
  {
    get { return _underlyingType; }
    set { _underlyingType = value; }
  }

}

//======================================================= List<MappingPropertyToColumn> Extension methods
#region List<MappingPropertyToColumn> Extension methods
private static bool _ContainsKey<T>(this List<T> list, string key) where T : MappingPropertyToColumn
{
  return list.Any(x => x.PropertyName == key);
}
private static MappingPropertyToColumn _Find<T>(this List<T> list, string key) where T : MappingPropertyToColumn
{
  return list.Where(x => x.PropertyName == key).FirstOrDefault();
}
private static void _AddProperty<T>(this List<T> list, string propertyName, string columnName, bool isEnum, Type underlyingType = null) where T : MappingPropertyToColumn
{
  list.Add((T)new MappingPropertyToColumn { PropertyName = propertyName, ColumnName = columnName, IsEnum = isEnum, UnderlyingType = underlyingType }); // C# case sensitive
}
#endregion

#endregion  }