R – Why is the Linq to Entities code duplicating rows

entity-frameworkhierarchylinqnet

I'm using the ADO.NET Entity Framework to manage a hierarchy of objects. Each has a single parent and zero or more children.

ADO.NET Entity Framework Object with parent and children http://img14.imageshack.us/img14/7158/thingpi1.gif

I present these in my WPF application with a TreeView control. New Things may be added as children to the selected Things with the click of a button…

Visual Basic…

Private Sub ButtonAddThing_Click(...) Handles ButtonAddThing.Click
    Dim NewThing As New Thing
    NewThing.Id = Guid.NewGuid()
    NewThing.Parent = DirectCast(TreeViewThings.SelectedItem, Thing)
    ...
    db.AddToThing(NewThing)
    db.SaveChanges()
    TreeViewThings.UpdateLayout()
End Sub

But, there's a problem. Rather than simply adding a new Thing to the database, it's also first adding a duplicate of the parent, but oddly with an empty uniqueidentifier for an Id.

This clutters up the database and throws the following exception after the second click of button ButtonAddThing.

Exception: "Violation of PRIMARY KEY
constraint 'PK_Thing'. Cannot insert
duplicate key in object 'dbo.Thing'.
The statement has been terminated."

These are the T-SQL insert statements generated…

The parent duplication:

exec sp_executesql N'insert [dbo].[Thing]([Id], [ParentId], ...)
values (@0, @1, ...) ',N'@0 uniqueidentifier,@1 uniqueidentifier,...',
@0='00000000-0000-0000-0000-000000000000',
@1='389D987D-79B1-4A9D-970F-CE15F5E3E18A',
...

The new thing:

exec sp_executesql N'insert [dbo].[Thing]([Id], [ParentId], ...)
values (@0, @1, ...) ',N'@0 uniqueidentifier,@1 uniqueidentifier,...',
@0='88641EBB-B7D7-4203-8191-B27E1D1E1840',
@1='391FF0D9-40ED-4349-BB91-0F2E440EF8C9',
...

Why is my Linq to Entities code duplicating these parent rows? How can I properly handle this parent/child relationship?


Update: It's not just a problem when creating new Things. My "delete" button isn't working properly either.

Private Sub ButtonDeleteThing_Click(...)
    db.DeleteObject(DirectCast(TreeViewThings.SelectedItem, Thing))
    db.SaveChanges()
End Sub

nor

Private Sub ButtonDeleteThing_Click(...)
    Dim Id As Guid = DirectCast(TreeViewThings.SelectedItem, Thing).Id
    Dim DoomedThing As Thing = (From t In db.Thing _
                                Where t.Id = Id _
                                Select t).First
    db.DeleteObject(DoomedThing)
    db.SaveChanges()
End Sub

I'm monitoring the SQL Server Profiler while I debug my application. Observed behavior:

  1. The first button click deletes just fine.
  2. The second button click inserts a duplicate-ish parent (empty GUID uniqueidentifier primary key) and then performs the delete.
  3. The third button click fails (Violation of PRIMARY KEY constraint) because it cannot insert a second Thing with an empty GUID primary key.

Best Answer

You should be able to do something like this:

Dim ParentId As Guid = DirectCast(TreeViewThings.SelectedItem, Thing).Id
NewThing.Parent = (From t In db.Thing _
                  Where t.Id = ParentId _
                  Select t).First

That will build the hierarchical model you are going for.