Order of inserts

Question

Hi!
I have an instance of an ObjectContext, I add about 50 objects of the same type using AddObject and then SaveChanges is executed.
I would expect that the objects are inserted into database in the order they where added to the context, but this is not the case. They seem to be added in random order. Since this is definitely not the case, does anybody know how the ObjectContext determines the order of inserts?
Important: I'm not talking about referenced data here. Just a list of flat objects that map to one table.

Answer 1

Order of AddObject() calls does not imply the order of INSERTs.

 

If you need the actual INSERTs happening in some particular order, you can force it by calling SaveChanges() after every single AddObject().

 

If you're only interested in getting consecutive primary key values in the database, you can achieve this assigning all key values on the client (using your own primary key generator).

 

Let me know if this strategy will work for you.

 

Answer 2

Hi and thanks for helping out,
your reply partially answers my question. One thing is how I will achieve some specific order I desire. I can do one the things you suggested.
The other thing though is what strategy does Entity Framework uses in general. I see it does not use the order with which the objects were added to the context and I'm sure it does not inserts the objects in random order. I also hope that this is not some kind of secret, since its important for the user of ObjectContext to know.
So, can you or somebody else describe the strategy of order of inserts? I repeat that I'm interested to know the order of plain, flat insert of multiple objects of the same type, without children data.

Answer 3

Anybody?

---

Dimitris Papadimitriou, Software Development Professional

Answer 4

 

Hi,

I'll go through all of the ordering rules just to establish context. I believe the last rule is the one affecting your scenario.

The Entity Framework uses a few rules to determine precedence between operations (inserts, deletes and updates). Here are some of the rules:

• Foreign keys: when the SSDL declares a foreign key (via an association type and association set), the EF will ensure that the principal end is inserted before the dependent end. Similarly, the dependent end will be deleted before the principal end. Updates introduce the expected ordering as well.
• Server generated values: the CSDL model constrains certain values to be equivalent. For instance, a relationship instance is constrained to have the same key values as the entities it relates. A referential integrity constraint in the model implies common values across entities as well. In these cases, the EF introduces an ordering dependency when a particular operation generates a new key value (e.g. inserting a row with an identity column).

If you are using stored procedures to modify your entities, another rule also comes into play:

• Relationships are assumed to be dependent on entities.

Of course, in your scenario there are no strong ordering constraints... When there are no foreign key constraints, server generated values or stored procedures constraining the order of operations, the EF sorts by:

• Operation
• Table or stored procedure name
• Key values

Thanks,
-Colin

---

This posting is provided "AS IS" with no warranties and confers no rights.

Answer 5

Hi Colin,
you said that EF sorts by Operation. What do you mean?
My problem is that I do the following:

context.Add(New Person { Name = "1st"});
context.Add(New Person { Name = "2nd"});
context.Add(New Person { Name = "3rd"});
context.Add(New Person { Name = "4th"});
context.Add(New Person { Name = "5th"});
context.Add(New Person { Name = "6th"});
context.SaveChanges();

ID column of Person entity is Auto Increment in database.
I would expect to have this result (taken for granted that table is brand new)...
ID | Name
==========
1  | 1st
2  | 2nd
3  | 3rd
4  | 4th
5  | 5th
6  | 6th

However I get this!...
ID | Name
==========
1  | 3rd
2  | 2nd
3  | 1st
4  | 6th
5  | 5th
6  | 4th

What's going on?

---

Dimitris Papadimitriou, Software Development Professional

Answer 6

I wish somebody could answer to this!

---

Dimitris Papadimitriou, Software Development Professional

Answer 7

Hi *,

and what's the problem of getting IDs not same as Persons created? For database it's just data - nothig more, nothing less. And the PK is just identifier, so it shouldn't have some hidden magic, like expecting to get some value before another.

---

Jiri {x2} Cincura

Answer 8

Dear friend,
If it is a problem or not is another issue!
The question of this thread is what is the strategy used by Entity Framework to insert new entities (simple entities that map 1 to 1 with database tables and without relations). As I said above I'm sure that the order of inserts is not random. There is a strategy and I suppose its not a secret. I'm wondering why isn't there is anybody from the EF team to answer this simple question!
Now, regarding your comment, one reason (I'm sure there are more) to require inserts to happen in the proper order is because an end-user would expect this to happen! Another reason could be that this was happening in all database access frameworks I used so far, including ADO Recordsets and ADO.NET DataAdapters etc.

---

Dimitris Papadimitriou, Software Development Professional

Answer 9

The insert order is a product of the graph walking algorithm in the System.Data.Mapping.Update.Internal.Graph class. Obviously, the algorithm does not preserve the original order if there are no edges, ie. no relationships between the objects.

As long as the graph walking algorithm is fast and correct, it shouldn't matter that the order is not preserved. In fact, I would be wary of any code that depends on the order of execution. This could mean that cursor-like logic has crept in my code and I may have to deal with performance and concurrency issues. Actually, I would be wary of SQL code that smelled of cursor-like logic as well.

Answer 10

Hi Panagioti,
this is the first straight answer I get!
As far as your second paragraph is concerned, the fact that I want to know what is going on does not mean that I really need to rely on a specific order. Its just that one should know what a framework is doing under the hood to appreciate it. So if its not some kind of secret it would be nice if the EF team shared this information, without dodging the question.
I can accept that we need to forget about ordering for the sake of performance but this does not mean that preserving the order would not be a nice feature to have.

---

Dimitris Papadimitriou, Software Development Professional

Answer 11

In your example, there is no deterministic order because every operation is indistinguishable based on the criteria I outlined:

• The key values are all the same (initially).
• The same table is being targeted for all of the entities.
• There is no foreign key constraints.
• They're all inserts.

You can control the order in your example by setting key values on your entities, though those key values will be overridden in the database.

context.Add(New Person { ID = 1, Name = "1st"});
context.Add(New Person { ID = 2, Name = "2nd"});

Thanks,
-Colin

---

This posting is provided "AS IS" with no warranties and confers no rights.

Answer 12

Hi Colin,
this is nice to know. I will try it out.
I cannot agree with the fact that there is no deterministic order in my example. The order of adding the objects to the context cannot be more deterministic. The point is that this order is not taken under consideration from the framework.
Anyway, the suggestion you propose seems pretty straightforward solution. I wouldn't even call it a workaround!

---

Dimitris Papadimitriou, Software Development Professional

Answer 13

Just wanted to verify that Colin's suggestion works for me.
So, indeed, explicitly specifying ID (even if it will be replaced later because of auto-increment setting in DB) enforces order of inserts!
I wonder if this is an officially supported solution or works by accident (meaning it may break in future release).

---

Dimitris Papadimitriou, Software Development Professional

Answer 14

The Entity Framework will continue to use key values to order operations. The behavior is not accidental: ensuring a consistent order of operations --  independent of internal data structures and the order in which the user tells the ObjectContext about changes -- minimizes the risk of a deadlock in the database when multiple applications are manipulating entities.

Thanks,
-Colin

---

This posting is provided "AS IS" with no warranties and confers no rights.