SqlBulkCopy for Generic List<T> (useful for Entity Framework & NHibernate)

January 26th, 2012

A common complaint of the Entity Framework is slow insert times for larger datasets. Last night I was trying to insert a catalog of 15k products and it was taking a very long time (I gave up after 5 minutes). I recalled this post a while back from Mikael Eliasson demonstrating SqlBulkCopy using .NET. I had used BCP in SQL server, but not from .NET. I took Mikael’s example and roughed out a reusable generic version below, which produced 15k inserts in 2.4s or +- 6200 rows per second. I upped it to 4 catalogs, 224392 rows in 39s, for +- 5750 rps (changing between 4 files). These are pretty decent records too, 41 columns and a few of the fields have a meaty char count. Good enough I say.

public static void BulkInsert<T>(string connection, string tableName, IList<T> list)
{
     using (var bulkCopy = new SqlBulkCopy(connection))
     {
          bulkCopy.BatchSize = list.Count;
          bulkCopy.DestinationTableName = tableName;

          var table = new DataTable();
          var props = TypeDescriptor.GetProperties(typeof(T))
                                     //Dirty hack to make sure we only have system data types 
                                     //i.e. filter out the relationships/collections
                                     .Cast<PropertyDescriptor>()
                                     .Where(propertyInfo => propertyInfo.PropertyType.Namespace.Equals("System"))
                                     .ToArray();
              
          foreach (var propertyInfo in props)
          {             
               bulkCopy.ColumnMappings.Add(propertyInfo.Name, propertyInfo.Name);
               table.Columns.Add(propertyInfo.Name, Nullable.GetUnderlyingType(propertyInfo.PropertyType) ?? propertyInfo.PropertyType);
          }

          var values = new object[props.Length];
          foreach (var item in list)
          {
               for (var i = 0; i < values.Length; i++)
               {
                    values[i] = props[i].GetValue(item);
               }

               table.Rows.Add(values);
          }

          bulkCopy.WriteToServer(table);
     }
}

This works off just a basic list of items which property names match the table column names. Given that most POCO based ORM’s generally match the schema exactly, it works great with EF code first objects.

To use I just build up a list of objects, pick the connection string off the DbContext and then call BulkInsert to save to the DB. Note that in this case I am just adding items to a List<T>, not the EF DbSet<T>.

var imports = new List<Product>();
//Load up the imports

//Pass in cnx, tablename, and list of imports
BulkInsert(context.Database.Connection.ConnectionString, "Products", imports);

  • Bruno Canettieri

    Just to remember the default behavior for SqlBulkCopy:
    1- Ignore some constraints (like FKs)
    2- Use default column values instead of null

    An example of the last one is
    create table Products ( /*…*/ registerDate datetime default getdate() )
    When you bulkInsert even if you pass null, the value on registerDate will be the default.

    To overcome them you have to use some options:

    using (var bulkCopy = new SqlBulkCopy(ConnectionString, 
       SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.KeepNulls))

    • http://www.Marisic.Net dotnetchris

      Probably neither of those SqlBulkCopyOptions would want to be used for normal scenarios.

    • http://twitter.com/jarodf Jarod Ferguson

      Hey thanks for pointing out the add’l options, could be useful to tweak those knobs.

  • http://www.Marisic.Net dotnetchris

    Awesome post Jarod!

    • http://twitter.com/jarodf Jarod Ferguson

      Thanks!

  • Anonymous

    FWIW, I’d been using David Browne’s AsDataReader, but it doesn’t seem to handle the needed ColumnMappings like this does. Nice!

    http://archive.msdn.microsoft.com/LinqEntityDataReader/

  • Mark

    Simple.Data’s bulk insert uses SqlBulkCopy.