实体框架批量插入引发KeyNotFoundException错误 [英] Entity Framework Bulk Insert Throws KeyNotFoundException error

问题描述

我正在使用 EF6 ，并且由于 AddRange（）方法的速度较慢，我需要使用 BulkInsert 。因此，我通过此处添加了针对EF6的BulkInsert的NuGet软件包。

I am using EF6 and due to the low speed of AddRange() method I need to use BulkInsert. So I added the NuGet package of BulkInsert for EF6 via here.

添加 dll 后收到的第一件事是以下警告：

First thing I received after adding the dlls was this warning:

找到相同从属
程序集的不同版本之间的冲突。请在项目文件中将 AutoGenerateBindingRedirects属性设置为
true。

Found conflicts between different versions of the same dependent assembly. Please set the "AutoGenerateBindingRedirects" property to true in the project file.

我做了列出我所有 Contact 实体（即 contactsToInsert ）中需要添加的所有实体（我的联系人在另一个表中具有外键）。当我尝试运行以下代码时，我收到一个 KeyNotFoundException ，其中声明 给定的密钥在词典中不存在。

I made a List of all my Contact entities namely contactsToInsert that need to be added (My contacts have a foreign key in another table, too). When I tried to run the following code I receive a KeyNotFoundException that claims "The given key was not present in the dictionary".

using (var db = new Entities(myConnectionString))
{
    db.BulkInsert(contactsToInsert);
    db.SaveChanges();
}

NB 。我正在BulkInsert内部运行 BackgroundWorker 。通过此修复程序来判断？

NB. I am running the BulkInsert inside a BackgroundWorker. Could this possibly be the cause of the issue judging by this fix?

StackTrace：

   at System.Collections.Generic.Dictionary`2.get_Item(TKey key)
   at EntityFramework.MappingAPI.Mappers.MapperBase.BindForeignKeys() in c:\dev\EntityFramework.MappingAPI\trunk\src\EntityFramework.MappingAPI\Mappers\MapperBase.cs:line 603
   at EntityFramework.MappingAPI.Mappings.DbMapping..ctor(DbContext context) in c:\dev\EntityFramework.MappingAPI\trunk\src\EntityFramework.MappingAPI\Mappings\DbMapping.cs:line 101
   at EntityFramework.MappingAPI.EfMap.Get(DbContext context) in c:\dev\EntityFramework.MappingAPI\trunk\src\EntityFramework.MappingAPI\EfMap.cs:line 60
   at EntityFramework.MappingAPI.Extensions.MappingApiExtensions.Db(DbContext ctx, Type type) in c:\dev\EntityFramework.MappingAPI\trunk\src\EntityFramework.MappingAPI\Extensions\MappingApiExtensions.cs:line 51
   at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector, IEqualityComparer`1 comparer)
   at System.Linq.Enumerable.ToDictionary[TSource,TKey,TElement](IEnumerable`1 source, Func`2 keySelector, Func`2 elementSelector)
   at EntityFramework.BulkInsert.Helpers.MappedDataReader`1..ctor(IEnumerable`1 enumerable, IEfBulkInsertProvider provider) in c:\dev\EntityFramework.BulkInsert\dev\Src\EntityFramework.BulkInsert\Helpers\MappedDataReader.cs:line 58
   at EntityFramework.BulkInsert.Providers.EfSqlBulkInsertProviderWithMappedDataReader.Run[T](IEnumerable`1 entities, SqlTransaction transaction, BulkInsertOptions options) in c:\dev\EntityFramework.BulkInsert\dev\Src\EntityFramework.BulkInsert\Providers\EfSqlBulkInsertProviderWithMappedDataReader.cs:line 22
   at EntityFramework.BulkInsert.Providers.ProviderBase`2.Run[T](IEnumerable`1 entities, IDbTransaction transaction, BulkInsertOptions options) in c:\dev\EntityFramework.BulkInsert\dev\Src\EntityFramework.BulkInsert\Providers\ProviderBase.cs:line 77
   at EntityFramework.BulkInsert.Providers.ProviderBase`2.Run[T](IEnumerable`1 entities, BulkInsertOptions options) in c:\dev\EntityFramework.BulkInsert\dev\Src\EntityFramework.BulkInsert\Providers\ProviderBase.cs:line 109
   at EntityFramework.BulkInsert.Extensions.BulkInsertExtension.BulkInsert[T](DbContext context, IEnumerable`1 entities, SqlBulkCopyOptions sqlBulkCopyOptions, Nullable`1 batchSize) in c:\dev\EntityFramework.BulkInsert\dev\Src\EntityFramework.BulkInsert\Extensions\BulkInsertExtension.cs:line 95
   at EntityFramework.BulkInsert.Extensions.BulkInsertExtension.BulkInsert[T](DbContext context, IEnumerable`1 entities, Nullable`1 batchSize) in c:\dev\EntityFramework.BulkInsert\dev\Src\EntityFramework.BulkInsert\Extensions\BulkInsertExtension.cs:line 75
   at Prospect.Update.bw_DoWork(Object sender, DoWorkEventArgs e) in c:\Users\pedram.mobedi\Documents\Visual Studio 2013\Projects\Prospect\Update.cs:line 546
   at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)

