c# - 带有子实体的 EFUtilities 批量 InsertAll

问题描述

我正在尝试使用 EFUtilities 包在我的 SQL Server 中批量插入项目列表。有没有办法可以插入具有多个导航属性的实体？我发现的只有这个

EFBatchOperation.For(DbContext, DbContext.Products).InsertAll(batch);

EFBatchOperation.For(DbContext, DbContext.Categories).InsertAll(batch.SelectMany(p => p.Categories));

EFBatchOperation.For(DbContext, DbContext.Orders).InsertAll(batch.SelectMany(p => p.Orders));

DbContext.SaveChanges();

它将实体保存在我的数据库中，但 FK 始终为 0。例如，我的产品表中的 ID 为 1，我的类别表中的 ID 为 0。所以这种方式不处理映射。

标签： c#asp.net-mvcentity-framework-6bulkinsert

我已经实现了对 EFUtilities 的扩展，它允许批量插入，同时还返回生成的 ID 并将它们设置在源对象上。我的版本包括其他改进，例如更好的线程安全性，以及插入集的可选分而治之类型重试。

但是有两个警告：

long仅支持( bigint)类型的主键。代码需要调整以支持其他类型的列。
导航属性不会自动插入，您必须InsertAll多次调用。

代码太长，这里不完整，可以参考我的博客：Entity Framework 6 – Bulk Insert and Returned of Generated Primary Keys

最相关的部分是 - 将所有数据插入临时表，并使用insert into并output确保以正确的顺序插入数据：

private static void BulkInsertAllAndReturnIds<T>(BulkInsertionCollectionMetadata<T> items, string schema,
    string tableName,
    IList<ColumnMapping> properties, SqlConnection connection, int? batchSize)
{
    if (items.Count == 0) return;
    long dummyValue = -1000 - items.Count;
    //set dummy IDs
    foreach (var item in items)
    {
        ((IHasPrimaryKey)item).PrimaryKey = dummyValue;
        dummyValue++;
    }
    try
    {
        if (connection.State != ConnectionState.Open)
        {
            connection.Open();
        }

        //create dummy table.
        using (var tempTable = new TempTable(connection, tableName, schema))
        {
            var createTempTableSql = $"Select * Into {tempTable.TableName} From {tableName} Where 1 = 2";
            using (var command = new SqlCommand(createTempTableSql, connection))
            {
                command.ExecuteNonQuery();
            }

            //bulk insert to temp table.
            BulkInsertAll(items, schema, tempTable.TableName, properties, connection, batchSize,
                SqlBulkCopyOptions.KeepNulls | SqlBulkCopyOptions.KeepIdentity);

            //note: IsPrimaryKey is not populated in InsertAll 
            // https://github.com/MikaelEliasson/EntityFramework.Utilities/blob/a5abc50b7367d64ca541b6e7e2e6018a500b6d8d/EntityFramework.Utilities/EntityFramework.Utilities/EFBatchOperation.cs#L129

            string primaryKeyNameOnObject = ((IHasPrimaryKey)items.First()).PrimaryKeyPropertyName;
            var primaryKey = properties.Single(c => c.NameOnObject == primaryKeyNameOnObject);
            var otherColumns = properties.Where(p => p != primaryKey);
            var allValueColumns = String.Join(", ", otherColumns.Select(c => "[" + c.NameInDatabase + "]"));

            //insert to real table and get new IDs.
            //this guarantees the record IDs are generated in the right order.
            var migrateAndReturnIds =
                $@"
                insert into {tableName} ({allValueColumns})
                OUTPUT inserted.{primaryKey.NameInDatabase}
                select {allValueColumns} from {tempTable.TableName} temp
                order by temp.{primaryKey.NameInDatabase}
                ";

            var newlyGeneratedIds = new List<long>(items.Count);
            using (var migrateDataCommand = new SqlCommand(migrateAndReturnIds, connection)
            {
                CommandTimeout = 0
            })
            using (var recordIdReader = migrateDataCommand.ExecuteReader())
            {
                while (recordIdReader.Read())
                {
                    var newId = recordIdReader.GetInt64(0);
                    newlyGeneratedIds.Add(newId);
                }
            }
            //set IDs on entities.
            if (newlyGeneratedIds.Count != items.Count)
            {
                throw new MissingPrimaryKeyException("There are fewer generated record IDs than the " +
                                                        "number of items inserted to the database.");
            }
            //the order of the IDs is not guaranteed, but the values will be generated in the same as the order values in `items`
            newlyGeneratedIds.Sort();
            for (int i = 0; i < newlyGeneratedIds.Count; i++)
            {
                ((IHasPrimaryKey)items[i]).PrimaryKey = newlyGeneratedIds[i];
            }
        }
    }
    finally
    {
        //make sure the ID is 0 if the row wasn't inserted.
        foreach (var item in items)
        {
            var entity = (IHasPrimaryKey)item;
            if (entity.PrimaryKey < 0) entity.PrimaryKey = 0;
        }
    }
}

顺便说一句 - 与实体框架的正常使用不同，您不需要SaveChanges()在使用批量插入后调用 -InsertAll已经保存了更改。

c# - 带有子实体的 EFUtilities 批量 InsertAll

问题描述

解决方案

推荐阅读