We have 2 tables:
PersonId number(10) [PK]
PersonAliasesId number(10) [PK]
PersonId number(10) [AK1,1] [FK with Persons]
PriorityNumber Number(2) [AK1,2]
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.
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!