Insert order problem when there is no deterministic order, help

Question

Hello everyone,

I have this table in my DB:

Person
PersonID uniqueidentifier PK
Name      varchar(100)
ID           int IDENTITY (1,1)


My problem is that I do the following:

context.Add(New Person { PersonID=Guid.NewGuid(), Name = "1st"});
context.Add(New Person { PersonID=Guid.NewGuid(), Name = "2nd"});
context.Add(New Person { PersonID=Guid.NewGuid(), Name = "3rd"});
context.Add(New Person { PersonID=Guid.NewGuid(), Name = "4th"});
context.Add(New Person { PersonID=Guid.NewGuid(), Name = "5th"});
context.Add(New Person { PersonID=Guid.NewGuid(), 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

So after reading this: http://social.msdn.microsoft.com/forums/en-US/adodotnetentityframework/thread/d8448144-05dd-4c34-b93c-9336c3b62f6e/

I tried with Colin Meek's advice:

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


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

Why is not working? What is going on? Is there anyway I can get this to insert in the right order?

Thanks

Answer 1

Colin Meek said:

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

In my case the Key is a uniqueidentifier which I generate by using Guid.NewGuid(). I believe this is why is not inserting in the correct order.

Is there a way for objectContext to sort by the column ID which is an INT Identity? If not, what other suggestion would you have? I cannot change the structure of that table. Basically I need some kind of work around as soon as possible.

Colin, Diego, MatthieuMEZIL, Daniel, anyone?

Thanks

Answer 2

Could somebody from the EF's team give me a hand on this? I really need a solution ASAP....

Thanks

Answer 3

Can somebody from the MSFT give me an answer please???

Thanks...

Answer 4

Sorry, but there's really nothing you can do here.  The EF update mechanism follows the rules which Colin has told you, and it's the expectation of the EF's design that applications should not be sensitive to this kind of thing.  The only other thing I can imagine that you could do would be to call SaveChanges separately between each add...

- Danny

---

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

Answer 5

Daniel,

Thanks a lot for getting back to me. I guess for now I will call SaveChanges each time I add a new entity....

The EF update mechanism follows the rules which Colin has told you, and it's the expectation of the EF's design that applications should not be sensitive to this kind of thing.

When you say that applications should not be sensitive to this kind of things I'm assuming you are talking about the insert order in a table like my example. If that’s the case then I have to disagree with you because data is sensitive and developers need flexibility in CRUD operations in order to achieve certain task. Basically the EF is limiting developers by forcing them to use other technologies to achieve something that it's supported by previous technologies.

Just to give you a quick basic example. Imagine you have a table where you need to insert a family tree so basically you need to inserts things in certain order

Now, has this been changed in EF 4.0? If not, would it be possible to add to EF 4.0 an exception rule so we can insert entities ordered by a column instead of using the default rules like in the example above?

Thanks

Answer 6

Even with a family tree, if you have relationships defined on the table that represent the referential integrity constraints.  The EF will automatically order things properly in order to honor those constraints.  It's very unusual that you should actually have to worry about ordering at all.

- Danny

---

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