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 dll
s 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)
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));
}
}
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With