Entity Framework Bulk Insert Throws KeyNotFoundExc

问题:

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.

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

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

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. 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)

回答1:

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.

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));
}

Full Code:

// 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));
    }
}

回答2:

Ok, I had a same error couldnt find any answers online so had to look deep so here is what i figured:

When your entity has an inherited and the child entities if not defined as part of the DBSet it flags an error such kind, secondly, not sure why it expects the new DbContext with just the related entity used for bulkInsertions if there is any other entities out there it raises the same error.

So its the 2 cause so i had to fix these both, and running like an horse!!!

its worth trying, so give a try

回答3:

The "BulkInsert" library is very fast but not very flexible and unsupported.

It doesn't support all type of inheritance (TPC, TPT) and has some issue with column mapping.

The issue you got is for one of these reasons.

Disclaimer: I'm the owner of the project Entity Framework Extensions

This library is the ultimate library for performance and allow to:

• BulkSaveChanges
• BulkInsert
• BulkUpdate
• BulkDelete
• BulkMerge

All inheritance and association are supported.

Example:

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

// BulkSaveChanges is slower than BulkInsert but way faster then SaveChanges
using (var db = new Entities(myConnectionString))
{
    db.Contacts.AddRange(contactsToInsert);
    db.BulkSaveChanges();
}