Unpredictable order for UPDATE statements during SaveChanges

Discussion of open issues, suggestions and bugs regarding Entity Framework support in ADO.NET Data providers
Post Reply
Posts: 9
Joined: Mon 15 Feb 2010 19:23

Unpredictable order for UPDATE statements during SaveChanges

Post by MonDeveloper » Mon 11 Apr 2011 16:40

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!

Devart Team
Posts: 2919
Joined: Mon 07 Jul 2008 13:16

Post by AndreyR » Tue 12 Apr 2011 12:14

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

Posts: 9
Joined: Mon 15 Feb 2010 19:23

Post by MonDeveloper » Tue 12 Apr 2011 13:28

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

Post Reply