SqlBulkCopy and Entity Framework

.net c# entity-framework sqlbulkcopy

Question

My current project consists of 3 standard layers: data, business, and presentation. I would like to use data entities for all my data access needs. Part of the functionality of the app will that it will need to copy all data within a flat file into a database. The file is not so big so I can use SqlBulkCopy. I have found several articles regarding the usage of SqlBulkCopy class in .NET. However, all the articles are using DataTables to move data back and forth.

Is there a way to use data entities along with SqlBulkCopy or will I have to use DataTables?

Expert Answer

As complement to @DaveHogan answer,

There is now new libraries which allow performing Bulk Insert (using SqlBulkCopy under the hood) for Entity Framework using data entities instead of DataTable.

Disclaimer: I'm the owner of the project Entity Framework Extensions

This library is not free but allows to easily perform:

  • BulkSaveChanges
  • BulkInsert
  • BulkUpdate
  • BulkDelete
  • BulkMerge

Under the hood, SqlBulkCopy is used. Using extension method make is way easier/faster than coding a custom solution for every Bulk Insert to use.

Example

// Easy to use
context.BulkSaveChanges();

// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);

poor performance

I'm sorry you are getting poor performance,

Make sure you are not including time from some common mistake like forgetting to JIT Compile, using Add instead of AddRange which impact the test performance but is not related to our library.

Most people report getting between 25-50x performance improvement when excluding all common performance benchmark mistake.

See: Entity Framework Extensions - Benchmark


Popular Answer

You'll need to convert the Entities to a IDataReader or DataTable.

There is a small helper class designed to assist with with: http://archive.msdn.microsoft.com/LinqEntityDataReader/Release/ProjectReleases.aspx?ReleaseId=389

EDIT: msdn link is broken, alt copy can be found here: https://github.com/matthewschrager/Repository/blob/master/Repository.EntityFramework/EntityDataReader.cs

Then you can use SqlBulkCopy like so:

var sbCopy= new SqlBulkCopy(connectionString);
sbCopy.DestinationTableName = "TableName";
sbCopy.WriteToServer(entitiesList.AsDataReader()); 



Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Is this KB legal? Yes, learn why