Click here to Skip to main content
Click here to Skip to main content

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;

        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.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);
    }
}

License

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

Share

About the Author

No Biography provided

Comments and Discussions

 
GeneralMy vote of 5 Pinmemberpatel_pratik27-Aug-13 2:04 

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

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

| Advertise | Privacy | Mobile
Web04 | 2.8.140826.1 | Last Updated 26 Aug 2013
Article Copyright 2013 by Måns Tånneryd
Everything else Copyright © CodeProject, 1999-2014
Terms of Service
Layout: fixed | fluid