Unpredictable order for UPDATE statements during SaveChanges

[MonDeveloper]

Hi guys, I have a problem related to the order of the update statements during the SaveChanges() command. I try to explain further with a simple example.

We have 2 tables:

• Persons
  PersonId number(10) [PK]
  FiscalCode varchar2(30)

• PersonAliases
  PersonAliasesId number(10) [PK]
  PersonId number(10) [AK1,1] [FK with Persons]
  Name varchar2(16)
  PriorityNumber Number(2) [AK1,2]

On the PersonAliases table we have a unique index XAK1_PersonAliases that garantees the uniqueness of the tuple (PersonId, PriorityNumber).

The data present into DB are:

• Persons (1 row)
  1 - CF001

• PersonAliases (4 rows)
  101 - 1 - Luc - 1
  102 - 1 - Lucas - 2
  103 - 1 - Luk - 3
  104 - 1 - Luke - 4

These are our c# steps:
1. Retrieve from DB the person with PersonID = 1 (1 row retrieved) .
2. Load the Person's NavigationProperty PersonAliases (4 rows retrieved).
3. Get PersonAlias with PersonAliasId = 102 and set its PriorityNumber = 99.
4. Get PersonAlias with PersonAliasId = 104 and set its PriorityNumber = 2.
5. Context.Persons.ApplyChanges(p);
6. Context.SaveChanges();


At this point, sometimes, we receive an error from DB due to the XAK1_PersonAliases unique index!

Using dbMonitor we understood that the produced update statements are 2 as expected but the order is unpredictable, sometimes the first one is the update of the PersonAlias with PersonAliasID=102, sometimes the 104 and this is the key point!

We know that EF has in charge to detect the correct order of the statements following the relations between tables and thanks god for it, it works perfectly in case of insert and delete but in case of update?

There is a way in wich we can force the order of these update statements?
If not, any other workaround?


Finally, at this link you can find a thread for a similar problem, unfortunately already open!


Thanks very much guys for the support!
mM

[AndreyR]

The order of the updates is being chosen by the EF engine. I recommend you to call the SaveChanges method after each update.

[MonDeveloper]

This would be against the pattern "Unit of Work".
I need only one transaction to guarantee ACID approach.