how to insert parent child while doing entityframework.bulkinsert?

entity entity-framework entity-framework-4 entity-framework-5 entity-framework-6

Question

I am using Entityframework 6, I am trying to insert a parent-child kind of data in the database. I am using Entityframework.BulkInsert to insert data. I have autoIncrement int primary key in all the tables

My object is as follows :

    var parentObjects= new List<parentObject>();            
    var childObjects= new List<childObject>();

        for (int i = 0; i <= 100; i++)
        {
            var parentObj= new parentObject()
            {
                Name="p1",
                Address="a1"
            };

childObjects= SeedInitializer.ChildItems.OrderBy(x => new Random().Next()).Take(2).ToList();//this gets 2 child objects
            foreach (var childObj in childObjects)
            {
                childObj .ParentObject= parentObj;
                //childObj .CommissionPlanId = i;   //tried this still not working
                parentObj.ChildObjects.Add(childObj );                                     
            }
            parentObjects.Add(parentObj);
        }
        //when I do a quickwatch on parentObjects, i see child objects in each parentObject, but                  
        //with the last id of parentObject
        context.BulkInsert(parentObjects, 1000);
        context.SaveChanges();

On save only 2 records are created in the childObject are created with a wrong parentObject id i.e. 0 I am not able to understand why child items are not getting created, while parent objects are getting created. Can someone help me understand where I am doing the mistake ?

1
2
1/6/2015 5:36:35 PM

Expert Answer

Disclaimer: I'm the owner of EntityFramework.BulkInsert

You cannot.

This feature has never been implemented.

Disclaimer: I'm the owner of Entity Framework Extensions

However, this new library (not free), can easily handle this kind of scenario.

The BulkSaveChanges work exactly like SaveChanges (handle parent/child) but way faster!

All methods are supported:

  • Bulk SaveChanges
  • Bulk Insert
  • Bulk Delete
  • Bulk Update
  • Bulk Merge

Example

// Easy to use
context.BulkSaveChanges();

// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);
1
3/10/2017 10:47:46 PM

Popular Answer

I do not think there is an easy way to accomplish this task, because in order to insert the children, you have to actually finish inserting the parents and get their ids. Normal EF inserts have the advantage that each INSERT will also embed a SELECT to fetch just generated identifier, so that it can use to push it for children (if any).

One possible solution is the following:

  1. Add a Guid RefProperty to the ParentObject type which is also persisted
  2. Add a Guid BatchId to the ParentObject type which is also persisted
  3. Add a Guid RefProperty to the ChildObject type which is not persisted
  4. Save the whole structure by using the following (mainly pseudocode) sequence

    var batchId = new Guid();
    parentObjects.ForEach(item => item.BatchId = batchId);
    
    // set RefProperty for all parents and children to reflect proper parentation 
    
    TransactionScope scope = null;
    try
    {
         context.BulkInsert(parentObjects, 1000);
         var newParents = context.ParentObjects.Where(_ => _.BatchId = batchId);
         var refPropMap = newParents.ToDictionary(_ => _.RefProperty, _ => ParentId);
         var childObjects.ForEach(item => item.ParentId = refPropMap[item.RefProperty]);
         context.BulkInsert(childObjects, 1000);
    
         DataAccess.SaveChanges();
         scope.Complete();
    }
    catch (Exception exc)
    {
         scope?.Dispose();
    }
    

Note: this is not tested

This is quite ugly, but it should do the trick: minimize round-trips to SQL Server and still be one single transaction.

In order to make the SELECT faster, an index on ParentObject table should be placed on BatchId including (covering) its key.

Alternative: change design for these tables to not use auto-increments, but UNIQUEIDENTIFIER columns. This way, all identifiers can be set before making the inserts.



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