This tip is an update of a previous tip and applies to the entity framework
version 5 and POCO objects. By extending the generated context class, using a partial class, we greatly improve insert speed using sql bulk copy. Since this version
of EF does not use attributes in the same way as before on the generated classes we have to delve into the metadata workspace to find column mappings.
And, since the metadata workspace properties and methods are mostly non public we have to use reflection and the Invoke
method to get what we want.
In the end though it works just fine, so here it is. As Before this has also been posted on my blog.
public partial class MyEntities
{
public override int SaveChanges()
{
var objectContext = ((IObjectContextAdapter)this).ObjectContext;
objectContext.CommandTimeout = 5 * 60;
return base.SaveChanges();
}
public void BulkInsertAll<t>(T[] entities) where T : class
{
var conn = (SqlConnection)Database.Connection;
conn.Open();
Type t = typeof(T);
Set(t).ToString();
var objectContext = ((IObjectContextAdapter)this).ObjectContext;
var workspace = objectContext.MetadataWorkspace;
var mappings = GetMappings(workspace, objectContext.DefaultContainerName, typeof(T).Name);
var tableName = GetTableName<t>();
var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = tableName};
var properties = t.GetProperties().Where(p => !p.GetGetMethod().IsVirtual).ToArray();
var table = new DataTable();
foreach (var property in properties)
{
Type propertyType = property.PropertyType;
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
propertyType = Nullable.GetUnderlyingType(propertyType);
}
table.Columns.Add(new DataColumn(property.Name, propertyType));
var clrPropertyName = property.Name;
var tableColumnName = mappings[property.Name];
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(clrPropertyName, tableColumnName));
}
foreach (var entity in entities)
{
var e = entity;
table.Rows.Add(properties.Select(property =>
GetPropertyValue(property.GetValue(e, null))).ToArray());
}
bulkCopy.WriteToServer(table);
conn.Close();
}
private string GetTableName<t>() where T : class
{
var dbSet = Set<t>();
var sql = dbSet.ToString();
var regex = new Regex(@"FROM (?.*) AS");
var match = regex.Match(sql);
return match.Groups["table"].Value;
}
private object GetPropertyValue(object o)
{
if (o == null)
return DBNull.Value;
return o;
}
private Dictionary<string,> GetMappings(MetadataWorkspace workspace, string containerName, string entityName)
{
var mappings = new Dictionary<string,>();
var storageMapping = workspace.GetItem<globalitem>(containerName, DataSpace.CSSpace);
dynamic entitySetMaps = storageMapping.GetType().InvokeMember(
"EntitySetMaps",
BindingFlags.GetProperty | BindingFlags.NonPublic | BindingFlags.Instance,
null, storageMapping, null);
foreach (var entitySetMap in entitySetMaps)
{
var typeMappings = GetArrayList("TypeMappings", entitySetMap);
dynamic typeMapping = typeMappings[0];
dynamic types = GetArrayList("Types", typeMapping);
if (types[0].Name == entityName)
{
var fragments = GetArrayList("MappingFragments", typeMapping);
var fragment = fragments[0];
var properties = GetArrayList("AllProperties", fragment);
foreach (var property in properties)
{
var edmProperty = GetProperty("EdmProperty", property);
var columnProperty = GetProperty("ColumnProperty", property);
mappings.Add(edmProperty.Name, columnProperty.Name);
}
}
}
return mappings;
}
private ArrayList GetArrayList(string property, object instance)
{
var type = instance.GetType();
var objects = (IEnumerable)type.InvokeMember(property, BindingFlags.GetProperty |
BindingFlags.NonPublic | BindingFlags.Instance, null, instance, null);
var list = new ArrayList();
foreach (var o in objects)
{
list.Add(o);
}
return list;
}
private dynamic GetProperty(string property, object instance)
{
var type = instance.GetType();
return type.InvokeMember(property, BindingFlags.GetProperty |
BindingFlags.NonPublic | BindingFlags.Instance, null, instance, null);
}
}