How to bulk insert in Entity Framework v4?

Question

I have been searching for a solution all day but couldn't find any so here I am, looking for some help.

I have three tables for saving groups & their contacts in my database. For simplicity sake, consider their name as table_1, table_2 & table_3

table_1 (Group Table)

+----------+------+
| Group_Id | Name |
+----------+------+
|        1 | A    |
|        2 | B    |
|        3 | C    |
+----------+------+

table_2 (Contact Table):

+------------+-------+--------+
| Contact_Id | name  | number |
+------------+-------+--------+
|          1 | Jack  |    123 |
|          2 | Sam   |    456 |
|          3 | Alice |    789 |
+------------+-------+--------+

table_3 (Junction Table)

+-----------------+----------+------------+
| ContactGroup_Id | Group_Id | Contact_Id |
+-----------------+----------+------------+
|               1 |        1 |          1 |
|               2 |        2 |          2 |
|               3 |        2 |          3 |
+-----------------+----------+------------+

Now I am reading excel file in my web application and then adding bulk contacts to database. Using EF4, this is what I am trying to do :

using (TransactionScope obj = new TransactionScope(TransactionScopeOption.Required, new TransactionOptions { IsolationLevel = System.Transactions.IsolationLevel.ReadUncommitted, Timeout = TransactionManager.MaximumTimeout }))
{
  for (int i = 1; i < totalrows; i++)
   {
    table_2 objTable_2 = new table_2();
    objTable_2.name = "ABC";
    objTable_2.number= "999";
    entity.table_2.AddObject(objTable_2);
    entity.SaveChanges();

    table_3 objTable_3 = new table_3();
    objTable_3.Group_Id = "1";
    objTable_3.Contact_Id = objTable_2.Contact_Id;
    entity.table_3.AddObject(objTable_3);
    entity.SaveChanges();
   }
}

Now as you can see I have to call entity.SaveChanges() every time to get the auto-generated PK of table_2 to insert in table_3. Doing this for hundreds or thousands of records is really slowing my application and I know this is a bad way of doing this.

Is there any other work around for this ? if I do this using any third-party bulk-insert extension or calling SaveChanges() after every couple of hundred records then how do I get auto-generated PK for table_2 to insert in table_3 ?

Did you have a look at my updated answer? Consider marking it as solution if it helped — Benjamin Soulier, Sep 16 '16 at 07:56

score 0 · Answer 1 · edited May 23 '17 at 10:33

0

Have a look at this extension for Entity Framework, which is compatible with EF from version 4.x to 6.x.

I've used it a couple of times, and its works pretty well.

Edit:

Solving the PK issue by iserting items within a stored procedure, doing all inserts from it in one call, to avoid thousands of round-trips between .NET code handling inserts and SQL Server

edited May 23 '17 at 10:33

Community

1
1

answered Aug 02 '16 at 11:12

Benjamin Soulier

2,223
1
18
30

I know about this extension but it doesn't solve my auto-generated PK issue. – NewbieProgrammer Aug 02 '16 at 11:18
Sorry for misreading your issue, I would say that in this case you should go building up a Stored Procedure. This would handle all steps at once, removing all roundtrips between your app and your db. – Benjamin Soulier Aug 02 '16 at 11:25
I see. But if I need to process 10000 contacts, then I will have to execute stored procedure 10000 times. Right? won't that be same as this (in terms of performance). And what about transaction ? how can I do that in stored procedure for all the records ? – NewbieProgrammer Aug 02 '16 at 11:42
When I thought of a stored Procedure, it was to give it all you data (or in smaller chunks of 1000) to your stored procedure, which will then manage inserting, grabing new Ids to insert additionnal rows. – Benjamin Soulier Aug 02 '16 at 11:44

How to bulk insert in Entity Framework v4?

1 Answers1