c# - How to save changes to related table in MVC 5 and EF 6 - Entity Framework Master-Detail

Question

I have two tables, Promotion and PromotionLine, with a foreign key defined as PromotionLine.PromoID = Promotion.ID

PromotionLines are associated with the Promotion model with the following in the Promotion class:

public IList<PromotionLine> PromotionLineItems { get; set; }

I chose not to use virtual because I don't want the promo lines loaded if we're just using a summary view to show high level Promotion info (such as a list of Promotions).

When promotion details are needed, I get the promotion lines:

    public static Promotion GetInstance(int? promotionId)
    {
        if (promotionId == null) return null;
        using (APP01Entities entities = new APP01Entities())
        {
            return entities.Promotions
                .Include(s => s.PromotionState)
                .Include(h => h.PromotionHeaderType)
                .Include(l => l.PromotionLineItems)
                .Include(c => c.PromotionComments)
                .FirstOrDefault(p => p.ID == promotionId);
        }

    }

This works, and I can access the promotion lines in my view.

However, when I go to update changes, I encounter the error:

“A referential integrity constraint violation occurred: The property value(s) of 'Promotion.ID' on one end of a relationship do not match the property value(s) of 'PromotionLine.PromotionID' on the other end.”

I understand WHY this error is occurring. I don't know how to get around it. I'm using the default update method (created by EF scaffolding):

public bool Update()
    {
        try
        {
            using (APP01Entities entities = new APP01Entities())
            {
                entities.Promotions.Attach(this);

                var entity = entities.ChangeTracker.Entries<Promotion>().FirstOrDefault(e => e.Entity == this);

                if (entity == null)
                {
                    return false;
                }
                entity.State = EntityState.Modified;
                entities.SaveChanges();
            }
            return true;
        }
        catch (System.Data.Entity.Validation.DbEntityValidationException e)
        {
            throw e.Improve();
        }
    }

The problem is with:

entities.Promotions.Attach(this);

"this" has the promotion lines. entities.Promotions does not.

Here is how I'm calling the update method:

    [HttpPost]
    public ActionResult Edit(Promotion promotion)
    {
        if (ModelState.IsValid)
        {
            promotion.Update();
        }
        return View(promotion);
    }

Questions

• How do I get the promotion lines added to entities.Promotions?
• or, should I be approaching this update differently?

Answer 1

This is not a trivial task. You need to update your object graph. In other word you need a Master-Detail update.

To keep the answer simple I suppose I have an Order entity that have OrderDetails property that is a List<OrderDetail>.You should pay attention when editing:

• There are some OrderDetail that have been added to OrderDetails and the main sign of them is having Id property equal to 0.
• Some OrderDetail have been removed and no longer exist in OrderDetails
• Some OrderDetail have been changed.

When updating the Order you should update the Order itself and also apply above changes.

Steps

Here are steps:

1. Get original order from database.
2. Update the value of original order using edited order.
3. Find list of added items (Id of added items is 0).
4. Find list of removed items (list of order details of original order where id of original order details is not between ids of order details of edited order).
5. Find list of edited items (list of order details of original order where id of original order details is between ids of order details of edited order).
6. Use a loop over deleted items list and set state of them to removed.
7. Use a loop over edited items list and update value of original order details that have been loaded in context.
8. Use a loop over added items list and set state of them to added.
9. Set the state of original order to modified.
10. Save context changes.

Code

Here is the code:

public void Update(Order editedOrder)
{
    using(var context = new YourDbContext())
    {   
        //Get original order from database.
        var originalOrder = context.Orders.Including("OrderDetails")
            .Where(x => x.OrderId == editedOrder.OrderId).FirstOrDefault();

        //Update the value of original order using edited order.
        context.Entry(originalOrder).CurrentValues.SetValues(editedOrder);

        //Find list of added items (Id of added items is 0).
        var addedList = editedOrder.OrderDetails
            .Where(y => y.OrderDetailId == 0).ToList();

        //Find list of removed items.
        var deletedList = originalOrder.OrderDetails
            .Where
            (
                x =>!editedOrder.OrderDetails.Select(y => y.OrderDetailId)
                        .Contains(x.OrderDetailId)
            )
            .ToList();

        //Find list of edited items.
        var editedList = editedOrder.OrderDetails
            .Where
            (
                y => originalOrder.OrderDetails.Select(z => z.OrderDetailId)
                         .Contains(y.OrderDetailId)
            )
            .ToList();

        //Use a loop over deleted items list and set state of them to removed.
        deletedList.ForEach(deletedDetail =>
        {
            originalOrder.OrderDetails.Remove(deletedDetail);
            context.Entry(editedOrder).State = EntityState.Deleted;
        });

        //Use a loop over edited items list 
        //and update value of original order details  that have been loaded in context.
        editedList.ForEach(editedDetail =>
        {
            var originalOrderDetail = originalOrder.OrderDetails
                .Where(x => x.OrderDetailId == editedDetail.OrderDetailId)
                .FirstOrDefault();
           context.Entry(originalOrderDetail).CurrentValues.SetValues(editedDetail);
        });

        //Use a loop over added items list and set state of them to added.
        addedList.ForEach(addedDetail =>
        {
            originalOrder.OrderDetails.Add(addedDetail);
        });

        //Set the state of original order to modified.
        context.Entry(oroginalOrder).State = System.Data.Entity.EntityState.Modified;

        //Save context changes.
        context.SaveChanges();
    }
}