Speeding up inserts using Linq-To-Entities - Part 4
Part one is found at http://blog.tanneryd.com/2011/11/speeding-up-inserts-using-linq-to-sql.html.
This version works better if you have nullable columns in your table and it also handles foreign keys. I.e. it will ignore foreign key objects but it requires that you set the foreign key id instead.
partial class MyDataContext { partial void OnCreated() { CommandTimeout = 5 * 60; } public void BulkInsertAll<T>(IEnumerable<T> entities) { entities = entities.ToArray(); string cs = Connection.ConnectionString; var conn = new SqlConnection(cs); conn.Open(); Type t = typeof(T); var tableAttribute = (TableAttribute)t.GetCustomAttributes(typeof(TableAttribute), false).Single(); var bulkCopy = new SqlBulkCopy(conn) { DestinationTableName = tableAttribute.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)); } foreach (var entity in entities) { table.Rows.Add(properties.Select(property => GetPropertyValue(property.GetValue(entity, null))).ToArray()); } bulkCopy.WriteToServer(table); conn.Close(); } private bool EventTypeFilter(System.Reflection.PropertyInfo p) { var attribute = Attribute.GetCustomAttribute(p, typeof (AssociationAttribute)) as AssociationAttribute; if (attribute == null) return true; if (attribute.IsForeignKey == false) return true; return false; } private object GetPropertyValue(object o) { if (o == null) return DBNull.Value; return o; } }
5 comments:
This post was a real timesaver to me today. I cut a data importer runtime down by about half. Thanks!
Your very welcome. Glad it helped!
Cut my insert time by a factor of 10! Sweet.
If you modify the function
private bool EventTypeFilter ( System.Reflection.PropertyInfo p )
{
var attribute = Attribute.GetCustomAttribute (p, typeof ( AssociationAttribute )) as AssociationAttribute ;
if ( attribute == null ) return true;
if ( attribute.IsForeignKey == false ) return true;
return false;
}
with this
private bool EventTypeFilter ( System.Reflection.PropertyInfo p )
{
/ / Exclude column ID for bulk upload
if ( p.Name == "ID" )
return false;
Attribute.GetCustomAttribute columAttr var = (p, typeof ( ColumnAttribute )) as ColumnAttribute ;
if ( columAttr ! = null)
{
if ( columAttr.IsPrimaryKey )
{
columAttr.IsPrimaryKey = false;
return false;
}
else return true;
}
else
return false;
}
BulkCopy ignores IDENTITY column and insert the record with the original ID
Regards
If you modify the function
private bool EventTypeFilter ( System.Reflection.PropertyInfo p )
{
var attribute = Attribute.GetCustomAttribute (p, typeof ( AssociationAttribute )) as AssociationAttribute ;
if ( attribute == null ) return true;
if ( attribute.IsForeignKey == false ) return true;
return false;
}
with this
private bool EventTypeFilter ( System.Reflection.PropertyInfo p )
{
/ / Exclude column ID for bulk upload
if ( p.Name == "ID" )
return false;
Attribute.GetCustomAttribute columAttr var = (p, typeof ( ColumnAttribute )) as ColumnAttribute ;
if ( columAttr ! = null)
{
if ( columAttr.IsPrimaryKey )
{
columAttr.IsPrimaryKey = false;
return false;
}
else return true;
}
else
return false;
}
BulkCopy ignores IDENTITY column and insert the record with the original ID
Regards
Post a Comment