MVC 5 Entity Framework 6 执行存储过程

Question

我卡住了。我有一个已经存在的应用程序，它拥有一个极大的数据库和大量的存储过程和函数库。我想做的就是使用DbContext来执行一个存储过程并返回一组数据或映射到上下文中的某个实体。这是我在网上没有发现的神奇功能吗？请有人帮帮我。这是我目前的代码（它没有返回任何结果，结果是-1）：

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

执行该命令返回-1。我也试过类似以下的命令，但是没有成功：

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

我知道可以通过添加edmx并映射到存储过程的方式来实现，但这不是首选方法。我们的数据库包含近4.5亿条记录和近3000个存储过程和函数的库。这将是一个维护的噩梦。我是否朝着正确的方向开始了呢？Entity Framework是正确的选择吗？

- clockwiseq

---

是的 - Entity Framework 绝对是正确的选择！ - marc_s

Answer 1

哇，似乎就在我放弃之后，我不知何故找到了答案。我发现了一个极好的文章关于执行存储过程，经过阅读后，这是我的解决方案：

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

非常感谢Anuraj的出色文章！ 我解决问题的关键是首先使用SqlQuery而不是ExecuteSqlCommand，并且执行映射到我的实体模型(Contact)的方法。

- clockwiseq

Answer 2

这段代码比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  }

- Geerry Drakerama

---

很棒的帖子@Geerry Drakerama！这绝对为使用存储过程的EF增加了更多的灵活性！再次感谢。 - clockwiseq

@GeerryDrakerama，感谢您分享这段代码。现在EF可以创造奇迹了 :)DbContextExtensions是什么？您能否与我们分享其实现与通用存储库模式或存储库模式。这将对像我这样的初学者有很大帮助。 - Sandeep Kumar

明白了，这是 DbContext 类的扩展 :) - Sandeep Kumar

好的。你能否也发布一下你的 DbContextExtensions 代码？ - doekman