Click here to Skip to main content
13,196,730 members (67,561 online)
Click here to Skip to main content
Add your own
alternative version


9 bookmarked
Posted 26 Aug 2013

Using SQL bulk copy with your LINQ-to-Entities datacontext - Part 2

, 26 Aug 2013
Rate this:
Please Sign up or sign in to vote.
This tip shows how to speed up inserts using POCO objects and the entity framework version 5.

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;

        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


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


This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)


About the Author

Måns Tånneryd
Software Developer (Senior)
Sweden Sweden
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralMy vote of 5 Pin
patel_pratik27-Aug-13 2:04
memberpatel_pratik27-Aug-13 2:04 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Praise Praise    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

Permalink | Advertise | Privacy | Terms of Use | Mobile
Web03 | 2.8.171019.1 | Last Updated 26 Aug 2013
Article Copyright 2013 by Måns Tånneryd
Everything else Copyright © CodeProject, 1999-2017
Layout: fixed | fluid