我希望通过Entity Framework在表中插入20000条记录，但需要2分钟左右。除了使用存储过程之外，是否有其他方法来提高性能？这是我的代码：
foreach (Employees item in sequence)
{
t = new Employees ();
t.Text = item.Text;
dataContext.Employees.AddObject(t);
}
dataContext.SaveChanges();
如果您正在使用DbContext，那么有几个改进的机会：
设置：
yourContext.Configuration.AutoDetectChangesEnabled = false;
yourContext.Configuration.ValidateOnSaveEnabled = false;
对于插入操作，建议使用每组100个保存的方式 SaveChanges()... 或者您可以尝试使用每组1000个条目，并查看性能变化。
由于在所有这些插入过程中，上下文是相同的并且正在变得越来越大，因此您可以每1000次插入重建一次上下文对象 var yourContext = new YourContext(); 我认为这是一个很大的收获。
通过在我的数据导入流程中进行这些改进，将时间从7分钟缩短到6秒。
实际数字可能不是100或1000，您可以尝试并进行微调。
以这种方式进行操作，没有办法强制EF提高性能。问题是EF会在单独的数据库往返中执行每个插入操作。太棒了吧？甚至DataSet都支持批处理。查看这篇文章获取一些解决方法。另一个解决方法可以是使用自定义存储过程来接收表值参数，但这需要使用原始的ADO.NET。
context中获取连接对象，并使用SQLBulkCopy对象加载数据。 - Zac Howlandpublic partial class MyEntities
{
public void BulkInsertAll<T>(T[] entities) where T : class
{
var conn = (SqlConnection)Database.Connection;
conn.Open();
Type t = typeof(T);
Set(t).ToString();
var objectContext = ((IObjectContextAdapter)this).ObjectContext;
var workspace = objectContext.MetadataWorkspace;
var mappings = GetMappings(workspace, objectContext.DefaultContainerName, typeof(T).Name);
var tableName = GetTableName<T>();
var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = tableName };
// Foreign key relations show up as virtual declared
// properties and we want to ignore these.
var properties = t.GetProperties().Where(p => !p.GetGetMethod().IsVirtual).ToArray();
var table = new DataTable();
foreach (var property in properties)
{
Type propertyType = property.PropertyType;
// Nullable properties need special treatment.
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
propertyType = Nullable.GetUnderlyingType(propertyType);
}
// Since we cannot trust the CLR type properties to be in the same order as
// the table columns we use the SqlBulkCopy column mappings.
table.Columns.Add(new DataColumn(property.Name, propertyType));
var clrPropertyName = property.Name;
var tableColumnName = mappings[property.Name];
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(clrPropertyName, tableColumnName));
}
// Add all our entities to our data table
foreach (var entity in entities)
{
var e = entity;
table.Rows.Add(properties.Select(property => GetPropertyValue(property.GetValue(e, null))).ToArray());
}
// send it to the server for bulk execution
bulkCopy.BulkCopyTimeout = 5 * 60;
bulkCopy.WriteToServer(table);
conn.Close();
}
private string GetTableName<T>() where T : class
{
var dbSet = Set<T>();
var sql = dbSet.ToString();
var regex = new Regex(@"FROM (?<table>.*) AS");
var match = regex.Match(sql);
return match.Groups["table"].Value;
}
private object GetPropertyValue(object o)
{
if (o == null)
return DBNull.Value;
return o;
}
private Dictionary<string, string> GetMappings(MetadataWorkspace workspace, string containerName, string entityName)
{
var mappings = new Dictionary<string, string>();
var storageMapping = workspace.GetItem<GlobalItem>(containerName, DataSpace.CSSpace);
dynamic entitySetMaps = storageMapping.GetType().InvokeMember(
"EntitySetMaps",
BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance,
null, storageMapping, null);
foreach (var entitySetMap in entitySetMaps)
{
var typeMappings = GetArrayList("TypeMappings", entitySetMap);
dynamic typeMapping = typeMappings[0];
dynamic types = GetArrayList("Types", typeMapping);
if (types[0].Name == entityName)
{
var fragments = GetArrayList("MappingFragments", typeMapping);
var fragment = fragments[0];
var properties = GetArrayList("AllProperties", fragment);
foreach (var property in properties)
{
var edmProperty = GetProperty("EdmProperty", property);
var columnProperty = GetProperty("ColumnProperty", property);
mappings.Add(edmProperty.Name, columnProperty.Name);
}
}
}
return mappings;
}
private ArrayList GetArrayList(string property, object instance)
{
var type = instance.GetType();
var objects = (IEnumerable)type.InvokeMember(property, BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance, null, instance, null);
var list = new ArrayList();
foreach (var o in objects)
{
list.Add(o);
}
return list;
}
private dynamic GetProperty(string property, object instance)
{
var type = instance.GetType();
return type.InvokeMember(property, BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance, null, instance, null);
}
}
这个版本适用于 EF 6。
public partial class CMLocalEntities
{
public void BulkInsertAll<T>(T[] entities) where T : class
{
var conn = (SqlConnection)Database.Connection;
conn.Open();
Type t = typeof(T);
Set(t).ToString();
var objectContext = ((IObjectContextAdapter)this).ObjectContext;
var workspace = objectContext.MetadataWorkspace;
var mappings = GetMappings(workspace, objectContext.DefaultContainerName, typeof(T).Name);
var tableName = GetTableName<T>();
var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = tableName };
// Foreign key relations show up as virtual declared
// properties and we want to ignore these.
var properties = t.GetProperties().Where(p => !p.GetGetMethod().IsVirtual).ToArray();
var table = new DataTable();
foreach (var property in properties)
{
Type propertyType = property.PropertyType;
// Nullable properties need special treatment.
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
propertyType = Nullable.GetUnderlyingType(propertyType);
}
// Since we cannot trust the CLR type properties to be in the same order as
// the table columns we use the SqlBulkCopy column mappings.
table.Columns.Add(new DataColumn(property.Name, propertyType));
var clrPropertyName = property.Name;
var tableColumnName = mappings[property.Name];
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(clrPropertyName, tableColumnName));
}
// Add all our entities to our data table
foreach (var entity in entities)
{
var e = entity;
table.Rows.Add(properties.Select(property => GetPropertyValue(property.GetValue(e, null))).ToArray());
}
// send it to the server for bulk execution
bulkCopy.BulkCopyTimeout = 5*60;
bulkCopy.WriteToServer(table);
conn.Close();
}
private string GetTableName<T>() where T : class
{
var dbSet = Set<T>();
var sql = dbSet.ToString();
var regex = new Regex(@"FROM (?<table>.*) AS");
var match = regex.Match(sql);
return match.Groups["table"].Value;
}
private object GetPropertyValue(object o)
{
if (o == null)
return DBNull.Value;
return o;
}
private Dictionary<string, string> GetMappings(MetadataWorkspace workspace, string containerName, string entityName)
{
var mappings = new Dictionary<string, string>();
var storageMapping = workspace.GetItem<GlobalItem>(containerName, DataSpace.CSSpace);
dynamic entitySetMaps = storageMapping.GetType().InvokeMember(
"EntitySetMaps",
BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance,
null, storageMapping, null);
foreach (var entitySetMap in entitySetMaps)
{
var typeMappings = GetArrayList("EntityTypeMappings", entitySetMap);
dynamic typeMapping = typeMappings[0];
dynamic types = GetArrayList("Types", typeMapping);
if (types[0].Name == entityName)
{
var fragments = GetArrayList("MappingFragments", typeMapping);
var fragment = fragments[0];
var properties = GetArrayList("AllProperties", fragment);
foreach (var property in properties)
{
var edmProperty = GetProperty("EdmProperty", property);
var columnProperty = GetProperty("ColumnProperty", property);
mappings.Add(edmProperty.Name, columnProperty.Name);
}
}
}
return mappings;
}
private ArrayList GetArrayList(string property, object instance)
{
var type = instance.GetType();
var objects = (IEnumerable)type.InvokeMember(
property,
BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance, null, instance, null);
var list = new ArrayList();
foreach (var o in objects)
{
list.Add(o);
}
return list;
}
private dynamic GetProperty(string property, object instance)
{
var type = instance.GetType();
return type.InvokeMember(property, BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance, null, instance, null);
}
}
partial class MyDataContext
{
partial void OnCreated()
{
CommandTimeout = 5 * 60;
}
public void BulkInsertAll<T>(IEnumerable<T> entities)
{
entities = entities.ToArray();
string cs = Connection.ConnectionString;
var conn = new SqlConnection(cs);
conn.Open();
Type t = typeof(T);
var tableAttribute = (TableAttribute)t.GetCustomAttributes(
typeof(TableAttribute), false).Single();
var bulkCopy = new SqlBulkCopy(conn) {
DestinationTableName = tableAttribute.Name };
var properties = t.GetProperties().Where(EventTypeFilter).ToArray();
var table = new DataTable();
foreach (var property in properties)
{
Type propertyType = property.PropertyType;
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
propertyType = Nullable.GetUnderlyingType(propertyType);
}
table.Columns.Add(new DataColumn(property.Name, propertyType));
}
foreach (var entity in entities)
{
table.Rows.Add(properties.Select(
property => GetPropertyValue(
property.GetValue(entity, null))).ToArray());
}
bulkCopy.WriteToServer(table);
conn.Close();
}
private bool EventTypeFilter(System.Reflection.PropertyInfo p)
{
var attribute = Attribute.GetCustomAttribute(p,
typeof (AssociationAttribute)) as AssociationAttribute;
if (attribute == null) return true;
if (attribute.IsForeignKey == false) return true;
return false;
}
private object GetPropertyValue(object o)
{
if (o == null)
return DBNull.Value;
return o;
}
}
您的代码存在两个主要性能问题：
使用Add方法
每添加一个实体，Add方法就会变得越来越慢。
参见：http://entityframework.net/improve-ef-add-performance
例如，通过以下方式添加10,000个实体：
注意：实体尚未保存在数据库中！
问题在于，Add方法在每个添加的实体上都尝试进行DetectChanges，而AddRange则在所有实体添加到上下文后只执行一次。
常见解决方案包括：
使用SaveChanges方法
Entity Framework并不适用于批量操作。对于每个要保存的实体，都会执行一次数据库往返。
因此，如果要插入20,000条记录，则会执行20,000次数据库往返，这是疯狂的！
有一些支持批量插入的第三方库可用：
参见：Entity Framework Bulk Insert library
注意，在选择批量插入库时要小心。只有Entity Framework Extensions支持所有类型的关联和继承，并且它是唯一仍受支持的库。
免责声明：我是Entity Framework Extensions的所有者
此库允许您执行所需的所有批量操作：
示例
// Easy to use
context.BulkSaveChanges();
// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);
// Perform Bulk Operations
context.BulkDelete(customers);
context.BulkInsert(customers);
context.BulkUpdate(customers);
// Customize Primary Key
context.BulkMerge(customers, operation => {
operation.ColumnPrimaryKeyExpression =
customer => customer.Code;
});
编辑：在评论中回答问题
你创建的库是否有推荐的最大批量插入大小？
不要太高，也不要太低。没有适合所有场景的特定值，因为它取决于多个因素，如行大小、索引、触发器等。
通常建议在4000左右。
还有一种方法可以将其全部绑定在一个事务中而不必担心超时吗？
您可以使用Entity Framework（实体框架）事务。如果启动了事务，我们的库将使用该事务。但是，请注意，需要太长时间的事务也可能带来一些问题，例如某些行/索引/表锁定。
目前还没有更好的方法，但将SaveChanges放在循环内部可能会为大约10个项目带来微小的改进。
int i = 0;
foreach (Employees item in sequence)
{
t = new Employees ();
t.Text = item.Text;
dataContext.Employees.AddObject(t);
// this will add max 10 items together
if((i % 10) == 0){
dataContext.SaveChanges();
// show some progress to user based on
// value of i
}
i++;
}
dataContext.SaveChanges();
在具有1个实例的基本网站的Azure环境中。 我尝试使用for循环插入1000条记录，总共有25000条记录。串行执行需要11.5分钟，但并行执行只需要不到一分钟。因此，我建议使用TPL（任务并行库）。
var count = (you collection / 1000) + 1;
Parallel.For(0, count, x =>
{
ApplicationDbContext db1 = new ApplicationDbContext();
db1.Configuration.AutoDetectChangesEnabled = false;
var records = members.Skip(x * 1000).Take(1000).ToList();
db1.Members.AddRange(records).AsParallel();
db1.SaveChanges();
db1.Dispose();
});
var count = (your collections.Count / 1000) + 1;
第7行：members 是您的集合，要么是...当我针对我的情况运行此代码时，我遇到了这个错误_Transaction (Process ID 80) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction._ - Abdurrahman I. var bulkCopy = new SqlBulkCopy(connection);
bulkCopy.DestinationTableName = TableName;
var dataReader = storeEntities.AsDataReader();
bulkCopy.WriteToServer(dataReader);
https://github.com/MHanafy/EntityExtensions
还有其他好东西，希望它将来能够扩展更多功能。
使用它就像这样简单：
var insertsAndupdates = new List<object>();
var deletes = new List<object>();
context.BulkUpdate(insertsAndupdates, deletes);