Skip to main content

How to insert many records into the database using Entity Framework (bulk insert)

It's using SqlBulkCopy but as both Entity Framework and SqlBulkCopy belong to ADO.NET...

Posted in Entity Framework, Code snippets, Tutorials

Entity Framework (EF) is a Microsoft ORM which allows programmers to develop against a conceptual data model instead of developing anything inside the database. In this way any change done in this conceptual data model must be persisted as this is not done automatically. For EF we have to use SaveChanges.

To understand EF is simple: A SQL script is generated for every action done against the conceptual model. For example: In case we want to add some record into the User table. For that we would have to use the AddObject method and pass the properties of the User (name, surname, etc) along. Behind the scenes the EF will generate an INSERT and will execute it in the database with the properties of User.

And what about inserting 3.000 records at once?

In this case we have 2 options:

  1. We do a loop and use AddObject for every one of the 3.000 records (not recommended)
  2. We use a class called SqlBulkCopy (deeply recommended)

But why?

The EF doesn't have a native method in case we want to make a bulk insert but, as it was built on top of ADO.NET, there is no problem to use SqlBulkCopy. Not to mention the performance gain if we compare with the multiple INSERTs.

An example of SqlBulkCopy:

Let's just hope Microsoft will add a similar feature into the Entity Framework in the future.

Did you like the article?

You can subscribe and get them as soon they are online, share using the buttons bellow or leave a comment.

And you also can share using your favorite social network: