Entity Framework, bulk inserts and relationship support

Question

I have what seems like a common problem, but I can’t figure out how to achieve the desired result. I have a nested object with navigation properties defined on it, as shown in the following diagram.

The collection of map points can potentially be quite large for a given MapLine, and for MapLayer there can be quite a large number of MapLines.

The question is, what is the best way to use the MapLayer object inserted into the database using the Entity Framework and maintain the relationships that are determined by the navigation properties?

Standard implementation of Entity Framework

dbContext.MapLayers.Add(mapLayer); dbContext.SaveChanges(); 

causes a big burst of memory and pretty bad return times.

I tried to implement the EntityFramework.BulkInsert package but it does not respect object relationships.

It seems that this would be a problem that someone has encountered before, but I cannot find any resources explaining how to complete this task.

Update

I tried to implement Richard's suggestion, but I don’t understand how to do this for a nested object such as the one I described. I assume that I need to insert a MapLayer object, then MapLines, then MapPoints to honor the PF / FK relationships in the database. I am currently trying to execute the following code, but this does not seem to be correct.

 dbContext.MapLayers.Add(mapLayer); dbContext.SaveChanges(); List<MapLine> mapLines = new List<MapLine>(); List<MapPoint> mapPoints = new List<MapPoint>(); foreach (MapLine mapLine in mapLayer.MapLines) { //Update the mapPoints.MapLine properties to reflect the current line object var updatedLines = mapLine.MapPoints.Select(x => { x.MapLine = mapLine; return x; }).ToList(); mapLines.AddRange(updatedLines); } using (TransactionScope scope = new TransactionScope()) { MyDbContext context = null; try { context = new MyDbContext(); context.Configuration.AutoDetectChangesEnabled = false; int count = 0; foreach (var entityToInsert in mapLines) { ++count; context = AddToContext(context, entityToInsert, count, 100, true); } context.SaveChanges(); } finally { if (context != null) context.Dispose(); } scope.Complete(); } 

Update 2

After several attempts to achieve this, I finally gave up and simply inserted MapLayer as an object and saved the MapLines => MapPoints relation as the Json source string in the byte array in the MapLayer object (since I am not asking for those structures, this works for me).

As the saying goes: "It's not bad, but it works."

I had some success with BulkInsert and relationship management outside of EF, but again there was a memory problem when trying to use EF to return data to the system. It seems that at present EF is not able to efficiently process large datasets and complex relationships.

Answer 1

I had a bad experience with enormous context preservation. All these recommendations on saving iterations per 100 lines, per 1000 lines, then deleting the context or the list of clearing and removing objects, assigning a zero value to all, etc. Etc. - it's all shit. We had requirements to insert daily millions of rows into many tables. You should definitely not use an entity in these conditions. You will deal with memory leaks and reduce the insertion speed while continuing iterations.

Our first improvement was to create stored procedures and add them to the model. It is 100 times faster than Context.SaveChanges() , and there are no leaks without slowing down over time.

But this was not enough for us, and we decided to use SqlBulkCopy . It is super fast. 1000 times faster than using stored procedures.

So my suggestion would be: if you have a lot of rows to insert, but a number under something like 50,000 rows, use stored procedures imported into the model; if you have hundreds of thousands of rows, try SqlBulkCopy .

Here is the code:

 EntityConnection ec = (EntityConnection)Context.Connection; SqlConnection sc = (SqlConnection)ec.StoreConnection; var copy = new SqlBulkCopy(sc, SqlBulkCopyOptions.CheckConstraints | SqlBulkCopyOptions.Default , null); copy.DestinationTableName = "TableName"; copy.ColumnMappings.Add("SourceColumn", "DBColumn"); copy.WriteToServer(dataTable); copy.Close(); 

If you use DbTransaction with a context, you can also perform bulk insert using this transaction, but some hacks are needed for this.

Answer 2

Bulk insertion is not the only way to efficiently add data using the Entity Framework - a number of alternatives are described in detail in this answer . You can use the optimizations offered there (disabling change tracking), then you can just add things as usual.

Note that when adding multiple elements at the same time, you need to recreate your context quite often to stop the memory leak and the slowdown you get.