Friday, April 11, 2014

Speeding up inserts using the Entity Framework - Part 4 (EF 6.1)

This is yet another post about speeding up inserts using the entity framework. Previously I have written about this in the following posts.
  1. Speeding up inserts using Linq-To-Entities - Part 3 (EF 6.0)
  2. Speeding up inserts using Linq-To-Entities - Part 2 (EF 5)
  3. Speeding up inserts using Linq-To-Entities - Part 1
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;

            Type t = typeof(T);
            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;


        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(
                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(
                BindingFlags.GetProperty | BindingFlags.Public | BindingFlags.Instance, null, instance, null);
            var list = new ArrayList();
            foreach (var o in objects)
            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)
                disposed = true;


William Pan said...

Thanks for keeping this series up to date! It's been a great help.

Måns Tånneryd said...
This comment has been removed by the author.
WAB69 said...

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
.Single(e => objectItemCollection.GetClrType(e) == type);

// Get the entity set that uses this entity type
var entitySet = metadata
.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(s => s.EntitySet == entitySet);

// Find the storage entity set (table) that the entity is mapped
var table = mapping

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

John Barness said...
This comment has been removed by a blog administrator.
Toby Valentine said...
This comment has been removed by a blog administrator.
Ringes said...

One piece of awesome code and runs very fast!

Sun Ivey said...

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