Saturday, January 30, 2010

Entity Framework v4 Object-Graph Deleting Quirks

Update: Danny was quick to respond with a great explanation about how EF4 deals with object-graphs and deletes.

I’ve been getting up to speed on Entity Framework V4 (EF4) lately, for use in a WPF application that I’m working on.   One of the issues I’m coming up against is removal of objects in one-to-many and many-to-many relationships.  It’s still not clear to me why things work the way they do in EF4, but I’ll lay out a few scenarios and talk about what works and what doesn’t work and hope that someone can later comment as to why things may not be working as I would expect.

Scenario 1: A many-to-many relationship where the join table has its own primary key (an identity column in this case):

image

Given the following code, I’d expect to be able to remove an object from the Employee.ProjectAssignments collection and see that object removed from the database when I call SaveChanges on my ObjectContext:

using (var context = new EFTestDBEntities()){
                var employeeGraph = (from e in context.Employees.Include("ProjectAssignments").Include("ProjectAssignments.Project") where e.ID == employeeID select e).FirstOrDefault();
                if (employeeGraph != null){
                    var assignment = employeeGraph.ProjectAssignments.First();
                    employeeGraph.ProjectAssignments.Remove(assignment);

                    context.SaveChanges();
                }
            }

When I hit the “SaveChanges()” call, I get the following exception:

System.InvalidOperationException: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

This strikes me as strange – I assume that I’ve indicated that the employee is no longer related to this project-assignment, so the object is essentially “worthless” and I’d expect it to be removed.

In order to successfully remove this project-assignment, I had to do the following:

using (var context = new EFTestDBEntities()){
                var employeeGraph = (from e in context.Employees.Include("ProjectAssignments").Include("ProjectAssignments.Project") where e.ID == employeeID select e).FirstOrDefault();
                if (employeeGraph != null){
                    ProjectAssignment assignment = employeeGraph.ProjectAssignments.First();
                    context.ProjectAssignments.DeleteObject(assignment);

                    context.SaveChanges();
                    Assert.AreEqual(0, employeeGraph.ProjectAssignments.Count);
                }
            }

Okay, so if I “manually” delete the object by the explicit call to DeleteObject, things work.  So far so good, I can work with this.

Scenario 2: A many-to-many relationship where the join table has a primary key that is composed by foreign-keys to the tables on either side of the relationship:

image 

In this case, the code I’d expect to work, actually worked and allowed me to delete a class-schedule from a teacher:

using (var context = new EFTestDBEntities()){
                var teacherGraph = (from t in context.Teachers.Include("ClassSchedules").Include("ClassSchedules.Class") where t.ID == teacherID select t).FirstOrDefault();
                if(teacherGraph != null){
                    var classSchedule = teacherGraph.ClassSchedules.First();
                    teacherGraph.ClassSchedules.Remove(classSchedule);

                    context.SaveChanges();
                }
            }

Why does EF4 make a distinction in behavior between these two scenarios that are essentially the same operation yet one object-graph allows me to write what I naturally want to write (scenario 2), but not the other?

Scenario 3: A one-to-many relationship:

image

Once again, I tried to write the code in a way I’d expect to naturally work:

using (var context = new EFTestDBEntities()){
                var orderGraph = (from o in context.Orders.Include("OrderDetails") where o.ID == orderID select o).FirstOrDefault();
                if(orderGraph != null){
                    var detail = orderGraph.OrderDetails.First();
                    orderGraph.OrderDetails.Remove(detail);

                    context.SaveChanges();
                }
            }

And once again, I got the following exception:

System.InvalidOperationException: The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable. When a change is made to a relationship, the related foreign-key property is set to a null value. If the foreign-key does not support null values, a new relationship must be defined, the foreign-key property must be assigned another non-null value, or the unrelated object must be deleted.

As with scenario 1, I had to write the following code to successfully remove the OrderDetail record:

using (var context = new EFTestDBEntities()){
                var orderGraph = (from o in context.Orders.Include("OrderDetails") where o.ID == orderID select o).FirstOrDefault();
                if (orderGraph != null){
                    var detail = orderGraph.OrderDetails.First();
                    context.OrderDetails.DeleteObject(detail);

                    context.SaveChanges();
                }
            }

Alright, so I have managed to work around some of the quirks with EF4, but I really don’t understand why removal of objects in the object-graph don’t cause a proper remove/delete in some cases, yet work just as I expect in others. 

9 comments:

  1. My response got too big for a comment. So I put up a blog post: http://blogs.msdn.com/dsimmons/archive/2010/01/31/deleting-foreign-key-relationships-in-ef4.aspx

    - Danny

    ReplyDelete
  2. Thanks Danny - that explanation was exactly what I was looking for. Definitely makes a lot of sense after you spelled out why it works that way.

    ReplyDelete
  3. I have the same problem and it's been a headache:

    -I am going to talk about Scenario 3-

    How can I solve the situation when I have the aggregate root (the orderGraph) been stored in session in a detached mode to add and remove related entities (the order details) following business rules?

    I have not access to the context, because the orderGraph in session in not in a definitive state -I am using this approach because of the nature of the application.

    The steps I follow are:
    1.- Retrieve using a repository
    2.- Detach from the repository -it has an underlying EF context- and store in session
    3.- Send data to the view
    4.- Comunicate client and server using ajax to mantain the aggregate root stored in the session applying the business rules
    5.- Persist the changes as a whole

    Sorry for the long post, the problem It's driven me crazy

    Thanks for any help you can provide me

    ReplyDelete
  4. Perhaps Danny can figure out a way to make TPT Inheritance work with EF as well (v1 or v4, it's broken in both currently).
    http://samscode.com/index.php/2010/01/the-entity-framework-v1-and-v4-deal-breaker-tpt-inheritance/

    ReplyDelete
  5. Thanks for the post. It helped me out today.

    - Mike J.

    ReplyDelete
  6. This comment has been removed by a blog administrator.

    ReplyDelete
  7. I am considering removing all of my foreign key relationships... Running into too many issues..

    ReplyDelete
  8. Looks great, this is something new for me. Thanks for sharing.

    ReplyDelete