推荐答案

对中的代码进行了修改，这是在遇到相同的字典中不存在给定键 后，对于我的Code First Fluent API设置起作用的原因c $ c> BulkInsert（）。这里唯一的依赖性是在上述文章的 DataExtensions 片段中找到的 ToDataTable（）扩展方法。

With modifications to the code in this blog post, here's what worked for my Code First Fluent API setup after encountering the same "The given key was not present in the dictionary" error on BulkInsert(). The only dependency here is the ToDataTable() extension method found in the DataExtensions snippet of the aforementioned post.

相关部分是 GetColumnMappings（）方法，该方法获取POCO类属性的首选名称（您在代码中指定的一个）作为源列名（在enumerable-turned-datatable中），并将其与元数据成员的 name （数据库列名）配对，作为目标列名。

The relevant part is the GetColumnMappings() method which gets the POCO class property's preferred name (the one you specified in code) as the source column name (in the enumerable-turned-datatable) and pairs it with the metadata member's name (the DB column name) as the destination column name.

GetColumnMappings（）：

private IEnumerable<SqlBulkCopyColumnMapping> GetColumnMappings<T>()
{
    var storageMetadata = ((EntityConnection)objectContext.Connection).GetMetadataWorkspace().GetItems(DataSpace.SSpace);
    var entityPropMembers = storageMetadata
        .Where(s => (s.BuiltInTypeKind == BuiltInTypeKind.EntityType))
        .Select(s => (EntityType)s)
        .Where(p => p.Name == typeof(T).Name)
        .Select(p => (IEnumerable<EdmMember>)(p.MetadataProperties["Members"].Value))
        .First();

    var sourceColumns = entityPropMembers.Select(m => (string)m.MetadataProperties["PreferredName"].Value);
    var destinationColumns = entityPropMembers.Select(m => m.Name);

    return Enumerable.Zip(sourceColumns, destinationColumns, (s, d) => new SqlBulkCopyColumnMapping(s, d));
}

完整代码：

// Modified from: https://ruijarimba.wordpress.com/2012/03/25/bulk-insert-dot-net-applications-part1 and
// https://ruijarimba.wordpress.com/2012/03/18/entity-framework-get-mapped-table-name-from-an-entity/

internal class BulkInserter
{
    private readonly ObjectContext objectContext;

    private readonly IDbConnection connection;

    internal BulkInserter(DbContext contextAdapter)
    {
        objectContext = ((IObjectContextAdapter)contextAdapter).ObjectContext;
        connection = contextAdapter.Database.Connection;
    }

    public void Insert<T>(IEnumerable<T> items) where T : class
    {
        EnsureOpenConnection();
        using (var bulkCopy = new SqlBulkCopy((SqlConnection)connection)
        {
            DestinationTableName = GetTableName<T>(),
        })
        {
            foreach (var mapping in GetColumnMappings<T>())
            {
                bulkCopy.ColumnMappings.Add(mapping);
            }

            bulkCopy.WriteToServer(items.ToDataTable());
        }
    }
    private void EnsureOpenConnection()
    {
        if (connection.State == ConnectionState.Closed)
        {
            connection.Open();
        }
    }

    [System.Diagnostics.CodeAnalysis.SuppressMessage("Microsoft.Design", "CA1004:GenericMethodsShouldProvideTypeParameter")]
    private string GetTableName<T>() where T : class
    {
        string sql = objectContext.CreateObjectSet<T>().ToTraceString();
        Regex regex = new Regex("FROM (?<table>.*) AS");
        Match match = regex.Match(sql);

        string table = match.Groups["table"].Value;
        return table;
    }

    private IEnumerable<SqlBulkCopyColumnMapping> GetColumnMappings<T>()
    {
        var storageMetadata = ((EntityConnection)objectContext.Connection).GetMetadataWorkspace().GetItems(DataSpace.SSpace);
        var entityPropMembers = storageMetadata
            .Where(s => (s.BuiltInTypeKind == BuiltInTypeKind.EntityType))
            .Select(s => (EntityType)s)
            .Where(p => p.Name == typeof(T).Name)
            .Select(p => (IEnumerable<EdmMember>)(p.MetadataProperties["Members"].Value))
            .First();

        var sourceColumns = entityPropMembers.Select(m => (string)m.MetadataProperties["PreferredName"].Value);
        var destinationColumns = entityPropMembers.Select(m => m.Name);

        return Enumerable.Zip(sourceColumns, destinationColumns, (s, d) => new SqlBulkCopyColumnMapping(s, d));
    }
}

这篇关于实体框架批量插入引发KeyNotFoundException错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！