Batch Insert with Entity Framework – LINQ to Entity

Batch Insert with Entity Framework – LINQ to Entity

Entity Framework – LINQ to Entity is a good idea for batch insert? Such as more than one thousand records.

When you try to do batch operations, you should be carefully using not only Entity Framework but also most of the ORM tools. The main problem is the context lifetime and according to that memory leaks. Context acts as a cache and in batch operations the size of context increases very quickly and that causes fatal errors or makes the speed of the operations too slow that is unacceptable!

If you use any ORM tools, it is not a good idea using that in batch operation (if it does not have a specific module). For the Entity Framework: After inserting about 1000 records, Entity Framework cause a memory leak and for the 20 0r 30 thousand of records the operation never ends.

You maybe divide the records in to small parts and after inserting each part you can create a new context instance and dispose the previous to avoid  memory leak but the speed! However, if you want to use transaction, you cannot divide the records.

{

int counter = 0;

using (MyEntities dataModel = new MyEntities ())

{

while (counter < 1000) { //Insert counter++; } } } For now the best way for batch operations is; not using the Entity Framework. There are lots of ways to do batch operations without Entity Framework. One of them is using SqlBulkCopy or using native Ado.Net classes. http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlbulkcopy.aspx

I hope that ORM tools and Entity Framework improve themselves in this side.

Leave a Reply

Your email address will not be published. Required fields are marked *