How to insert/update master-detail in Entity Framework?

Question

I'm trying to make a master-detail Web Form working with Entity Framework and performing insert and update on the same page. I'm new at EF, so I must be making a lot of mistakes here. Can you help me pointing me what's the best practices to perform insert/update on EF? What am I doing wrong here?

In this code, the "New" mode works well, but the "Edit" mode gets this error: "An entity object cannot be referenced by multiple instances of IEntityChangeTracker".

    OrdersEntities ordersEntities = new OrdersEntities();

    private Order myOrder
    {
        get { return (Order)Session["myOrder"]; }
        set { Session["myOrder"] = value; }
    }

    public DataTable dtOrderDetails
    {
        get { return (DataTable)ViewState["dtOrderDetails"]; }
        set { ViewState["dtOrderDetails"] = value; }
    }

    private string Mode
    {
        get { return (string)ViewState["mode"]; }
        set { ViewState["_modo"] = value; }
    }

    private void btnSaveOrder_Click(object sender, EventArgs e)
    {
        if (dtOrderDetails.Rows.Count > 0)
        {
            using (ordersEntities)
            {
                using (var contextTransaction = ordersEntities.Database.BeginTransaction())
                {
                    try
                    {
                        if (Mode == "New")
                        {
                            Order newOrder = new Order();
                            OrderDetails newOrderDetails;

                            int maxOrderNumber = ordersEntities.Order.Select(o => o.OrderNumber).DefaultIfEmpty(0).Max();
                            maxOrderNumber++;

                            newOrder.OrderNumber = maxOrderNumber;
                            newOrder.Date = DateTime.ParseExact(txtOrderDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                            newOrder.CustomerID = Convert.ToInt32(ddlCustomer.SelectedValue);
                            newOrder.Status = 1;

                            ordersEntities.Orders.Add(newOrder);

                            foreach (DataRow dt in dtOrderDetails.Rows)
                            {
                                newOrderDetails = new OrderDetails();
                                newOrderDetails.OrderNumer = maxOrderNumber;
                                newOrderDetails.ProductId = Convert.ToInt32(dt["ProductId"]);
                                newOrderDetails.Quantity = Convert.ToInt32(dt["Quantity"]);

                                ordersEntities.OrderDetails.Add(newOrderDetails);
                            }

                            ordersEntities.SaveChanges();
                            contextTransaction.Commit();

                            myOrder = newOrder;
                        }

                        if (Mode == "Edit")
                        {
                            Order editedOrder = myOrder;
                            OrderDetails editedOrderDetails;

                            editedOrder.Date = DateTime.ParseExact(txtOrderDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);
                            editedOrder.CustomerID = Convert.ToInt32(ddlCustomer.SelectedValue);

                            ordersEntities.Order.Attach(editedOrder);
                            ordersEntities.Entry(editedOrder).State = System.Data.Entity.EntityState.Modified;

                            editedOrder.OrderDetails.Clear();

                            foreach (DataRow dt in dtOrderDetails.Rows)
                            {
                                editedOrderDetails = new OrderDetails();
                                editedOrderDetails.OrderNumer = editedOrder.OrderNumber;
                                editedOrderDetails.ProductId = Convert.ToInt32(dt["ProductId"]);
                                editedOrderDetails.Quantity = Convert.ToInt32(dt["Quantity"]);

                                ordersEntities.OrderDetails.Add(editedOrderDetails);
                            }

                            ordersEntities.SaveChanges();
                            contextTransaction.Commit();
                        }
                    }
                    catch (Exception ex)
                    {
                        contextTransaction.Rollback();
                    }
                }
            }
        }
    }

Answer 1

Here is how you should approach it.

It would be best if you abstract the DbContext away, with this simple interface:

public interface IDataRepository : IDisposable
{
    IDbSet<Order> Orders { get; set; }

    void Save();
}

Of course, your implementation of IDataRepository is based on EntityFramework. Note that you will need to have a dataRepositoryConnection connection string in your web.config file:

public class EfDataRepository : DbContext, IDataRepository
{
    public EfDataRepository() : base("dataRepositoryConnection")
    {
    }

    public IDbSet<Order> Orders { get; set; }

    public void Save()
    {
        this.SaveChanges();
    }
}

In my experience, you also need a 'factory', which gives you a new instance of the data repository. This allows you to be the 'owner' of the instance, and you can safely dispose it. Note that the interaction with the DataContext should be minimal - you do your Unity of Work and get rid of it. Don't reuse! You will see it as an example below.

public class DataRepositoryFactory<T> where T : IDataRepository
{
    private Type dataRepositoryImplementationType;

    public DataRepositoryFactory(T dataRepositoryImplementation)
    {
        if (dataRepositoryImplementation == null)
        {
            throw new ArgumentException("dataRepositoryImplementation");
        }

        this.dataRepositoryImplementationType = dataRepositoryImplementation.GetType();
    }

    public T Create()
    {
        return (T)Activator.CreateInstance(this.dataRepositoryImplementationType);
    }
}

In your controller (if it were MVC app), or Page backend (forms), it would be best if you use Microsoft Unity to get an instance of DataRepositoryFactory. For now, a manual construction would suffice too.

IDataRepository dataRepository = new EfDataRepository();
var dataRepositoryFactory = new DataRepositoryFactory<IDataRepository>(dataRepository);

Also, you don't need all this Transaction/Commit stuff you have put. It should be transparent for you. EF supports it implicitly, you don't have to be explicit about it.

// See, now you are the 'owner' of the dataRepository
using (var dataRepository = this.dataRepositoryFactory.Create())
{
    if (Mode == "New")
    {
        Order newOrder = new Order();

        // This doesn't make sense. Either generate a random order number (e.g. a Guid), or just use the Order.Id as an order number, although I don't recommend it.
        int maxOrderNumber = dataRepository.Orders.Select(o => o.OrderNumber).DefaultIfEmpty(0).Max();
        maxOrderNumber++;

        newOrder.OrderNumber = maxOrderNumber;
        newOrder.Date = DateTime.ParseExact(txtOrderDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);
        newOrder.CustomerID = Convert.ToInt32(ddlCustomer.SelectedValue);
        newOrder.Status = 1;

        dataRepository.Orders.Add(newOrder);

        foreach (DataRow dt in dtOrderDetails.Rows)
        {
            OrderDetails newOrderDetails = new OrderDetails();
            newOrderDetails.OrderNumer = maxOrderNumber;
            newOrderDetails.ProductId = Convert.ToInt32(dt["ProductId"]);
            newOrderDetails.Quantity = Convert.ToInt32(dt["Quantity"]);

            newOrder.OrderDetails.Add(newOrderDetails);
        }

        myOrder = newOrder;
    }

    if (Mode == "Edit")
    {
        Order editedOrder = dataRepository.Orders.FirstOrDefault(o => o.Id == myOrder.Id);

        editedOrder.Date = DateTime.ParseExact(txtOrderDate.Text, "dd/MM/yyyy", CultureInfo.InvariantCulture);
        editedOrder.CustomerID = Convert.ToInt32(ddlCustomer.SelectedValue);
        editedOrder.OrderDetails.Clear();

        foreach (DataRow dt in dtOrderDetails.Rows)
        {
            OrderDetails editedOrderDetails = new OrderDetails();
            editedOrderDetails.OrderNumer = editedOrder.OrderNumber;
            editedOrderDetails.ProductId = Convert.ToInt32(dt["ProductId"]);
            editedOrderDetails.Quantity = Convert.ToInt32(dt["Quantity"]);

            editedOrder.OrderDetails.Add(editedOrderDetails);
        }
    }

    dataRepository.Save();
}

Also, I am pretty sure you have setup the relation between Order and OrderDetails classes incorrectly, in your EF code-first approach.

This is just wrong:

OrderDetails newOrderDetails = new OrderDetails();
newOrderDetails.OrderNumer = maxOrderNumber;

If you post them here, I can fix them for you.