Entity Framework 4.3 : How to insert, update, delete master-detail entity

I have already talk about complex object in a previous post concerning Entity Framework and complex entity with MVC and Entity Framework. This time, I’ll explain you how to insert, update and delete detail of a master-detail scenario.

First of all, you need to update the master properties. This is done by getting the entity from the database and to update all its scalar information from the controller (if you are using Asp.Net MVC).

[HttpPost]
public ActionResult Update(MyModel formModel) 
{
    var fromDatabase = Database.MyModel.Single(formModel.ID);
    //Scalar
    Database.Entry(fromDatabase).CurrentValues.SetValues(formModel);
    Database.Entry(fromDatabase).State = EntityState.Modified;

The next step is to update relationship that are easier which are the 1 to 1 relation or the 0 to 1 relation. These one would have in the MyModel an object that reference another object. To update the reference it just need to be set to the object and to be sure that the Database context know about it. From the primary key of the object, everything will be binded correctly.

    // Relationship -> 1 to 1
    if (formModel.OneOneObject != null) 
    {
        Database.OneOneObjects.Attach(formModel.OneOneObject);
    }
    fromDatabase.OneOneObject = formModel.OneOneObject;

The last step is to handle 0 to many relationship. This need to work in multiple case like adding a new detail, removing a detail or editing a scalar property of this entity.

The first thing to do is if the master doesn’t have any children to remove any trace of them. To do you need to null the reference and if you do not allow NULL in the database, kill all children reference to the master.

// Relationship -> MyModelDetail
if (formModel.MyModelDetails != null && formModel.MyModelDetails.Any()) {
//Here Update, individual delete, and insertion
}
else
{
    fromDatabase.MyModelDetails = null;	
    var MyModelDetailsToRemove = Database.MyModelDetails.Where(x=>x.MyModel.ID==formModel.ID);
    foreach (var MyModelDetailToRemove in MyModelDetailsToRemove) 
    {
        Database.MyModelDetails.Remove(MyModelDetailToRemove);
    }
}

As you can see, we set to NULL the reference to any detail and we remove from all children any reference to the master.

Now, we need to replace the line 3 of the previous code which is the update, insert, delete of the master-detail relationship.

foreach (var MyModelDetail in formModel.MyModelDetails) 
{
    MyModelDetail.MyModel = fromDatabase;     
    if (MyModelDetail.ID == 0) 
    {
	Database.MyModelDetails.Add(MyModelDetail);
    }
    else 
    {
        if (!Database.Set<MyModelDetail>().Local.Any(e => e.ID == MyModelDetail.ID))
        {
            Database.MyModelDetails.Attach(MyModelDetail);
        } 
        var fromDatabaseMyModelDetail = Database.MyModelDetails.Single(x => x.ID == MyModelDetail.ID);
        Database.Entry(fromDatabaseMyModelDetail).CurrentValues.SetValues(MyModelDetail);
        Database.Entry(fromDatabaseMyModelDetail).State = EntityState.Modified;                   
    }
}

This code loop all children and assign the master reference. Then, it check is the unique identifier has been set. This will indicate if we insert or we need to update. The first case insert the object at the database context, the second case will attach the object if this one is not attached yet and will set the new values. We could have for both case update non scalar property for all children. This can be done by simply processing the same way we just did for the master but at the child level.

Last thing we need to do, and not to forget, is to remove children that is not any more required to be attached to the master. This can be done with linq to get all children that has not been submitted by the user.

    if (fromDatabase.MyModelDetails != null)
    {
        foreach (var MyModelDetailToRemove in fromDatabase.MyModelDetails.Where(x => !formModel.MyModelDetails.Any(u=>u.ID==x.ID)).ToList()) 
	{
	    Database.MyModelDetails.Remove(MyModelDetailToRemove);
	}
    }

This code simply create a list of children (MyModelDetail) that is not any more present in the list submitted of the user. It checks with the database. The use of ToList() is required because we remove children which are in the list that we are looping.

If you like my article, think to buy my annual book, professionally edited by a proofreader. directly from me or on Amazon. I also wrote a TypeScript book called Holistic TypeScript

10 thoughts on “Entity Framework 4.3 : How to insert, update, delete master-detail entity

  1. I’m using DTO for transferring data between layers.
    Basically the approach is same with yours, but I do add edit and delete in its navigation and set its state to modified then save.

    I’ve got error “The operation failed: The relationship could not be changed because one or more of the foreign-key properties is non-nullable”. Then I changed the code based on this article and it’s works

    Can we use its navigation property rather than directly to the context?

    • Hi @willy,

      I am happy to hear that it works since you changed your code from this article. Concerning the navigation property question, I am not sure to follow you. What do you mean by using the navigation property rather than the context?

  2. Hi patrick, thanks for your answer and sorry for late reply

    When you define an entity for example Purchase and PurchaseDetails (one-to-many).

    Purchase
    Properties:
    – PurchaseID
    – InvoiceNo
    Navigation Properties:
    – PurchaseDetails <– ICollection

    That’s what I mean by navigation properties.

    Back to my first question, I do add, edit, and delete through its navigation properties and got that error when save the deleted object. I delete the object using this code:

    purchase.PurchaseDetails.Remove(purchaseDetail); <– from Nav Prop

    and your example using Database.MyModelDetails.Remove, if I translate to my code,

    context.Set().Remove(purchaseDetail)

    I’m not getting an error using your approach. So, could we use Purchase’s navigation properties to insert, update, delete master detail entity? Basically I want do add edit delete in the Collection and then Save but never get success until I find this article, and I can only say thanks for the sharing.

    • Great that it works for you. You cannot just add/delete from the collection and have Entity Framework knows to change the state to Added or Removed with a “disconnected” object. By “disconnected” I mean that the Entity may come back from an Http Request. If you get back the Entity from the database, through Entity Framework, this one may track changes for properties if you have enabled to automatically track changes but it will never track collection changes. The best way is to use like you did in this article and the loop through the collection and figure out which one should be added or removed.

  3. Hi Patrick,
    I have a problem when delete the detail

    foreach (PurchaseOrderDetailViewModel podVM in purchaseOrderVM.PurchaseOrderDetails)
    {
    PurchaseOrderDetail purchaseOrderDetail = purchaseOrder.PurchaseOrderDetails.SingleOrDefault(
    pod => pod.PurchaseOrderDetailID == podVM.PurchaseOrderDetailID);

    // New
    if (purchaseOrderDetail == null)
    {
    purchaseOrder.PurchaseOrderDetails.Add(new PurchaseOrderDetail()
    {
    PurchaseOrderID = podVM.PurchaseOrderID,
    ItemID = podVM.ItemID,
    Quantity = podVM.Quantity,
    BuyPrice = podVM.BuyPrice
    });
    //
    }
    // Edit
    else
    {
    purchaseOrderDetail.Quantity = podVM.Quantity – purchaseOrderDetail.Quantity;
    purchaseOrderDetail.BuyPrice = podVM.BuyPrice;
    // set EntityState.Modified will be done in BLL
    }
    }

    // Delete
    foreach (PurchaseOrderDetail pod in purchaseOrder.PurchaseOrderDetails.Where(x => !purchaseOrderVM.PurchaseOrderDetails.Any(y => y.PurchaseOrderDetailID == x.PurchaseOrderDetailID)).ToList())
    {
    ??? -> I cannot delete at this step because it will be done in BLL
    }

    purchaseOrderManager.Update(purchaseOrder);

  4. in BLL I loop the detail to add or update the detail
    foreach (PurchaseOrderDetail purchaseOrderDetail in purchaseOrder.PurchaseOrderDetails)
    {
    // Update Stock

    // New
    if (purchaseOrderDetail.PurchaseOrderDetailID == 0)
    {
    unitOfWork.PurchaseOrderDetailRepository.Insert(purchaseOrderDetail);
    }
    // Edit
    else
    {
    unitOfWork.PurchaseOrderDetailRepository.Update(purchaseOrderDetail);
    }
    }

    I loop once again to remove detail that is not any more required:
    foreach (PurchaseOrderDetail purchaseOrderDetail in purchaseOrder.PurchaseOrderDetails)
    {
    // Update Stock

    unitOfWork.PurchaseOrderDetailRepository.Delete(purchaseOrderDetail);
    }

    The problem is what should I do in Controller, so that in BLL I know the detail is deleted? If I delete at Controller, I cannot update the stock in BLL.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.