This is yet another post about speeding up inserts using the entity framework. Previously I have written about this in the following posts.
- Speeding up inserts using Linq-To-Entities - Part 3 (EF 6.0)
- Speeding up inserts using Linq-To-Entities - Part 2 (EF 5)
- Speeding up inserts using Linq-To-Entities - Part 1
- http://blog.tanneryd.com/2011/11/speeding-up-inserts-using-linq-to-sql.html
- http://blog.tanneryd.com/2011/12/speeding-up-inserts-using-linq-to-sql.html
This time the code is adapted for EF 6.1. It's pretty much the same as for EF 6.0 but there are some subtle differences.
public partial class MyEntities
{
private Dictionary<string, object> _cache;
public Dictionary<string, object> Cache
{
get { return _cache ?? (_cache = new Dictionary<string, object>()); }
}
public MyEntities(string nameOrConnectionString)
: base(string.Format("name={0}", nameOrConnectionString))
{
}
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 };
// Foreign key relations show up as virtual declared
// properties and we want to ignore these.
var properties = t.GetProperties().Where(p => !p.GetGetMethod().IsVirtual).ToArray();
var table = new DataTable();
foreach (var property in properties)
{
Type propertyType = property.PropertyType;
// Nullable properties need special treatment.
if (propertyType.IsGenericType &&
propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
{
propertyType = Nullable.GetUnderlyingType(propertyType);
}
// Since we cannot trust the CLR type properties to be in the same order as
// the table columns we use the SqlBulkCopy column mappings.
table.Columns.Add(new DataColumn(property.Name, propertyType));
var clrPropertyName = property.Name;
var tableColumnName = mappings[property.Name];
bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(clrPropertyName, tableColumnName));
}
// Add all our entities to our data table
foreach (var entity in entities)
{
var e = entity;
table.Rows.Add(properties.Select(property => GetPropertyValue(property.GetValue(e, null))).ToArray());
}
// send it to the server for bulk execution
bulkCopy.BulkCopyTimeout = 5*60;
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 (?<table>.*) 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, string> GetMappings(MetadataWorkspace workspace, string containerName, string entityName)
{
var mappings = new Dictionary<string, string>();
var storageMapping = workspace.GetItem<GlobalItem>(containerName, DataSpace.CSSpace);
dynamic entitySetMaps = storageMapping.GetType().InvokeMember(
"EntitySetMappings",
BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance,
null, storageMapping, null);
foreach (var entitySetMap in entitySetMaps)
{
var typeMappings = GetArrayList("EntityTypeMappings", entitySetMap);
dynamic typeMapping = typeMappings[0];
dynamic types = GetArrayList("EntityTypes", typeMapping);
if (types[0].Name == entityName)
{
var fragments = GetArrayList("Fragments", typeMapping);
var fragment = fragments[0];
var properties = GetArrayList("PropertyMappings", fragment);
foreach (var property in properties)
{
var edmProperty = GetProperty("Property", property);
var columnProperty = GetProperty("Column", 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.Public | 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.Public | BindingFlags.Instance, null, instance, null);
}
private bool disposed = false;
protected override void Dispose(bool disposing)
{
if (!disposed)
{
if (disposing)
{
Cache.Clear();
}
disposed = true;
}
base.Dispose(disposing);
}
}
7 comments:
Thanks for keeping this series up to date! It's been a great help.
EF 6.1 way for getting the tablename:
public static string GetTableName(Type type, DbContext context)
{
var metadata = ((IObjectContextAdapter)context).ObjectContext.MetadataWorkspace;
// Get the part of the model that contains info about the actual CLR types
var objectItemCollection = ((ObjectItemCollection)metadata.GetItemCollection(DataSpace.OSpace));
// Get the entity type from the model that maps to the CLR type
var entityType = metadata
.GetItems(DataSpace.OSpace)
.Single(e => objectItemCollection.GetClrType(e) == type);
// Get the entity set that uses this entity type
var entitySet = metadata
.GetItems(DataSpace.CSpace)
.Single()
.EntitySets
.Single(s => s.ElementType.Name == entityType.Name);
// Find the mapping between conceptual and storage model for this entity set
var mapping = metadata.GetItems(DataSpace.CSSpace)
.Single()
.EntitySetMappings
.Single(s => s.EntitySet == entitySet);
// Find the storage entity set (table) that the entity is mapped
var table = mapping
.EntityTypeMappings.Single()
.Fragments.Single()
.StoreEntitySet;
// Return the table name from the storage entity set
var name = (string)table.MetadataProperties["Table"].Value ?? table.Name;
var schema = (string)table.MetadataProperties["Schema"].Value ?? table.Schema;
return string.Format("[{0}].[{1}]", schema, name);
}
One piece of awesome code and runs very fast!
VDR is a win-win service for any company. The advantages are countless. Humanity is becoming increasingly remote from the problems with the human factor. virtual data room reviews
Post a Comment