Insertion Order for Multiple Entries in Entity Framework

Question

I am having problems with EF changing my inserts when trying to add an entity with multiple children at the same time. I have a 3-level structure with a one-to-many relationship between each ( Outer 1--* Item 1--* SubItem ). If I try to insert a new Outer with elements and subelements, elements containing SubItems will be inserted first.

Sample code (.NET 4.5, EF 5.0.0-rc):

 public class Outer { public int OuterId { get; set; } public virtual IList<Item> Items { get; set; } } public class Item { public int OuterId { get; set; } [ForeignKey("OuterId")] public virtual Outer Outer { get; set; } public int ItemId { get; set; } public int Number { get; set; } public virtual IList<SubItem> SubItems { get; set; } } public class SubItem { public int SubItemId { get; set; } [ForeignKey("ItemId")] public virtual Item Item { get; set; } public int ItemId { get; set; } } public class MyContext : DbContext { public DbSet<Outer> Outers { get; set; } public DbSet<Item> Items { get; set; } public DbSet<SubItem> SubItems { get; set; } } class Program { static void Main(string[] args) { Database.SetInitializer(new DropCreateDatabaseAlways<MyContext>()); MyContext context = new MyContext(); // Add an Outer object, with 3 Items, the middle one having a subitem Outer outer1 = new Outer { Items = new List<Item>() }; context.Outers.Add(outer1); outer1.Items.Add(new Item { Number = 1, SubItems = new List<SubItem>() }); outer1.Items.Add(new Item { Number = 2, SubItems = new List<SubItem>(new SubItem[] { new SubItem() }) }); outer1.Items.Add(new Item { Number = 3, SubItems = new List<SubItem>() }); context.SaveChanges(); // Print the order these have ended up in foreach (Item item in context.Items) { Console.WriteLine("{0}\t{1}", item.ItemId, item.Number); } // Produces output: // 1 2 // 2 1 // 3 3 } } 

I know this answer from Alex James , which says that inserts may need to be reordered in order to satisfy relational constraints, but this is not a problem here. His answer also mentions that they cannot track the order of elements in order preserving structures such like lists.

I would like to know how I can order these inserts. Although I can rely on sorting my inserted elements by a field other than a PC, it is much more efficient if I can rely on the PK order. I really don't want to use multiple SaveChanges calls to accomplish this.

I use the EF5 RC, but judging by other unanswered questions, this has been around for some time!

Answer 1

What I would like to know is how I can order these inserts.

You can not. The order of the database commands is the internal behavior of EF. If you want to control the order of commands, do not use tools that abstract you from interacting with low levels of the database - use SQL directly.

Edit based on comment:

Yes, this interaction is low, because you have high hopes for the order of SQL commands when working with an abstraction that you do not have under your control. At a high level, you get something else because you use expectations that do not work with this abstraction. If you want to control the order of the SQL commands, you must either force EF, saving the elements in turn (=> several SaveChanges and TransactionScope ), or write the SQL yourself. Otherwise, use a separate column for the order.

Btw. EF does not save the object as you see it. It has its own change tracker containing links to all of your attached instances. Links are stored in multiple instances of the Dictionary , and the dictionary does not preserve the insertion order . If these collections are used to generate SQL commands (and I think they are), the order cannot be guaranteed.

Answer 2

The tables in the database are sets. This means that the order is not guaranteed. In your example, I assume that you want the results to be ordered by Number. If this is what you want, what are you going to do if this number changes and it no longer reflects the order in the database?

If you really want the rows to be inserted in a specific order, you should select several SaveChanges.

The reason that no one wants to call SaveChanges multiple times is because it is just like this: a dirty hack.

Since the primary key is a technical concept, it should not have any functional meaning to order your results on this key. You can order the results for a specific field and use the database index for this. You probably won't see the difference in speed.

Giving an explicit indication of an order has other advantages: it’s easier to understand for people who need to support it. Otherwise, this person should know that the order for the primary key is important and gives the correct results, because in another (completely) unrelated section of your application, it accidentally coincides with the serial number of the field.