ADO.NET Entity Framework generates unexpected, problem INSERTs

Answer 1

I haven't had a detailed look at the code but the first thing to consider is that you shouldn't need to call DeleteObject at every level of your hierarchy. EF like other O/RMs tracks objects and their associations for you.

Let's say, for example, that you have a parent->child 1..* relationship. If you query the parent, remove a child object from the parents Children collection and then call SaveChanges(), EF will generate the appropriate DELETE SQL statements for you - You do not need to track this yourself.

So, a better way to achieve your scenario would be to do this:

1. Query the object hierarchy out of EF.
2. Bind that to your UI. Let your UI modify the in-memory objects as you see fit.
3. When you are done, call SaveChanges and let the EF figure out what to do.

Let me know if that helps.

Answer 2

There are two things going on here. I don't completely understand the relationship between them, but I think I can get you on your way.

The first thing that you need to understand is that the Entity Framework does not deal well with deleting a less-than-fully-materialized instance. That is why Include changes the behavior that you see. So if you have an entity that aggregates a list of children, you need to load those children before calling delete. Only if the child instances are in memory will they be deleted before the parent. So, with or without Include, you need to do something like this before calling Delete.

if (!thing.BrandReference.IsLoaded) thing.BrandReference.Load();

If you've called Include on the relationship, then this will do nothing if you haven't, then it will ensure that everything is materialized before you

The second thing that unique understand is that inserting a new entity with a relationship to an existing entity is conceptually two different inserts. This is a consequence of the fact that relationships are first-class in the Entity Framework. The first insert is the entity itself, the second is the relationship. In this case, there is no separate table for the relationship, so only one actual insert to the database as needed. However, the Entity Framework can only figure this out if it is mapped correctly.

So what is going on in this case? What follows is my speculation based on some of the things I see going on here. But I think the situation is even more complicated than I'm describing, so I believe what follows is incorrect in some of the details. It may be close enough to help you solve the actual problem, though.

1. You had an instance that was less-than-fully-materialized before you tried to delete it.
2. When you tried to delete something else, the framework tried to look into the same relationship. It found things out of whack and tried, unsuccessfully, to put things back into a good state.
3. Then you tried to delete again, 2 repeats, only this time it is even less successful, due to the database constraint.
4. Using Include fixes the issue in 1.

Answer 3

Why are you loading the parent again when adding a new child, when you already have the object? While this is no problem for the database, it will introduce inconsistencies on the object level. You can just use the existing parent like this:

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

About the delete: have you specified cascading deletes in the database?