sql - 在EF中插入多行太慢，如何从SqlBulkCopy获取主键？

Question

我们的应用程序中有一个用例，其中用户触发一个请求，该请求将导致插入 100 到 1000 行。
插入之后，我们需要该对象继续处理并创建更多对象，这些对象是原始插入对象的外键，或者换句话说，我们需要插入对象的主键ID。

到目前为止，我们已经使用 EF 在 foreach 循环中执行此操作，这太慢了，大约需要 15-20 秒才能完成大约 600 行。 (同时阻止用户，不好:( )

原始代码(也处理更新，但我们不关心那里的性能，它不会阻止用户):

foreach (Location updatedLoc in locationsLoaded)
{
    // find it in the collection from the database
    Location fromDb = existingLocations.SingleOrDefault(loc => loc.ExtId.Equals(updatedLoc.ExtId));

    // update or insert
    if (fromDb != null)
    {
        // link ids for update
        updatedLoc.Id = fromDb.Id;

        // set values for update 
        db.Entry(fromDb).CurrentValues.SetValues(updatedLoc);
    }
    else
    {
        System.Diagnostics.Trace.WriteLine("Adding new location: " + updatedLoc.Name, "loadSimple");

        // insert a new location <============ This is the bottleneck, takes about 20-40ms per row
        db.Locations.Add(updatedLoc);
    }
}

// This actually takes about 3 seconds for 600 rows, was actually acceptable
db.SaveChanges();

所以在研究了SO和互联网之后，我发现我使用EF的方式是错误的，需要使用SqlBulkCopy

因此代码被重写，以前需要约 20 秒，现在需要约 100 毫秒(!)

foreach (Location updatedLoc in locationsLoaded)
{
    // find it in the collection from the database
    Location fromDb = existingLocations.SingleOrDefault(loc => loc.ExtId.Equals(updatedLoc.ExtId));

    // update or insert
    if (fromDb != null)
    {
        // link ids for update
        updatedLoc.Id = fromDb.Id;

        // set values for update
        db.Entry(fromDb).CurrentValues.SetValues(updatedLoc);
    }
    else
    {
        System.Diagnostics.Trace.WriteLine("Adding new location: " + updatedLoc.Name, "loadSimple");

        // insert a new location
        dataTable.Rows.Add(new object[] { \\the 14 fields of the location.. });
    }
}

System.Diagnostics.Trace.WriteLine("preparing to bulk insert", "loadSimple");

// perform the bulk insert
using (var bulkCopy = new System.Data.SqlClient.SqlBulkCopy(System.Configuration.ConfigurationManager.ConnectionStrings["bulk-inserter"].ConnectionString))
{
    bulkCopy.DestinationTableName = "Locations";

    for (int i = 0; i < dataTable.Columns.Count; i++)
    {
        bulkCopy.ColumnMappings.Add(i, i + 1);
    }

    bulkCopy.WriteToServer(dataTable);
}

// for update
db.SaveChanges();

问题是，批量复制后，作为 EF ORM 一部分的 Locations 集合中的对象不会更改(这是正常的且符合预期) ，但我需要插入的 id 才能继续处理这些对象。

一个简单的解决方案是立即从数据库中再次选择数据，我手头有数据，我可以简单地将其重新选择到不同的集合中。

但是该解决方案感觉不正确，是否无法将 id 作为插入的一部分获取。

编辑:简单的解决方案有效，请参阅下面接受的答案，了解如何轻松将其同步回 EF。

也许我不应该使用 SqlBulkCopy(我预计最多大约 1000 行，不再)而使用其他东西？

请注意，一些相关的 SO 问题和解决方案似乎都偏离了 EF..

1. Possible to get PrimayKey IDs back after a SQL BulkCopy?
2. Improving bulk insert performance in Entity framework
3. Fastest Way of Inserting in Entity Framework (这是关于 SaveChanges() 性能的许多挂起插入的性能，应该在每 X 个插入时调用它，而不是在处理结束时调用它并有 1000 秒挂起)

Answer 1

通过 EF 执行的任何操作都不会像 SqlBulkCopy 那样快。事实上，原始 SQL INSERT 并不那么快。所以你只需要重新阅读位置即可。通过使用 MergeOption.OverwriteChanges 重新读取来刷新查询.