Bulk insert using AddRange in Entity framework

c# entity-framework

Question

I am adding multiple entities in the database using AddRange in Entity Framework:

foreach (string tagNumber in notPresent)
{
    element = new TagMaster { Name = Guid.NewGuid().ToString(), IsActive = true };
    element.TagCollections.Add(new TagCollection { TagNumber = tagNumber });
    newTagMasters.Add(element);
}
dbContext.TagMasters.AddRange(newTagMasters);
dbContext.SaveChanges();

What I was expecting is that by adding the complete collection in context using AddRange method, there would be a single query that will be sent to database. But to my surprise, I see multiple insert statements for each record to be inserted.

Any Insights?

1
0
10/27/2015 1:09:57 PM

Popular Answer

The problem you are running in is that sadly the entity framework commands know NO bulk inserts. Instead they generate 1 statement per line that you want to insert. There is no workaround to this.

The only possiblity to get 1 single statement that does all the inserts is to use specific classes or libraries. As example here SqlBulkCopy which needs no external lib to be downloaded to work.

Here a link to the msdn site: https://msdn.microsoft.com/de-de/library/system.data.sqlclient.sqlbulkcopy(v=vs.110).aspx

The usage is quite easy. You only give the constructor your connection (after opening it beforehand!) and tell it what it shall write to teh server and what the destination table name is. Then you only need to close the connection afterwards again.

sqlcon.Open();
using (SqlBulkCopy sqlBulkCopyVariable= new SqlBulkCopy(sqlcon))
{
    sqlBulkCopyVariable.BulkCopyTimeout = 600; // 10 minutes timeout
    sqlBulkCopyVariable.DestinationTableName = "targetTableName";
    sqlBulkCopyVariable.WriteToServer(MyData);
}
sqlcon.Close();

The WriteToServer takes DataTable, DataReader or even arrays of DataRow. The exact implementation there would depend on how you want to give the data to it. So far from my personal experience: That class is quite fast and generates only 1 single statement. BUT it is only there for SqlClients. Thus if you have a different client you need to look up which class or external library would be best fitting for you.

3
10/27/2015 9:20:32 PM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow