CODEWITHSUNDEEP
c#entity-frameworkentity-framework-6entity-framework-core
Adam
Adam

Reputation: 4174

What logic determines the insert order of Entity Framework 6

So, I have a DBContext, and I am doing the following operations:

dbContext.SomeTables1.Add(object1)
dbContext.SomeTables2.AddRange(objectArray2)
dbContext.SomeTables3.AddRange(objectArray3)
dbContext.SaveChanges();

The EF doesn't insert the db records in this order, it inserts them in a random order. To insert them in the same order, I have to do a dbContext.SaveChanges() after each addition. This is not an efficient solution and in my case, it is taking 10 seconds to do all my inserts, while the random order with one save takes around 3 seconds.

N.B. I need the right order to solve a deadlock issue.

My questions are:

  • Is this issue resolved in EF7?
  • I can profile EF and determine the random order, however, is there a guarantee that it will be consistently with the same random order or does it change between requests? (I can adopt my other code if the answer to this question is positive).
  • Is there a better way of maintaining the order than dbContext.SaveChanges() on every addition?

Upvotes: 30

Views: 18519

Answers (4)

Jonathan Magnan
Jonathan Magnan

Reputation: 11347

  • There is no way you can specify a save order in EF6 or EF Core
  • The issue is not resolved in EF Core since this is not an issue.
  • The order will be the same if the predecessor is the same (which will likely rarely happen)

When you call SaveChanges, all entities are ordered from an internal order in the method “ProduceDynamicCommands” then sorted again by the method “TryTopologicalSort” which loops to add command with no predecessor left (if you add A and B and A depend on B, then B will be inserted before A)

You are left to insert by batch addition.

Since it takes you 3 seconds to perform your insert, I will assume you have thousands of entities and performing bulk insert may improve your performance to reduce the 10 seconds to less, and then maybe the initial 3 seconds!

To improve your performance, you can use http://entityframework-extensions.net/ (PAID but support all cases)

Disclaimer: I'm the owner of the Entity Framework Extensions project.

Upvotes: 19

keynem
keynem

Reputation: 94

I've found a very simple solution.

Just set the property for the ID (primary key) of the entity to a value that matches your desired order. SaveChanges() first sorts by this ID, then by other properties. The assigned ID may already exist in the database. A unique ID is assigned when writing to the database.

for(int i = 0; i < objectArray2.Count(); i++)
{
   objectArray2[i].Id = i;
}

dbContext.SomeTables2.AddRange(objectArray2)

Upvotes: 0

Gichamba
Gichamba

Reputation: 997

To explicitly set the values of the Primary Keys (and hence the order of the Clustered Index) in an Identity column in EF and EF Core, you need to manually turn on IDENTITY_INSERT before calling _context.SaveChanges() after which you need to turn off IDENTITY_INSERT like so: This example assumes EF Core

// Add your items with Identity Primary Key field manually set
_context.SomeTables1.AddRange(yourItems);
_context.Database.OpenConnection();
try {
      _context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.SomeTables1 ON");
      _context.SaveChanges();
      _context.Database.ExecuteSqlRaw("SET IDENTITY_INSERT dbo.SomeTables1 OFF");
} finally {
      _context.Database.CloseConnection();
}

Upvotes: 0

Olivier Quirion
Olivier Quirion

Reputation: 106

I've found a way to do it. It just thought I'd let you know:

using (var dbContextTransaction = Context.Database.BeginTransaction())
{
   dbContext.SomeTables1.Add(object1);
   dbContext.SaveChanges();

   dbContext.SomeTables1.Add(object2);
   dbContext.SaveChanges();

   dbContextTransaction.Commit();
}

Upvotes: 4

Related Questions