Thursday, August 30, 2012

Speeding up inserts using Linq-To-Entities - Part 1

Simply add this partial class,partial to your own generated class, and you will be able to bulk copy your entities. If you're using Linq-To-Sql have a look at this. This will NOT work if you are using EF the POCO style. For that we need to make some adjustments.

    public partial class MyEntities
    {
        partial void OnContextCreated()
        {
            CommandTimeout = 5 * 60;
        }

        public void BulkInsertAll<T>(IEnumerable<T> entities)
        {
            entities = entities.ToArray();
            
            var ec = (EntityConnection) Connection; 
            var conn = (SqlConnection) ec.StoreConnection;
            
            conn.Open();
            Type t = typeof(T);

            var entityTypeAttribute = (EdmEntityTypeAttribute)t.GetCustomAttributes(typeof(EdmEntityTypeAttribute), false).Single();
            var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = entityTypeAttribute.Name };

            var properties = t.GetProperties().Where(EventTypeFilter).ToArray();
            var table = new DataTable();

            foreach (var property in properties)
            {
                Type propertyType = property.PropertyType;
                if (propertyType.IsGenericType &&
                    propertyType.GetGenericTypeDefinition() == typeof(Nullable<>))
                {
                    propertyType = Nullable.GetUnderlyingType(propertyType);
                }

                table.Columns.Add(new DataColumn(property.Name, propertyType));
                bulkCopy.ColumnMappings.Add(new SqlBulkCopyColumnMapping(property.Name, property.Name));
            }

            foreach (var entity in entities)
            {
                var e = entity;
                table.Rows.Add(properties.Select(property => GetPropertyValue(property.GetValue(e, null))).ToArray());
            }

            bulkCopy.WriteToServer(table);
            conn.Close();
        }

        private bool EventTypeFilter(System.Reflection.PropertyInfo p)
        {
            var attribute = Attribute.GetCustomAttribute(p, typeof(EdmScalarPropertyAttribute)) as EdmScalarPropertyAttribute;

            if (attribute != null) return true;

            return false;
        }

        private object GetPropertyValue(object o)
        {
            if (o == null)
                return DBNull.Value;
            return o;
        }
    }

No comments: