views:

590

answers:

3

I'm using a transaction to insert multiple rows in multiple tables. For these rows I would like to add these rows in order. Upon calling SaveChanges all the rows are inserted out of order.

When not using a transaction and saving changes after each insertion does keep order, but I really need a transaction for all entries.

+2  A: 

The order inserts/updates and deletes are made in the Entity Framework is dependent upon many things in the Entity Framework.

For example if you insert a new Product in a new Category we have to add the Category before the Product.

This means that if you have a large set of changes there are local ordering constraints that we must satisfy first, and indeed this is what we do.

The order that you do things on the context can be in conflict with these rules. For example if you do this:

ctx.AddToProducts(
   new Product{
      Name = "Bovril",
      Category = new Category {Name = "Food"}
   }
);

the effect is that the Product is added (to the context) first and then when we walk the graph we add the Category too.

i.e. the insert order into the context is:

Product
Category

but because of referential integrity constraints we must re-order like this before attempting to insert into the database:

Category
Product

So this kind of local re-ordering is kind of non-negotiable.

However if there are no local dependencies like this, you could in theory preserve ordering. Unfortunately we don't currently track 'when' something was added to the context, and for efficiency reason we don't track entities in order preserving structures like lists. As a result we can't currently preserve the order of unrelated inserts.

However we were debating this just recently, so I am keen to see just how vital this is to you?

Hope this helps

Alex

Program Manager Entity Framework Team

Alex James
Thanks for the explanation. It would certainly save me some time if it were supported, but I guess I could also generate my own custom primary keys to achieve the same effect.
Wouter
There are some other responses to the question below. I got around the issue by ordering on timestamps or what not instead of the primary key.
Wouter
Any update on this? I have a situtation where my tables have a constraint on an Alternate Key, so there is no visible relationship on the model. (A.K. is userName, instead of userId). So how can I change the order of inserts, as of now, when I add the user and then add the user to a group, savechanges fails.
hazimdikenli
A: 

Hi Alex,

I'm in the process of crossing this bridge. I'm replacing NHibernate with EF and the issue that I'm running across is how lists are inserted into the DB. If I add items to a list like so (in pseduo code):

list.Add(testObject); list.Add(testObject1);

I'm not currently guaranteed to get the same order when is run 'SaveChanges'. That's a shame because my list object (i.e. linked list) knows the order it was created in. Objects that are chained together using references MUST be saved to the DB in the same order. Not sure why you mentioned that you're "debating" this. =)

not sure if he will notice when you respond here, you should probably also comment on his answer
Wouter
A: 

I share opinion with Nick, I am having same the same problem and this is not for debate :)

Adin