Entity Framework - parent child table update in single transation

asp.net-mvc c# entity-framework entity-framework-6

Question

I am working on application where I have a parent child relation between User and Orders tables. Below are the table structures:

User:

Column  | DataType  | Constraint
--------|-----------|-------------
UserId  | int       | Primary key
Username| varchar   |
Email   | varchar   |
IsActive| bit       |

Order:

Column  | DataType  | Constraint
--------|-----------|----------
OrderId | int       | Primary key
UserId  | int       | Foreign key to User.UserId
Type    | smallint  |
Quantity| smallint  |

As per the functionality I have to update User and his orders in one go where orders can be dynamically added, removed or updated.

I am using Entity Framework 6.0 and I cannot find any best solution to update parent table (user) and child table (order) in single transaction.

Could any one explain the best approach to achieve such functionality ?

Below is the code I am using:

Please take a note that I am using AutoMapper to map Object to Entity

public bool ManageUser(UserModel userDetails, string deletedOrders)
{
    var isSuccess = false;
    try
    {
        using (var entity = new UserEntity())
        {
            if (userDetails.UserId == 0)
            {
                entity.Users.Add(Mapper.Map<User>(userDetails));
            }
            else
            {
                var userToEdit = Mapper.Map<User>(userDetails);

                foreach (var item in userToEdit.Orders)
                {
                    if (item.OrderId == 0)
                    {
                        entity.Entry(item).State = System.Data.Entity.EntityState.Added;
                    }
                    else
                    {
                        entity.Entry(item).State = System.Data.Entity.EntityState.Modified;
                    }
                }

                /*** 
                * How to Write a code here to remove orders using deletedOrders parameter
                * Note: deletedOrders contains comma separated Id of the orders which needs to be removed. e.g. "1,5,6" 
                ****/

                entity.Entry(userToEdit).State = System.Data.Entity.EntityState.Modified;
            }

            entity.SaveChanges();
            isSuccess = true;
        }

    }
    catch (Exception ex)
    {

    }

    return isSuccess;
}

Note: This is just a simple code. But what if I will have multiple child tables like Order ? If I would follow the same practice than code will become complex and hard to handle. So looking for best approach for such scenarios.

1
2
11/3/2016 9:15:07 AM

Accepted Answer

It is not necessary to make a trip to database for entities which should be deleted then.

  1. If userDetails.Orders includes orders which should be deleted, you change their states inf your foreach (var item in userToEdit.Orders) cycle to System.Data.Entity.EntityState.Deleted.

  2. If not, you simply create ones with suitable id's, attach them and then change their EntityState to Deleted as well:

    var ordersToDelete = deletedOrders.Split(new[] {','},
      StringSplitOptions.RemoveEmptyEntries).Select(x => new Order 
        { 
            Id = int.Parse(x.Trim())
        })
        .ToList();
    
    ordersToDelete.ForEach(o => 
        entity.Entry(x).State = System.Data.Entity.EntityState.Deleted);
    
1
11/3/2016 9:34:05 AM


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow