我正在寻找插入实体框架的最快方法。

我之所以问这个问题,是因为您有一个活动的TransactionScope,并且插入量很大(4000+)。它可能会持续超过10分钟(事务的默认超时),这将导致事务不完整。


当前回答

据我所知,EntityFramework中没有BulkInsert来提高大型插件的性能。

在这种情况下,您可以使用ADO.net中的SqlBulkCopy来解决问题

其他回答

〔2019更新〕EF Core 3.1

如上所述,在EF Core中禁用AutoDetectChangesEnabled非常有效:插入时间除以100(从几分钟到几秒,10k条记录具有交叉表关系)

更新的代码为:

context.ChangeTracker.AutoDetectChangesEnabled = false;
foreach (IRecord record in records) {
    //Add records to your database        
}
context.ChangeTracker.DetectChanges();
context.SaveChanges();
context.ChangeTracker.AutoDetectChangesEnabled = true; //do not forget to re-enable

正如其他人所说,如果您想要真正好的插入性能,SqlBulkCopy是一种实现方法。

它的实现有点麻烦,但有一些库可以帮助您实现它。有一些库,但这次我将无耻地使用我自己的库:https://github.com/MikaelEliasson/EntityFramework.Utilities#batch-插入实体

您需要的唯一代码是:

 using (var db = new YourDbContext())
 {
     EFBatchOperation.For(db, db.BlogPosts).InsertAll(list);
 }

那么它快多少?很难说,因为这取决于许多因素,计算机性能、网络、对象大小等。我所做的性能测试表明,如果您像其他答案中提到的那样优化EF配置,则可以在10秒左右以标准方式在本地主机上插入25k个实体。使用EFUtilities,大约需要300毫秒。更有趣的是,我使用这种方法在不到15秒内保存了大约300万个实体,平均每秒大约200万个实体。

当然,一个问题是若需要插入相关数据。这可以使用上述方法在sql server中高效地完成,但它需要您有一个Id生成策略,允许您在应用程序代码中为父级生成Id,以便您可以设置外键。这可以使用GUID或类似HiLo id生成的方法来完成。

如果您添加的实体()依赖于上下文中的其他预加载实体(例如导航财产),则Dispose()上下文会产生问题

我使用类似的概念来保持我的上下文较小,以实现相同的性能

但我只是分离已经SaveChanges()的实体,而不是Dispose()上下文并重新创建

public void AddAndSave<TEntity>(List<TEntity> entities) where TEntity : class {

const int CommitCount = 1000; //set your own best performance number here
int currentCount = 0;

while (currentCount < entities.Count())
{
    //make sure it don't commit more than the entities you have
    int commitCount = CommitCount;
    if ((entities.Count - currentCount) < commitCount)
        commitCount = entities.Count - currentCount;

    //e.g. Add entities [ i = 0 to 999, 1000 to 1999, ... , n to n+999... ] to conext
    for (int i = currentCount; i < (currentCount + commitCount); i++)        
        _context.Entry(entities[i]).State = System.Data.EntityState.Added;
        //same as calling _context.Set<TEntity>().Add(entities[i]);       

    //commit entities[n to n+999] to database
    _context.SaveChanges();

    //detach all entities in the context that committed to database
    //so it won't overload the context
    for (int i = currentCount; i < (currentCount + commitCount); i++)
        _context.Entry(entities[i]).State = System.Data.EntityState.Detached;

    currentCount += commitCount;
} }

如果需要,用try-catch和TrasactionScope()将其包装起来,为了保持代码干净,没有在这里显示它们

SqlBulkCopy速度极快

这是我的实现:

// at some point in my calling code, I will call:
var myDataTable = CreateMyDataTable();
myDataTable.Rows.Add(Guid.NewGuid,tableHeaderId,theName,theValue); // e.g. - need this call for each row to insert

var efConnectionString = ConfigurationManager.ConnectionStrings["MyWebConfigEfConnection"].ConnectionString;
var efConnectionStringBuilder = new EntityConnectionStringBuilder(efConnectionString);
var connectionString = efConnectionStringBuilder.ProviderConnectionString;
BulkInsert(connectionString, myDataTable);

private DataTable CreateMyDataTable()
{
    var myDataTable = new DataTable { TableName = "MyTable"};
// this table has an identity column - don't need to specify that
    myDataTable.Columns.Add("MyTableRecordGuid", typeof(Guid));
    myDataTable.Columns.Add("MyTableHeaderId", typeof(int));
    myDataTable.Columns.Add("ColumnName", typeof(string));
    myDataTable.Columns.Add("ColumnValue", typeof(string));
    return myDataTable;
}

private void BulkInsert(string connectionString, DataTable dataTable)
{
    using (var connection = new SqlConnection(connectionString))
    {
        connection.Open();
        SqlTransaction transaction = null;
        try
        {
            transaction = connection.BeginTransaction();

            using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
            {
                sqlBulkCopy.DestinationTableName = dataTable.TableName;
                foreach (DataColumn column in dataTable.Columns) {
                    sqlBulkCopy.ColumnMappings.Add(column.ColumnName, column.ColumnName);
                }

                sqlBulkCopy.WriteToServer(dataTable);
            }
            transaction.Commit();
        }
        catch (Exception)
        {
            transaction?.Rollback();
            throw;
        }
    }
}

我同意亚当·拉基斯的观点。SqlBulkCopy是将批量记录从一个数据源传输到另一数据源的最快方法。我用这个复制了20K张唱片,只用了不到3秒钟。看看下面的例子。

public static void InsertIntoMembers(DataTable dataTable)
{           
    using (var connection = new SqlConnection(@"data source=;persist security info=True;user id=;password=;initial catalog=;MultipleActiveResultSets=True;App=EntityFramework"))
    {
        SqlTransaction transaction = null;
        connection.Open();
        try
        {
            transaction = connection.BeginTransaction();
            using (var sqlBulkCopy = new SqlBulkCopy(connection, SqlBulkCopyOptions.TableLock, transaction))
            {
                sqlBulkCopy.DestinationTableName = "Members";
                sqlBulkCopy.ColumnMappings.Add("Firstname", "Firstname");
                sqlBulkCopy.ColumnMappings.Add("Lastname", "Lastname");
                sqlBulkCopy.ColumnMappings.Add("DOB", "DOB");
                sqlBulkCopy.ColumnMappings.Add("Gender", "Gender");
                sqlBulkCopy.ColumnMappings.Add("Email", "Email");

                sqlBulkCopy.ColumnMappings.Add("Address1", "Address1");
                sqlBulkCopy.ColumnMappings.Add("Address2", "Address2");
                sqlBulkCopy.ColumnMappings.Add("Address3", "Address3");
                sqlBulkCopy.ColumnMappings.Add("Address4", "Address4");
                sqlBulkCopy.ColumnMappings.Add("Postcode", "Postcode");

                sqlBulkCopy.ColumnMappings.Add("MobileNumber", "MobileNumber");
                sqlBulkCopy.ColumnMappings.Add("TelephoneNumber", "TelephoneNumber");

                sqlBulkCopy.ColumnMappings.Add("Deleted", "Deleted");

                sqlBulkCopy.WriteToServer(dataTable);
            }
            transaction.Commit();
        }
        catch (Exception)
        {
            transaction.Rollback();
        }

    }
}