Entity framework, referential integrity constraint violation occurred error on updating entity from disconnected area

Question

I have below model

public class Order
{
    [Key]
    public virtual string OrderNo {get;set;}
    public virtual IList<OrderItem> Items {get;set;}
}

public class OrderItem
{
    [Key]
    public virtual string ItemNo {get; set;}
    public virtual string ParentItemNo {get;set;}
    public virtual string OrderNo {get;set;}

    public virtual OrderItem ParentItem {get;set;}
    public virtual IList<OrderItem> ChildItems {get;set;}
    public virtual IList<ItemProperty> ItemProperties {get;set;}
    public virtual Order Order {get;set;}
}

public class ItemProperty
{
    [Key]
    public virtual string PropertyNo {get; set;}
    public virtual string ParentPropertyNo {get;set;}
    public virtual string OrderItemNo {get;set;}

    public virtual ItemProperty ParentProperty {get;set;}
    public virtual IList<ItemProperty> ChildProperties {get;set;}
    public virtual OrderItem OrderItem {get;set;}
}

I'm running on disconnected area (with service our disconnected from the Entity Framework context)

1. I create a order and save to database

client:

service.CreateOrder(new Order() { OrderN="fksjdf1" });

server:

using(EfDbContext context = new EfDbContext())
{
    context.Orders.Add(order);
    context.SaveChanges();
}

1. I need to add one or more OrderItems to previously added order

client:

var order = service.GetOrder("fksjdf1");

var item1 = new OrderItem();
item1.ItemNo="i1";
item1.Order=order;
item1.OrderNo=order.OrderNo;
item1.ItemProperties.Add(new ItemProperty()
    PropertyNo="p1",
    OrderItem = item1
})
order.Items.Add(item1);

var item2 = new OrderItem();
item2.ItemNo="i2";
item2.Order=order;
item2.OrderNo=order.OrderNo;
item2.ItemProperties.Add(new ItemProperty()
    PropertyNo="p2",
    OrderItem = item2
});
item2.ItemProperties.Add(new ItemProperty()
    PropertyNo="p3",
    OrderItem = item2
})
order.Items.Add(item2);
service.UpdateOrder(order);

server:

using(EfDbContext context = new EfDbContext())
{
    DbEntityEntry dbEntityEntry = context.Entry(order);
    if (dbEntityEntry.State == EntityState.Detached)
    {
        // ERROR
        context.Set<Order>().Attach(order);
    }

    dbEntityEntry.State = EntityState.Modified;
    context.SaveChanges();
}

Error: A referential integrity constraint violation occurred: The property values that define the referential constraints are not consistent between principal and dependent objects in the relationship.

Why I see this error? Why I not update this entity?

How can i use entity framework from disconnected area?

EDIT 1:

public Order GetOrder(string orderNo)
{
using (EfDbContext context = new EfDbContext())
            {
                context.Configuration.ProxyCreationEnabled = false;
                var order = context.Orders
                    .Include(o => o.OrderItems
                                      .Select(z => z.ItemProperties
                                                       .Select(y => y.ChildProperties)))
                                                       .Where(o => o.OrderNo == orderNo)
                    .FirstOrDefault();
            }
}

Answer 1

You probably have this error because you don't set OrderItemNo in ItemProperty, like so:

var item1 = new OrderItem();
item1.ItemNo="i1";
item1.Order=order;
item1.OrderNo=order.OrderNo;
item1.ItemProperties.Add(new ItemProperty {
    PropertyNo="p1",
    OrderItem = item1,
    OrderItemNo = item1.ItemNo // = "i1"
});
order.Items.Add(item1);

// and the same for item2

When the order gets attached to the context, related order items and item properties get attached as well. The navigation property OrderItem of ItemProperty refers to an entity with a key you supplied ("i1"), but the foreign key OrderItemNo doesn't have this key value. That's the inconsistency the exception is complaining about.

Edit

Unfortunately it is not that easy to update a detached object graph in the database. Setting the state of an entity to Modified only marks this entity as Modified and no related entity.

Your GetOrder method makes things complicated because you are eagerly loading already existing items and more related stuff to the client, then add new OrderItems on client side and send this new modified object graph back to the server. On server side you now have the problem that you must handle the already existing items differently (don't insert them into the database) than the new items (insert them into the database). To do this, you need to detect which items are old and which are new. If you don't transport some flag in the entities themselves that would indicate if the entity is new or not, you must query the database again and compare the object graph loaded from the DB with the detached object graph sent from the client. A general sketch how it can be done is here: https://stackoverflow.com/a/5540956/270591 (That's only for a parent with child collection. If grandchild collections are involved - like in your model - it is getting more complex.)

In my opinion you can simplify the whole procedure if you would not only have this global service.UpdateOrder(order) method that tries to handle all possible changes in the object graph but also some specialized methods that leverage the knowledge you have about the change of the graph - in this case a specialized method for adding new OrderItems to an existing order. It could look like this:

var order = service.GetOrder("fksjdf1");

var newOrderItems = new List<OrderItem>();

var item1 = new OrderItem();
item1.ItemNo="i1";
item1.OrderNo=order.OrderNo; // leave the Order property null
item1.ItemProperties.Add(new ItemProperty { PropertyNo="p1" });
newOrderItems.Add(item1);

// the same for item2
newOrderItems.Add(item2);

service.AddNewOrderItems(newOrderItems);

And the service method would be:

public void AddNewOrderItems(List<OrderItem> newOrderItems)
{
    using(EfDbContext context = new EfDbContext())
    {
        foreach (var newOrderItem in newOrderItems)
            context.Set<OrderItem>().Add(newOrderItem);

        context.SaveChanges();
    }
}