Entity Framework 4.0: when I try to save some changes with unique constraint I get an exception

Question

Well, I am trying to save a tree in a database, so I am using the modified preorder tree traversal method.

I have a table with the following columns:

IDNode
IDRootNode
IDParentNode
Left
Right

In my table, I have two unique constraints, (IDRoot, Left) and (IDRoot, right) because in a tree, there is not possible to have two nodes with the same root and the same left, or the same root and the same right. This is the reason to use this two constraints.

Well, now, I have a tree with three nodes:

Node A
IDNode = 1
IDRootNode = 1
IDParentNode = 1
Left = 1
Right = 6

Node B
IDNode = 2
IDRootNode = 1
IDParentNode = 1
Left = 2
Right = 3

Node C
IDNode = 3
IDRootNode = 1
IDParentNode = 1
Left = 4
Right = 5

Now I am trying to add a new child in the B node. So with some operations, before save changes in the database, I get the following state of the nodes:

Node A
IDNode = 1
IDRootNode = 1
IDParentNode = 1
Left = 1
Right = 8

Node B
IDNode = 2
IDRootNode = 1
IDParentNode = 1
Left = 2
Right = 5

Node C
IDNode = 3
IDRootNode = 1
IDParentNode = 1
Left = 6
Right = 7


New node D
IDNode = 3
IDRootNode = 1
IDParentNode = 2
Left = 3
Right = 4

This is the correct data for all the nodes, but when I submit the changes, I get a Unique Key exception, because I am trying to modify the left/right values.

The problem is with the node B, I am trying to set the right field to 5, but this value is assigned to the node C in the database.

So it makes me think that when EF makes changes, it apply the changes to the current register and compare the data with the information in the database, not with the information of the entities in the context. So when I try to update the node B, It does not take account of the entity value but the value in the database, so how in the database the node C has a value of 5 in the right field, SQL Server throw an error because of the unique constraint. If EF would take account of the entity data, in which the node C has a 7 value, there would not be problems.

If I delete the constraint in the database, the all works fine and the data is coherent, but then I would design the database according to the use of EF, and the design would be independent of the data access technology.

Am I right? is it possible with EF to make changes that take in account other possible changes in other entities?

Thanks. Daimroc.

Answer 1

What you show in your example doesn't look like a tree at all. Let's check your modified sample:

• Node A has ID = 1, RootID = 1 and ParentID = 1. How can a root node have a parent? ParentID should be NULL.
• Node A has LeftID = 1. So it is pointing to itself? What kind of tree is that? Even in threaded tree nodes usually doesn't point to itself.
• Node B has ParentID = 1 but Node A has neither LeftID = 2 or RightID = 2. How can Node A be direct parent of Node B when Node B is not direct child of Node A?
• And many more similar issues
• Btw. how do you model leafs or incomplete nodes (node where LeftID = NULL or RightID = NULL) with your unique constraints? Those constrains say that there can be only single node having LeftID = NULL and single node having RightID = NULL

Now to the problem with EF. EF doesn't understand unique constraints. EF also doesn't perform any additional queries to database to check the state. All queries are executed by you (even lazy loading is executed by your code).

What you found is most probably problem of incorrect order of update commands executed in the database where EF is trying to update Node B before it updates Node C. Order of EF updates is driven by relationships in your database but bidirectional relationship can cause that EF is not able to select correct order of the commands. In such case it usually throws exception so it leads me to suspicion that you don't have tree structure modeled on table with self referencing relationships. In such case EF will not be able to assign correct command order.

I expect that in this case you will have to write updates manually to perform the update.