Introduction
The current status of this project can be found here on GitHub. There is also a nuget package available here.
The idea to extend the EF context with bulk operations had its origin in a small partial class for LINQ-to-SQL back in 2011. A year or so later, I also published two versions for EF here and here. They sort of did the trick but only for relatively simple tables. In 2017, I made a bit of an effort to handle more complex database designs and published an article here on Codeproject along with some sample code. The more I used it in my own projects, the more I discovered I needed to add to it and I also decided it would be great to publish it as a nuget package. So, I did. In this article I will revisit some of what I talked about in the previous article but I will also discuss how to handle bulk updates and more complex foreign key setups. I hope you will find it useful. The nuget package is named Tanneryd.BulkOperations.EF6.
Background
The main reason why inserts numbered in the hundred thousands or millions take eons to finish when using EF is that each and every insert is encapsulated in and executed through the stored procedure sp_executesql. So, you will get just as many roundtrips to the database as you have inserts, each executing this stored procedure with your insert statement inside.
Typically, it could look something like this:
exec sp_executesql N'INSERT [dbo].[Prime]([Number], [CompositeId])
VALUES (@0, @1)
SELECT [Id]
FROM [dbo].[Prime]
WHERE @@ROWCOUNT > 0 AND [Id] = scope_identity()',N'@0 bigint,@1 bigint',@0=7,@1=198
Updates are handled in a similar manner so we have the same kind of problem there.
Using the Code
The bulk operations are provided as extensions to the DbContext class. So, all you have to do is to install the nuget package.
BulkInsertAll
public static void BulkInsertAll(
this DbContext ctx,
IList entities,
SqlTransaction transaction = null,
bool recursive = false)
If you set recursive to true, BulkInsertAll will, recursively, follow all navigation properties and insert all new entities found. If you use transactions, you should get the SqlTransaction from the context and set the transaction argument.
using(var db = new MyContext())
using(var transaction = db.Database.BeginTransaction())
{
try
{
var sqlTransaction = (SqlTransaction)transaction.UnderlyingTransaction;
db.BulkInsertAll(entities, sqlTransaction, true);
transaction.Commit();
}
catch(Exception e)
{
transaction.Rollback();
}
}
BulkUpdateAll
public static void BulkUpdateAll(
this DbContext ctx,
IList entities,
string[] updatedColumns = null,
SqlTransaction transaction = null)
The BulkUpdateAll method will update ALL columns mapped to properties in your entities or the named columns in the updatedColumns argument. It only works for tables with a primary key defined.
How It Works
Table Mappings
It's All Connected
The entity framework context contains all the information we need in order to figure out how to map the entities to tables. It is, however, not necessarily available via public APIs so we have to do a bit of reflection to get what we need.
First, we extract something called a storage mapping and from that, we get two things, the EntitySetMappings and the AssociationSetMappings. The EntitySetMappings has everything we need except for the information about many-to-many relationships. Those things we find in the AssociationSetMappings.
var objectContext = ((IObjectContextAdapter)ctx).ObjectContext;
var workspace = objectContext.MetadataWorkspace;
var containerName = objectContext.DefaultContainerName;
var entityName = t.Name;
var storageMapping = (EntityContainerMapping)workspace.GetItem<GlobalItem>
(containerName, DataSpace.CSSpace);
var entitySetMaps = storageMapping.EntitySetMappings.ToList();
var associationSetMaps = storageMapping.AssociationSetMappings.ToList();
Scalar Properties
Figuring out how the scalar properties are connected to table columns is relatively straight forward. Scalar properties are all properties except for the properties representing what a foreign key point at. Those are called navigation properties. The property holding the actual foreign key though is still a scalar property.
var entitySetMap = entitySetMaps.Single(m => m.EntitySet.ElementType.Name == entityName);
var typeMappings = entitySetMap.EntityTypeMappings;
EntityTypeMapping typeMapping = typeMappings[0];
var fragments = typeMapping.Fragments;
var fragment = fragments[0];
var properties = fragment.PropertyMappings;
var columnMappings = new Dictionary<string, CLR2ColumnMapping>();
foreach (var property in properties.Where
(p => p is ScalarPropertyMapping).Cast<ScalarPropertyMapping>())
{
var clrProperty = property.Property;
var columnProperty = property.Column;
columnMappings.Add(clrProperty.Name, new CLR2ColumnMapping
{
CLRProperty = clrProperty,
ColumnProperty = columnProperty,
});
}
Navigation Properties
First, we need to find all navigation properties.
var navigationProperties =
typeMapping.EntityType.DeclaredMembers
.Where(m => m.BuiltInTypeKind == BuiltInTypeKind.NavigationProperty)
.Cast<NavigationProperty>()
.Where(p => p.RelationshipType is AssociationType)
.ToArray();
We store our mapping data in the ForeignKeyMapping class.
var fkMapping = new ForeignKeyMapping
{
NavigationPropertyName = navigationProperty.Name,
BuiltInTypeKind = navigationProperty.TypeUsage.EdmType.BuiltInTypeKind,
Name = relType.Name,
};
One-To-One and One-To-Many mappings are not so much of a hassle.
fkMapping.FromType = relType.Constraint.FromProperties.First().DeclaringType.Name;
fkMapping.ToType = relType.Constraint.ToProperties.First().DeclaringType.Name;
var foreignKeyRelations = new List<ForeignKeyRelation>();
for (int i = 0; i < relType.Constraint.FromProperties.Count; i++)
{
foreignKeyRelations.Add(new ForeignKeyRelation
{
FromProperty = relType.Constraint.FromProperties[i].Name,
ToProperty = relType.Constraint.ToProperties[i].Name,
});
}
fkMapping.ForeignKeyRelations = foreignKeyRelations.ToArray();
Many-To-Many a little bit more though. These relationships are implemented using a separate database table linking rows in the two associated tables. The table name of this third table is what we find in the store entity set at the end of this code snippet.
var map = associationSetMaps.Single(m => m.AssociationSet.Name == relType.Name);
var sourceMapping =
new CLR2ColumnMapping
{
ColumnProperty = map.SourceEndMapping.PropertyMappings[0].Column,
CLRProperty = map.SourceEndMapping.PropertyMappings[0].Property,
};
var targetMapping =
new CLR2ColumnMapping
{
ColumnProperty = map.TargetEndMapping.PropertyMappings[0].Column,
CLRProperty = map.TargetEndMapping.PropertyMappings[0].Property,
};
fkMapping.FromType =
(map.SourceEndMapping.AssociationEnd.TypeUsage.EdmType as RefType)?.ElementType.Name;
fkMapping.ToType =
(map.TargetEndMapping.AssociationEnd.TypeUsage.EdmType as RefType)?.ElementType.Name;
fkMapping.AssociationMapping = new AssociationMapping
{
TableName = new TableName
{
Name = map.StoreEntitySet.Table,
Schema = map.StoreEntitySet.Schema,
},
Source = sourceMapping,
Target = targetMapping
};
Bulk Insert
When we have our mappings sorted, we can start with the actual bulk inserts. We go about this in three steps. First, we find all one-to-one navigation properties and if they are new, we insert them by recursively calling bulk insert and when we get back, we set the resulting primary key value to the relevant foreign key property on our entity. Second, we insert all entities passed as arguments to this bulk insert call. And, finally we run through all collection type navigation properties, i.e., the one-to-many and many-to-many navigation properties and using recursive calls, we bulk insert them as well.
This should, if we are lucky, result in the entire entity graph being inserted.
Bulk Update
The bulk update does not support entity graphs so there is no recursion going on here. The selected columns, or all mapped columns if no selection was made, will be updated whether they have been modified or not. To speed things up, we first bulk insert the passed entities into a temporary database table and then we run a regular SQL update command where we join the target table with our temp table.
The Actual Code
using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Entity;
using System.Data.Entity.Core.Mapping;
using System.Data.Entity.Infrastructure;
using System.Data.Entity.Core.Metadata.Edm;
using System.Data.SqlClient;
using System.Dynamic;
using System.Linq;
using System.Reflection;
using System.Runtime.CompilerServices;
using System.Text.RegularExpressions;
using System.Threading;
namespace Tanneryd.BulkInsert
{
public static class DbContextExtensionMethods
{
#region Public API
public static void BulkUpdateAll(
this DbContext ctx,
IList entities,
string[] updatedColumns = null,
SqlTransaction transaction = null)
{
if (entities.Count == 0) return;
var globalId = CreateGlobalId(ctx);
using (var mutex = new Mutex(false, globalId))
{
if (mutex.WaitOne())
{
try
{
DoBulkUpdateAll(ctx, entities, updatedColumns, transaction);
}
finally
{
mutex.ReleaseMutex();
}
}
}
}
public static void BulkInsertAll(
this DbContext ctx,
IList entities,
SqlTransaction transaction = null,
bool recursive = false)
{
if (entities.Count == 0) return;
var globalId = CreateGlobalId(ctx);
using (var mutex = new Mutex(false, globalId))
{
if (mutex.WaitOne())
{
try
{
DoBulkInsertAll(ctx, entities, transaction, recursive,
new Dictionary<object, object>(new IdentityEqualityComparer<object>()));
}
finally
{
mutex.ReleaseMutex();
}
}
}
}
#endregion
#region Private methods
private static void DoBulkUpdateAll
(this DbContext ctx, IList entities, string[] updatedColumnNames = null,
SqlTransaction transaction = null)
{
if (entities.Count == 0) return;
Type t = entities[0].GetType();
var mappings = GetMappings(ctx, t);
var tableName = mappings.TableName;
var columnMappings = mappings.ColumnMappings;
dynamic declaringType = columnMappings
.Values
.First().ColumnProperty.DeclaringType;
var keyMembers = declaringType.KeyMembers;
var pkColumnMappings = columnMappings.Values
.Where(m => keyMembers.Contains(m.ColumnProperty.Name))
.ToArray();
if (pkColumnMappings.Any())
{
var modifiedColumnMappingCandidates = columnMappings.Values
.Where(m => !keyMembers.Contains(m.ColumnProperty.Name))
.Select(m => m)
.ToArray();
if (updatedColumnNames != null &&
updatedColumnNames.Any())
{
modifiedColumnMappingCandidates =
modifiedColumnMappingCandidates.Where
(c => updatedColumnNames.Contains(c.CLRProperty.Name)).ToArray();
}
var modifiedColumnMappings = modifiedColumnMappingCandidates.ToArray();
var conn = GetSqlConnection(ctx);
var tempTableName = FillTempTable(conn, entities, tableName,
columnMappings, pkColumnMappings, modifiedColumnMappings, transaction);
var setStatements = modifiedColumnMappings.Select
(c => $"t0.{c.ColumnProperty.Name} = t1.{c.ColumnProperty.Name}");
var setStatementsSql = string.Join(" , ", setStatements);
var conditionStatements = pkColumnMappings.Select
(c => $"t0.{c.ColumnProperty.Name} = t1.{c.ColumnProperty.Name}");
var conditionStatementsSql = string.Join(" AND ", conditionStatements);
var cmdBody = $@"UPDATE t0 SET {setStatementsSql}
FROM {tableName.Fullname} AS t0
INNER JOIN #{tableName.Name} AS t1 ON {conditionStatementsSql}
";
var cmd = new SqlCommand(cmdBody, conn, transaction);
cmd.ExecuteNonQuery();
var cmdFooter = $@"DROP TABLE {tempTableName}";
cmd = new SqlCommand(cmdFooter, conn, transaction);
cmd.ExecuteNonQuery();
}
}
private static void DoBulkInsertAll(this DbContext ctx, IList entities,
SqlTransaction transaction, bool recursive, Dictionary<object, object> savedEntities)
{
if (entities.Count == 0) return;
Type t = entities[0].GetType();
var mappings = GetMappings(ctx, t);
if (recursive)
{
foreach (var fkMapping in mappings.ToForeignKeyMappings)
{
var navProperties = new HashSet<object>();
var modifiedEntities = new List<object[]>();
foreach (var entity in entities)
{
var navProperty = GetProperty(fkMapping.NavigationPropertyName, entity);
if (navProperty != null)
{
foreach (var foreignKeyRelation in fkMapping.ForeignKeyRelations)
{
var navPropertyKey =
GetProperty(foreignKeyRelation.ToProperty, entity);
if (navPropertyKey == 0)
{
var currentValue = GetProperty(foreignKeyRelation.FromProperty,
navProperty);
if (currentValue > 0)
{
SetProperty
(foreignKeyRelation.ToProperty, entity, currentValue);
}
else
{
if (navProperty != entity)
{
navProperties.Add(navProperty);
modifiedEntities.Add(new object[]
{ entity, navProperty });
}
}
}
}
}
}
if (!navProperties.Any()) continue;
DoBulkInsertAll(ctx, navProperties.ToArray(), transaction, true, savedEntities);
foreach (var modifiedEntity in modifiedEntities)
{
var e = modifiedEntity[0];
var p = modifiedEntity[1];
foreach (var foreignKeyRelation in fkMapping.ForeignKeyRelations)
{
SetProperty(foreignKeyRelation.ToProperty, e,
GetProperty(foreignKeyRelation.FromProperty, p));
}
}
}
}
var validEntities = new ArrayList();
var ignoredEntities = new ArrayList();
foreach (dynamic entity in entities)
{
if (savedEntities.ContainsKey(entity))
{
ignoredEntities.Add(entity);
continue;
}
validEntities.Add(entity);
savedEntities.Add(entity, entity);
}
DoBulkInsertAll(ctx, validEntities, t, mappings, transaction);
if (recursive)
{
foreach (var fkMapping in mappings.FromForeignKeyMappings)
{
var navigationPropertyName = fkMapping.NavigationPropertyName;
var navPropertyEntities = new List<dynamic>();
var navPropertySelfReferences = new List<SelfReference>();
foreach (var entity in entities)
{
if (fkMapping.BuiltInTypeKind == BuiltInTypeKind.CollectionType ||
fkMapping.BuiltInTypeKind == BuiltInTypeKind.CollectionKind)
{
var navProperties = GetProperty(navigationPropertyName, entity);
if (fkMapping.ForeignKeyRelations != null)
{
foreach (var navProperty in navProperties)
{
foreach (var foreignKeyRelation in fkMapping.ForeignKeyRelations)
{
SetProperty(foreignKeyRelation.ToProperty, navProperty,
GetProperty(foreignKeyRelation.FromProperty, entity));
}
navPropertyEntities.Add(navProperty);
}
}
else if (fkMapping.AssociationMapping != null)
{
foreach (var navProperty in navProperties)
{
dynamic np = new ExpandoObject();
AddProperty(np,
fkMapping.AssociationMapping.Source.ColumnProperty.Name,
GetProperty(fkMapping.AssociationMapping.Source.CLRProperty.Name,
entity));
AddProperty(np,
fkMapping.AssociationMapping.Target.ColumnProperty.Name,
GetProperty(fkMapping.AssociationMapping.Target.CLRProperty.Name,
navProperty));
navPropertyEntities.Add(np);
}
}
}
else
{
var navProperty = GetProperty(navigationPropertyName, entity);
if (navProperty != null)
{
foreach (var foreignKeyRelation in fkMapping.ForeignKeyRelations)
{
SetProperty(foreignKeyRelation.ToProperty, navProperty,
GetProperty(foreignKeyRelation.FromProperty, entity));
}
if (navProperty != entity)
navPropertyEntities.Add(navProperty);
else
navPropertySelfReferences.Add(new SelfReference
{
Entity = entity,
ForeignKeyProperties = fkMapping.ForeignKeyRelations.Select
(p => p.ToProperty).ToArray()
});
}
}
}
if (navPropertySelfReferences.Any())
{
DoBulkUpdateAll(
ctx,
navPropertySelfReferences.Select(e => e.Entity).Distinct().ToArray(),
navPropertySelfReferences.SelectMany
(e => e.ForeignKeyProperties).Distinct().ToArray(),
transaction);
}
if (navPropertyEntities.Any())
{
if (navPropertyEntities.First() is ExpandoObject)
{
var expandoMappings = new Mappings
{
TableName = fkMapping.AssociationMapping.TableName,
ColumnMappings = new Dictionary<string, CLR2ColumnMapping>()
};
expandoMappings.ColumnMappings.Add(
fkMapping.AssociationMapping.Source.ColumnProperty.Name,
new CLR2ColumnMapping
{
CLRProperty =
fkMapping.AssociationMapping.Source.ColumnProperty,
ColumnProperty =
fkMapping.AssociationMapping.Source.ColumnProperty
});
expandoMappings.ColumnMappings.Add(
fkMapping.AssociationMapping.Target.ColumnProperty.Name,
new CLR2ColumnMapping
{
CLRProperty =
fkMapping.AssociationMapping.Target.ColumnProperty,
ColumnProperty =
fkMapping.AssociationMapping.Target.ColumnProperty
});
DoBulkInsertAll(ctx, navPropertyEntities.ToArray(),
typeof(ExpandoObject), expandoMappings, transaction);
}
else
DoBulkInsertAll(ctx, navPropertyEntities.ToArray(), transaction,
true, savedEntities);
}
}
}
}
private static void DoBulkInsertAll(this DbContext ctx,
IList entities, Type t, Mappings mappings, SqlTransaction transaction)
{
if (entities.Count == 0) return;
var tableName = mappings.TableName;
var columnMappings = mappings.ColumnMappings;
var conn = GetSqlConnection(ctx);
var bulkCopy = new SqlBulkCopy(conn, SqlBulkCopyOptions.Default, transaction)
{ DestinationTableName = tableName.Fullname };
var table = new DataTable();
var properties = GetProperties(entities[0])
.Where(p => columnMappings.ContainsKey(p.Name)).ToArray();
foreach (var property in properties)
{
Type propertyType = property.Type;
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
propertyType = Nullable.GetUnderlyingType(propertyType);
}
if (columnMappings.ContainsKey(property.Name))
{
table.Columns.Add(new DataColumn(property.Name, propertyType));
var clrPropertyName = property.Name;
var tableColumnName = columnMappings[property.Name].ColumnProperty.Name;
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping
(clrPropertyName, tableColumnName));
}
}
dynamic declaringType = columnMappings
.Values
.First().ColumnProperty.DeclaringType;
var keyMembers = declaringType.KeyMembers;
var pkColumnMappings = columnMappings.Values
.Where(m => keyMembers.Contains(m.ColumnProperty.Name))
.ToArray();
var pkColumns = pkColumnMappings.Select(m => m.ColumnProperty).ToArray();
if (pkColumns.Length == 0)
{
foreach (var entity in entities)
{
var e = entity;
table.Rows.Add(properties.Select(p => GetProperty
(p.Name, t, e, DBNull.Value)).ToArray());
}
bulkCopy.BulkCopyTimeout = 5 * 60;
bulkCopy.WriteToServer(table);
}
else if (pkColumns.Length == 1 &&
(pkColumns[0].IsStoreGeneratedIdentity || pkColumns[0].IsStoreGeneratedComputed))
{
var pkColumn = pkColumns[0];
var newEntities = new ArrayList();
foreach (var entity in entities)
{
var pk = GetProperty(pkColumn.Name, entity);
if (pk == 0)
newEntities.Add(entity);
}
if (newEntities.Count > 0)
{
var pkColumnType =
Type.GetType(pkColumn.PrimitiveType.ClrEquivalentType.FullName);
var cmd = conn.CreateCommand();
cmd.CommandTimeout = (int)TimeSpan.FromMinutes(30).TotalSeconds;
cmd.Transaction = transaction;
cmd.CommandText = $@"SELECT COUNT(*) FROM {tableName.Fullname}";
var result = cmd.ExecuteScalar();
var count = Convert.ToInt64(result);
cmd.CommandText = $"SELECT IDENT_INCR('{tableName.Fullname}')";
result = cmd.ExecuteScalar();
dynamic identIncrement = Convert.ChangeType(result, pkColumnType);
cmd.CommandText = $"SELECT IDENT_CURRENT('{tableName.Fullname}')";
result = cmd.ExecuteScalar();
dynamic identcurrent = Convert.ChangeType(result, pkColumnType);
var nextId = identcurrent + (count > 0 ? identIncrement : 0);
foreach (var entity in newEntities)
{
var e = entity;
table.Rows.Add
(properties.Select(p => GetProperty(p.Name, t, e, DBNull.Value))
.ToArray());
}
bulkCopy.BulkCopyTimeout = 5 * 60;
bulkCopy.WriteToServer(table);
cmd.CommandText = $"SELECT SCOPE_IDENTITY()";
result = cmd.ExecuteScalar();
dynamic lastId = Convert.ChangeType(result, pkColumnType);
cmd.CommandText =
$"SELECT {pkColumn.Name} From {tableName.Fullname}
WHERE {pkColumn.Name} >= {nextId} and {pkColumn.Name} <= {lastId}";
var reader = cmd.ExecuteReader();
var ids = (from IDataRecord r in reader
let pk = r[pkColumn.Name]
select pk)
.OrderBy(i => i)
.ToArray();
if (ids.Length != newEntities.Count)
throw new ArgumentException(
"More id values generated than we had entities.
Something went wrong, try again.");
for (int i = 0; i < newEntities.Count; i++)
{
SetProperty(pkColumn.Name, newEntities[i], ids[i]);
}
}
}
else
{
var nonPrimaryKeyColumnMappings = columnMappings
.Values
.Except(pkColumnMappings)
.ToArray();
var tempTableName = FillTempTable(conn, entities, tableName,
columnMappings, pkColumnMappings, nonPrimaryKeyColumnMappings, transaction);
var conditionStatements =
pkColumnMappings.Select(c => $"t0.{c.ColumnProperty.Name} =
t1.{c.ColumnProperty.Name}");
var conditionStatementsSql = string.Join(" AND ", conditionStatements);
string cmdBody;
SqlCommand cmd;
if (nonPrimaryKeyColumnMappings.Any())
{
var setStatements = nonPrimaryKeyColumnMappings.Select(c =>
$"t0.{c.ColumnProperty.Name} = t1.{c.ColumnProperty.Name}");
var setStatementsSql = string.Join(" , ", setStatements);
cmdBody = $@"UPDATE t0 SET {setStatementsSql}
FROM {tableName.Fullname} AS t0
INNER JOIN #{tableName.Name} AS t1 ON {conditionStatementsSql}
";
cmd = new SqlCommand(cmdBody, conn, transaction);
cmd.ExecuteNonQuery();
}
string listOfPrimaryKeyColumns = string.Join(",",
pkColumnMappings.Select(c => c.ColumnProperty));
string listOfColumns = string.Join(",",
pkColumnMappings.Concat
(nonPrimaryKeyColumnMappings).Select(c => c.ColumnProperty));
cmdBody = $@"INSERT INTO {tableName.Fullname} ({listOfColumns})
SELECT {listOfColumns}
FROM #{tableName.Name} AS t0
WHERE NOT EXISTS (
SELECT {listOfPrimaryKeyColumns}
FROM {tableName.Fullname} AS t1
WHERE {conditionStatementsSql}
)
";
cmd = new SqlCommand(cmdBody, conn, transaction);
cmd.ExecuteNonQuery();
var cmdFooter = $@"DROP TABLE {tempTableName}";
cmd = new SqlCommand(cmdFooter, conn, transaction);
cmd.ExecuteNonQuery();
}
}
private static string CreateGlobalId(DbContext ctx)
{
var ds = ctx.Database.Connection.DataSource.Replace(@"\", "_");
var dbname = ctx.Database.Connection.Database.Replace(@"\", "_");
var globalId = $@"Global\{ds}_{dbname}";
return globalId;
}
private static string FillTempTable(
SqlConnection conn,
IList entities,
TableName tableName,
Dictionary<string, CLR2ColumnMapping> columnMappings,
CLR2ColumnMapping[] primaryKeyColumnMappings,
CLR2ColumnMapping[] nonPrimaryKeyColumnMappings,
SqlTransaction sqlTransaction)
{
var tempTableName = $@"#{tableName.Name}";
var columns = primaryKeyColumnMappings.Select(m => m.ColumnProperty.Name)
.Concat(nonPrimaryKeyColumnMappings.Select(m => m.ColumnProperty.Name)).ToArray();
var columnNames = string.Join(",", columns.Select(c => c));
var cmdHeader = $@"
IF OBJECT_ID('tempdb..#{tableName.Name}')
IS NOT NULL DROP TABLE #{tableName.Name}
SELECT {columnNames}
INTO #{tableName.Name}
FROM {tableName.Fullname}
WHERE 1=0
";
if (primaryKeyColumnMappings.Length == 1 &&
(primaryKeyColumnMappings[0].ColumnProperty.IsStoreGeneratedIdentity ||
primaryKeyColumnMappings[0].ColumnProperty.IsStoreGeneratedComputed))
{
cmdHeader += $@"SET IDENTITY_INSERT #{tableName.Name} ON";
}
var cmd = new SqlCommand(cmdHeader, conn, sqlTransaction);
cmd.ExecuteNonQuery();
var bulkCopy =
new SqlBulkCopy(conn, SqlBulkCopyOptions.KeepIdentity, sqlTransaction)
{
DestinationTableName = tempTableName,
BulkCopyTimeout = 5 * 60,
};
var allProperties = GetProperties(entities[0]);
var pkColumnProperties = allProperties
.Where(p => primaryKeyColumnMappings.Any(m => m.CLRProperty.Name == p.Name))
.ToArray();
var selectedColumnProperties = allProperties
.Where(p => nonPrimaryKeyColumnMappings.Any(m => m.CLRProperty.Name == p.Name))
.ToArray();
var properties = pkColumnProperties.Concat(selectedColumnProperties).ToArray();
var table = new DataTable();
foreach (var property in properties)
{
Type propertyType = property.Type;
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
propertyType = Nullable.GetUnderlyingType(propertyType);
}
if (columnMappings.ContainsKey(property.Name))
{
table.Columns.Add(new DataColumn(property.Name, propertyType));
var clrPropertyName = property.Name;
var tableColumnName = columnMappings[property.Name].ColumnProperty.Name;
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping
(clrPropertyName, tableColumnName));
}
}
foreach (var entity in entities)
{
var e = entity;
table.Rows.Add(properties.Select
(p => GetProperty(p.Name, e, DBNull.Value)).ToArray());
}
bulkCopy.WriteToServer(table);
return tempTableName;
}
private static PropInfo[] GetProperties(object o)
{
if (o is ExpandoObject)
{
var props = new List<PropInfo>();
var dict = (IDictionary<string, object>)o;
foreach (var kvp in dict)
{
props.Add(new PropInfo
{
Name = kvp.Key,
Type = kvp.Value.GetType()
});
}
return props.ToArray();
}
Type t = o.GetType();
return t.GetProperties().Select(p => new PropInfo
{
Type = p.PropertyType,
Name = p.Name,
}).ToArray();
}
private static void AddProperty
(ExpandoObject expando, string propertyName, object propertyValue)
{
var expandoDict = expando as IDictionary<string, object>;
if (expandoDict.ContainsKey(propertyName))
expandoDict[propertyName] = propertyValue;
else
expandoDict.Add(propertyName, propertyValue);
}
private static SqlConnection GetSqlConnection(this DbContext ctx)
{
var conn = (SqlConnection)ctx.Database.Connection;
if (conn.State == ConnectionState.Closed)
conn.Open();
return conn;
}
private static TableName GetTableName(this DbContext ctx, Type t)
{
var dbSet = ctx.Set(t);
var sql = dbSet.ToString();
var regex = new Regex(@"FROM (?<table>.*) AS");
var match = regex.Match(sql);
var name = match.Groups["table"].Value;
var n = name.Replace("[", "").Replace("]", "");
var m = Regex.Match(n, @"(.*)\.(.*)");
if (m.Success)
{
return new TableName { Schema = m.Groups[1].Value, Name = m.Groups[2].Value };
}
m = Regex.Match(n, @"(.*)");
if (m.Success)
{
return new TableName { Schema = "dbo", Name = m.Groups[1].Value };
}
throw new ArgumentException
($"Failed to parse tablename {name}. Bulk operation failed.");
}
private static Mappings GetMappings(DbContext ctx, Type t)
{
var objectContext = ((IObjectContextAdapter)ctx).ObjectContext;
var workspace = objectContext.MetadataWorkspace;
var containerName = objectContext.DefaultContainerName;
var entityName = t.Name;
var storageMapping = (EntityContainerMapping)workspace.GetItem<GlobalItem>
(containerName, DataSpace.CSSpace);
var entitySetMaps = storageMapping.EntitySetMappings.ToList();
var associationSetMaps = storageMapping.AssociationSetMappings.ToList();
var entitySetMap = entitySetMaps.Single
(m => m.EntitySet.ElementType.Name == entityName);
var typeMappings = entitySetMap.EntityTypeMappings;
EntityTypeMapping typeMapping = typeMappings[0];
var fragments = typeMapping.Fragments;
var fragment = fragments[0];
var properties = fragment.PropertyMappings;
var columnMappings = new Dictionary<string, CLR2ColumnMapping>();
foreach (var property in properties.Where
(p => p is ScalarPropertyMapping).Cast<ScalarPropertyMapping>())
{
var clrProperty = property.Property;
var columnProperty = property.Column;
columnMappings.Add(clrProperty.Name, new CLR2ColumnMapping
{
CLRProperty = clrProperty,
ColumnProperty = columnProperty,
});
}
var foreignKeyMappings = new List<ForeignKeyMapping>();
var navigationProperties =
typeMapping.EntityType.DeclaredMembers.Where
(m => m.BuiltInTypeKind == BuiltInTypeKind.NavigationProperty)
.Cast<NavigationProperty>()
.Where(p => p.RelationshipType is AssociationType)
.ToArray();
foreach (var navigationProperty in navigationProperties)
{
var relType = (AssociationType)navigationProperty.RelationshipType;
if (foreignKeyMappings.All(m => m.Name != relType.Name))
{
var fkMapping = new ForeignKeyMapping
{
NavigationPropertyName = navigationProperty.Name,
BuiltInTypeKind = navigationProperty.TypeUsage.EdmType.BuiltInTypeKind,
Name = relType.Name,
};
if (associationSetMaps.Any() &&
associationSetMaps.Any(m => m.AssociationSet.Name == relType.Name))
{
var map = associationSetMaps.Single
(m => m.AssociationSet.Name == relType.Name);
var sourceMapping =
new CLR2ColumnMapping
{
ColumnProperty = map.SourceEndMapping.PropertyMappings[0].Column,
CLRProperty = map.SourceEndMapping.PropertyMappings[0].Property,
};
var targetMapping =
new CLR2ColumnMapping
{
ColumnProperty = map.TargetEndMapping.PropertyMappings[0].Column,
CLRProperty = map.TargetEndMapping.PropertyMappings[0].Property,
};
fkMapping.FromType = (map.SourceEndMapping.AssociationEnd.TypeUsage.EdmType
as RefType)?.ElementType.Name;
fkMapping.ToType = (map.TargetEndMapping.AssociationEnd.TypeUsage.EdmType
as RefType)?.ElementType.Name;
fkMapping.AssociationMapping = new AssociationMapping
{
TableName = new TableName
{
Name = map.StoreEntitySet.Table,
Schema = map.StoreEntitySet.Schema,
},
Source = sourceMapping,
Target = targetMapping
};
}
else
{
fkMapping.FromType =
relType.Constraint.FromProperties.First().DeclaringType.Name;
fkMapping.ToType =
relType.Constraint.ToProperties.First().DeclaringType.Name;
var foreignKeyRelations = new List<ForeignKeyRelation>();
for (int i = 0; i < relType.Constraint.FromProperties.Count; i++)
{
foreignKeyRelations.Add(new ForeignKeyRelation
{
FromProperty = relType.Constraint.FromProperties[i].Name,
ToProperty = relType.Constraint.ToProperties[i].Name,
});
}
fkMapping.ForeignKeyRelations = foreignKeyRelations.ToArray();
}
foreignKeyMappings.Add(fkMapping);
}
}
var tableName = GetTableName(ctx, t);
return new Mappings
{
TableName = tableName,
ColumnMappings = columnMappings,
ToForeignKeyMappings =
foreignKeyMappings.Where(m => m.ToType == entityName).ToArray(),
FromForeignKeyMappings = foreignKeyMappings.Where
(m => m.FromType == entityName).ToArray()
};
}
private static dynamic GetProperty(string property, object instance, object def = null)
{
var type = instance.GetType();
return GetProperty(property, type, instance, def);
}
private static dynamic GetProperty(string property, Type type,
object instance, object def = null)
{
if (instance is ExpandoObject)
{
var dict = (IDictionary<string, object>)instance;
return dict[property];
}
var p = type.InvokeMember(property, BindingFlags.GetProperty |
BindingFlags.Public |
BindingFlags.Instance, Type.DefaultBinder, instance, null);
if (p == null) return def;
return p;
}
private static void SetProperty(string property, object instance, object value)
{
var type = instance.GetType();
type.InvokeMember(property, BindingFlags.SetProperty |
BindingFlags.Public | BindingFlags.Instance,
Type.DefaultBinder, instance, new[] { value });
}
#endregion
}
internal class PropInfo
{
public Type Type { get; set; }
public string Name { get; set; }
}
internal class TableName
{
public string Schema { get; set; }
public string Name { get; set; }
public string Fullname => $"[{Schema}].[{Name}]";
}
internal class Mappings
{
public TableName TableName { get; set; }
public Dictionary<string, CLR2ColumnMapping> ColumnMappings { get; set; }
public ForeignKeyMapping[] ToForeignKeyMappings { get; set; }
public ForeignKeyMapping[] FromForeignKeyMappings { get; set; }
}
internal class CLR2ColumnMapping
{
public EdmProperty CLRProperty { get; set; }
public EdmProperty ColumnProperty { get; set; }
}
internal class AssociationMapping
{
public TableName TableName { get; set; }
public CLR2ColumnMapping Source { get; set; }
public CLR2ColumnMapping Target { get; set; }
}
internal class ForeignKeyMapping
{
public BuiltInTypeKind BuiltInTypeKind { get; set; }
public string NavigationPropertyName { get; set; }
public string Name { get; set; }
public string FromType { get; set; }
public string ToType { get; set; }
public ForeignKeyRelation[] ForeignKeyRelations { get; set; }
public AssociationMapping AssociationMapping { get; set; }
}
internal class ForeignKeyRelation
{
public string FromProperty { get; set; }
public string ToProperty { get; set; }
}
internal class SelfReference
{
public dynamic Entity { get; set; }
public string[] ForeignKeyProperties { get; set; }
}
internal class IdentityEqualityComparer<T> : IEqualityComparer<T> where T : class
{
public int GetHashCode(T value)
{
return RuntimeHelpers.GetHashCode(value);
}
public bool Equals(T left, T right)
{
return left == right;
}
}
}
Nuget
This code is available via nuget. The package name is Tanneryd.BulkInsert and the URL to the package is https://www.nuget.org/packages/Tanneryd.BulkOperations.EF6.
