Reputation: 191
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 ?
Upvotes: 3
Views: 2997
Reputation: 11337
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:
Example
// Easy to use
context.BulkSaveChanges();
// Easy to customize
context.BulkSaveChanges(bulk => bulk.BatchSize = 100);
Upvotes: 2
Reputation: 23078
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:
Guid RefProperty
to the ParentObject
type which is also persistedGuid BatchId
to the ParentObject
type which is also persistedGuid RefProperty
to the ChildObject
type which is not persistedSave 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.
Upvotes: 1