How to add/update child entities when updating a parent entity in EF

Question

The two entities are one-to-many relationship (built by code first fluent api).

public class Parent
{
    public Parent()
    {
        this.Children = new List<Child>();
    }

    public int Id { get; set; }

    public virtual ICollection<Child> Children { get; set; }
}

public class Child
{
    public int Id { get; set; }

    public int ParentId { get; set; }

    public string Data { get; set; }
}

In my WebApi controller I have actions to create a parent entity(which is working fine) and update a parent entity(which has some problem). The update action looks like:

public void Update(UpdateParentModel model)
{
    //what should be done here?
}

Currently I have two ideas:

1. Get a tracked parent entity named existing by model.Id, and assign values in model one by one to the entity. This sounds stupid. And in model.Children I don't know which child is new, which child is modified(or even deleted).

2. Create a new parent entity via model, and attached it to the DbContext and save it. But how can the DbContext know the state of children (new add/delete/modified)?

What's the correct way of implement this feature?

Answer 1

Consider to use https://github.com/WahidBitar/EF-Core-Simple-Graph-Update. It works well for me.

The library is simple, practically has only one extension method

T InsertUpdateOrDeleteGraph<T>(this DbContext context,
 T newEntity, T existingEntity)

https://github.com/WahidBitar/EF-Core-Simple-Graph-Update/blob/master/src/Diwink.Extensions.EntityFrameworkCore/DbContextExtensions.cs#L34

Compare to most of the answers to this question, it is generic (doesn’t use hardcoded table names, can be used for different models), and includes unit tests for different model changes.
The author promptly responses to reported issues.

Answer 2

So, I finally managed to get it working, although not fully automatically.
Notice the AutoMapper <3. It handles all the mapping of properties so you don't have to do it manually. Also, if used in a way where it maps from one object to another, then it only updates the properties and that marks changed properties as Modified to EF, which is what we want.
If you would use explicit context.Update(entity), the difference would be that entire object would be marked as Modified and EVERY prop would be updated.
In that case you don't need tracking but the drawbacks are as mentioned.
Maybe that's not a problem for you but it's more expensive and I want to log exact changes inside Save so I need correct info.

            // We always want tracking for auto-updates
            var entityToUpdate = unitOfWork.GetGenericRepository<Article, int>()
                .GetAllActive() // Uses EF tracking
                .Include(e => e.Barcodes.Where(e => e.Status == DatabaseEntityStatus.Active))
                .First(e => e.Id == request.Id);

            mapper.Map(request, entityToUpdate); // Maps it to entity with AutoMapper <3
            ModifyBarcodes(entityToUpdate, request);

            // Removed part of the code for space

            unitOfWork.Save();

ModifyBarcodes part here.
We want to modify our collection in a way that EF tracking won't end up messed up.
AutoMapper mapping would, unforunately, create a completely new instance of collection, there fore messing up the tracking, although, I was pretty sure it should work. Anyways, since I'm sending complete list from FE, here we actually determine what should be Added/Updated/Deleted and just handle the list itself.
Since EF tracking is ON, EF handles it like a charm.

            var toUpdate = article.Barcodes
                .Where(e => articleDto.Barcodes.Select(b => b.Id).Contains(e.Id))
                .ToList();

            toUpdate.ForEach(e =>
            {
                var newValue = articleDto.Barcodes.FirstOrDefault(f => f.Id == e.Id);
                mapper.Map(newValue, e);
            });

            var toAdd = articleDto.Barcodes
                .Where(e => !article.Barcodes.Select(b => b.Id).Contains(e.Id))
                .Select(e => mapper.Map<Barcode>(e))
                .ToList();

            article.Barcodes.AddRange(toAdd);

            article.Barcodes
                .Where(e => !articleDto.Barcodes.Select(b => b.Id).Contains(e.Id))
                .ToList()
                .ForEach(e => article.Barcodes.Remove(e));


CreateMap<ArticleDto, Article>()
            .ForMember(e => e.DateCreated, opt => opt.Ignore())
            .ForMember(e => e.DateModified, opt => opt.Ignore())
            .ForMember(e => e.CreatedById, opt => opt.Ignore())
            .ForMember(e => e.LastModifiedById, opt => opt.Ignore())
            .ForMember(e => e.Status, opt => opt.Ignore())
            // When mapping collections, the reference itself is destroyed
            // hence f* up EF tracking and makes it think all previous is deleted
            // Better to leave it on manual and handle collecion manually
            .ForMember(e => e.Barcodes, opt => opt.Ignore())
            .ReverseMap()
            .ForMember(e => e.Barcodes, opt => opt.MapFrom(src => src.Barcodes.Where(e => e.Status == DatabaseEntityStatus.Active)));

Answer 3

This ought to do it...

private void Reconcile<T>(DbContext context,
    IReadOnlyCollection<T> oldItems,
    IReadOnlyCollection<T> newItems,
    Func<T, T, bool> compare)
{
    var itemsToAdd = new List<T>();
    var itemsToRemove = new List<T>();

    foreach (T newItem in newItems)
    {
        T oldItem = oldItems.FirstOrDefault(arg1 => compare(arg1, newItem));

        if (oldItem == null)
        {
            itemsToAdd.Add(newItem);
        }
        else
        {
            context.Entry(oldItem).CurrentValues.SetValues(newItem);
        }
    }

    foreach (T oldItem in oldItems)
    {
        if (!newItems.Any(arg1 => compare(arg1, oldItem)))
        {
            itemsToRemove.Add(oldItem);
        }
    }

    foreach (T item in itemsToAdd)
        context.Add(item);

    foreach (T item in itemsToRemove)
        context.Remove(item);
}

Answer 4

Refer below code snippet from one of my projects where I implemented the same thing. It will make save data if new entry, updates if existing and delete if record is not available in the posting json. Json Data to help you understand the schema:

{
    "groupId": 1,
    "groupName": "Group 1",
    "sortOrder": 1,
    "filterNames": [
        {
            "filterId": 1,
            "filterName1": "Name11111",
            "sortOrder": 10,
            "groupId": 1           
        }  ,
        {
            "filterId": 1006,
            "filterName1": "Name Changed 1",
            "sortOrder": 10,
            "groupId": 1           
        }  ,
        {
            "filterId": 1007,
            "filterName1": "New Filter 1",
            "sortOrder": 10,
            "groupId": 1           
        } ,
        {
            "filterId": 2,
            "filterName1": "Name 2 Changed",
            "sortOrder": 10,
            "groupId": 1           
        }                 
    ]
}


public async Task<int> UpdateFilter(FilterGroup filterGroup)
        {                        
            var Ids = from f in filterGroup.FilterNames select f.FilterId;
            var toBeDeleted = dbContext.FilterNames.Where(x => x.GroupId == filterGroup.GroupId
            && !Ids.Contains(x.FilterId)).ToList();
            foreach(var item in toBeDeleted)
            {
                dbContext.FilterNames.Remove(item);
            }
            await dbContext.SaveChangesAsync();

            dbContext.FilterGroups.Attach(filterGroup);
            dbContext.Entry(filterGroup).State = EntityState.Modified;
            for(int i=0;i<filterGroup.FilterNames.Count();i++)            
            {
                if (filterGroup.FilterNames.ElementAt(i).FilterId != 0)
                {
                    dbContext.Entry(filterGroup.FilterNames.ElementAt(i)).State = EntityState.Modified;
                }
            }            
            return await dbContext.SaveChangesAsync();
        }

Answer 5

Because the model that gets posted to the WebApi controller is detached from any entity-framework (EF) context, the only option is to load the object graph (parent including its children) from the database and compare which children have been added, deleted or updated. (Unless you would track the changes with your own tracking mechanism during the detached state (in the browser or wherever) which in my opinion is more complex than the following.) It could look like this:

public void Update(UpdateParentModel model)
{
    var existingParent = _dbContext.Parents
        .Where(p => p.Id == model.Id)
        .Include(p => p.Children)
        .SingleOrDefault();

    if (existingParent != null)
    {
        // Update parent
        _dbContext.Entry(existingParent).CurrentValues.SetValues(model);

        // Delete children
        foreach (var existingChild in existingParent.Children.ToList())
        {
            if (!model.Children.Any(c => c.Id == existingChild.Id))
                _dbContext.Children.Remove(existingChild);
        }

        // Update and Insert children
        foreach (var childModel in model.Children)
        {
            var existingChild = existingParent.Children
                .Where(c => c.Id == childModel.Id && c.Id != default(int))
                .SingleOrDefault();

            if (existingChild != null)
                // Update child
                _dbContext.Entry(existingChild).CurrentValues.SetValues(childModel);
            else
            {
                // Insert child
                var newChild = new Child
                {
                    Data = childModel.Data,
                    //...
                };
                existingParent.Children.Add(newChild);
            }
        }

        _dbContext.SaveChanges();
    }
}

...CurrentValues.SetValues can take any object and maps property values to the attached entity based on the property name. If the property names in your model are different from the names in the entity you can't use this method and must assign the values one by one.

Answer 6

Because I hate repeating complex logic, here's a generic version of Slauma's solution.

Here's my update method. Note that in a detached scenario, sometimes your code will read data and then update it, so it's not always detached.

public async Task UpdateAsync(TempOrder order)
{
    order.CheckNotNull(nameof(order));
    order.OrderId.CheckNotNull(nameof(order.OrderId));

    order.DateModified = _dateService.UtcNow;

    if (_context.Entry(order).State == EntityState.Modified)
    {
        await _context.SaveChangesAsync().ConfigureAwait(false);
    }
    else // Detached.
    {
        var existing = await SelectAsync(order.OrderId!.Value).ConfigureAwait(false);
        if (existing != null)
        {
            order.DateModified = _dateService.UtcNow;
            _context.TrackChildChanges(order.Products, existing.Products, (a, b) => a.OrderProductId == b.OrderProductId);
            await _context.SaveChangesAsync(order, existing).ConfigureAwait(false);
        }
    }
}

CheckNotNull is defined here.

Create these extension methods.

/// <summary>
/// Tracks changes on childs models by comparing with latest database state.
/// </summary>
/// <typeparam name="T">The type of model to track.</typeparam>
/// <param name="context">The database context tracking changes.</param>
/// <param name="childs">The childs to update, detached from the context.</param>
/// <param name="existingChilds">The latest existing data, attached to the context.</param>
/// <param name="match">A function to match models by their primary key(s).</param>
public static void TrackChildChanges<T>(this DbContext context, IList<T> childs, IList<T> existingChilds, Func<T, T, bool> match)
    where T : class
{
    context.CheckNotNull(nameof(context));
    childs.CheckNotNull(nameof(childs));
    existingChilds.CheckNotNull(nameof(existingChilds));

    // Delete childs.
    foreach (var existing in existingChilds.ToList())
    {
        if (!childs.Any(c => match(c, existing)))
        {
            existingChilds.Remove(existing);
        }
    }

    // Update and Insert childs.
    var existingChildsCopy = existingChilds.ToList();
    foreach (var item in childs.ToList())
    {
        var existing = existingChildsCopy
            .Where(c => match(c, item))
            .SingleOrDefault();

        if (existing != null)
        {
            // Update child.
            context.Entry(existing).CurrentValues.SetValues(item);
        }
        else
        {
            // Insert child.
            existingChilds.Add(item);
            // context.Entry(item).State = EntityState.Added;
        }
    }
}

/// <summary>
/// Saves changes to a detached model by comparing it with the latest data.
/// </summary>
/// <typeparam name="T">The type of model to save.</typeparam>
/// <param name="context">The database context tracking changes.</param>
/// <param name="model">The model object to save.</param>
/// <param name="existing">The latest model data.</param>
public static void SaveChanges<T>(this DbContext context, T model, T existing)
    where T : class
{
    context.CheckNotNull(nameof(context));
    model.CheckNotNull(nameof(context));

    context.Entry(existing).CurrentValues.SetValues(model);
    context.SaveChanges();
}

/// <summary>
/// Saves changes to a detached model by comparing it with the latest data.
/// </summary>
/// <typeparam name="T">The type of model to save.</typeparam>
/// <param name="context">The database context tracking changes.</param>
/// <param name="model">The model object to save.</param>
/// <param name="existing">The latest model data.</param>
/// <param name="cancellationToken">A cancellation token to cancel the operation.</param>
/// <returns></returns>
public static async Task SaveChangesAsync<T>(this DbContext context, T model, T existing, CancellationToken cancellationToken = default)
    where T : class
{
    context.CheckNotNull(nameof(context));
    model.CheckNotNull(nameof(context));

    context.Entry(existing).CurrentValues.SetValues(model);
    await context.SaveChangesAsync(cancellationToken).ConfigureAwait(false);
}

Answer 7

Here is my code that works just fine.

public async Task<bool> UpdateDeviceShutdownAsync(Guid id, DateTime shutdownAtTime, int areaID, decimal mileage,
        decimal motohours, int driverID, List<int> commission,
        string shutdownPlaceDescr, int deviceShutdownTypeID, string deviceShutdownDesc,
        bool isTransportation, string violationConditions, DateTime shutdownStartTime,
        DateTime shutdownEndTime, string notes, List<Guid> faultIDs )
        {
            try
            {
                using (var db = new GJobEntities())
                {
                    var isExisting = await db.DeviceShutdowns.FirstOrDefaultAsync(x => x.ID == id);

                    if (isExisting != null)
                    {
                        isExisting.AreaID = areaID;
                        isExisting.DriverID = driverID;
                        isExisting.IsTransportation = isTransportation;
                        isExisting.Mileage = mileage;
                        isExisting.Motohours = motohours;
                        isExisting.Notes = notes;                    
                        isExisting.DeviceShutdownDesc = deviceShutdownDesc;
                        isExisting.DeviceShutdownTypeID = deviceShutdownTypeID;
                        isExisting.ShutdownAtTime = shutdownAtTime;
                        isExisting.ShutdownEndTime = shutdownEndTime;
                        isExisting.ShutdownStartTime = shutdownStartTime;
                        isExisting.ShutdownPlaceDescr = shutdownPlaceDescr;
                        isExisting.ViolationConditions = violationConditions;

                        // Delete children
                        foreach (var existingChild in isExisting.DeviceShutdownFaults.ToList())
                        {
                            db.DeviceShutdownFaults.Remove(existingChild);
                        }

                        if (faultIDs != null && faultIDs.Any())
                        {
                            foreach (var faultItem in faultIDs)
                            {
                                var newChild = new DeviceShutdownFault
                                {
                                    ID = Guid.NewGuid(),
                                    DDFaultID = faultItem,
                                    DeviceShutdownID = isExisting.ID,
                                };

                                isExisting.DeviceShutdownFaults.Add(newChild);
                            }
                        }

                        // Delete all children
                        foreach (var existingChild in isExisting.DeviceShutdownComissions.ToList())
                        {
                            db.DeviceShutdownComissions.Remove(existingChild);
                        }

                        // Add all new children
                        if (commission != null && commission.Any())
                        {
                            foreach (var cItem in commission)
                            {
                                var newChild = new DeviceShutdownComission
                                {
                                    ID = Guid.NewGuid(),
                                    PersonalID = cItem,
                                    DeviceShutdownID = isExisting.ID,
                                };

                                isExisting.DeviceShutdownComissions.Add(newChild);
                            }
                        }

                        await db.SaveChangesAsync();

                        return true;
                    }
                }
            }
            catch (Exception ex)
            {
                logger.Error(ex);
            }

            return false;
        }

Answer 8

For VB.NET developers Use this generic sub to mark the child state, easy to use

Notes:

• PromatCon: the entity object
• amList: is the child list that you want to add or modify
• rList: is the child list that you want to remove

updatechild(objCas.ECC_Decision, PromatCon.ECC_Decision.Where(Function(c) c.rid = objCas.rid And Not objCas.ECC_Decision.Select(Function(x) x.dcid).Contains(c.dcid)).toList)

Sub updatechild(Of Ety)(amList As ICollection(Of Ety), rList As ICollection(Of Ety))
        If amList IsNot Nothing Then
            For Each obj In amList
                Dim x = PromatCon.Entry(obj).GetDatabaseValues()
                If x Is Nothing Then
                    PromatCon.Entry(obj).State = EntityState.Added
                Else
                    PromatCon.Entry(obj).State = EntityState.Modified
                End If
            Next
        End If

        If rList IsNot Nothing Then
            For Each obj In rList.ToList
                PromatCon.Entry(obj).State = EntityState.Deleted
            Next
        End If
End Sub

PromatCon.SaveChanges()

Answer 9

public async Task<IHttpActionResult> PutParent(int id, Parent parent)
        {
            if (!ModelState.IsValid)
            {
                return BadRequest(ModelState);
            }

            if (id != parent.Id)
            {
                return BadRequest();
            }

            db.Entry(parent).State = EntityState.Modified;

            foreach (Child child in parent.Children)
            {
                db.Entry(child).State = child.Id == 0 ? EntityState.Added : EntityState.Modified;
            }

            try
            {
                await db.SaveChangesAsync();
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!ParentExists(id))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }

            return Ok(db.Parents.Find(id));
        }

This is how I solved this problem. This way, EF knows which to add which to update.

Answer 10

OK guys. I had this answer once but lost it along the way. absolute torture when you know there's a better way but can't remember it or find it! It's very simple. I just tested it multiple ways.

var parent = _dbContext.Parents
  .Where(p => p.Id == model.Id)
  .Include(p => p.Children)
  .FirstOrDefault();

parent.Children = _dbContext.Children.Where(c => <Query for New List Here>);
_dbContext.Entry(parent).State = EntityState.Modified;

_dbContext.SaveChanges();

You can replace the whole list with a new one! The SQL code will remove and add entities as needed. No need to concern yourself with that. Be sure to include child collection or no dice. Good luck!

Answer 11

If you are using EntityFrameworkCore you can do the following in your controller post action (The Attach method recursively attaches navigation properties including collections):

_context.Attach(modelPostedToController);

IEnumerable<EntityEntry> unchangedEntities = _context.ChangeTracker.Entries().Where(x => x.State == EntityState.Unchanged);

foreach(EntityEntry ee in unchangedEntities){
     ee.State = EntityState.Modified;
}

await _context.SaveChangesAsync();

It is assumed that each entity that was updated has all properties set and provided in the post data from the client (eg. won't work for partial update of an entity).

You also need to make sure that you are using a new/dedicated entity framework database context for this operation.

Answer 12

Just proof of concept Controler.UpdateModel won't work correctly.

Full class here:

const string PK = "Id";
protected Models.Entities con;
protected System.Data.Entity.DbSet<T> model;

private void TestUpdate(object item)
{
    var props = item.GetType().GetProperties();
    foreach (var prop in props)
    {
        object value = prop.GetValue(item);
        if (prop.PropertyType.IsInterface && value != null)
        {
            foreach (var iItem in (System.Collections.IEnumerable)value)
            {
                TestUpdate(iItem);
            }
        }
    }

    int id = (int)item.GetType().GetProperty(PK).GetValue(item);
    if (id == 0)
    {
        con.Entry(item).State = System.Data.Entity.EntityState.Added;
    }
    else
    {
        con.Entry(item).State = System.Data.Entity.EntityState.Modified;
    }

}

Answer 13

I've been messing about with something like this...

protected void UpdateChildCollection<Tparent, Tid , Tchild>(Tparent dbItem, Tparent newItem, Func<Tparent, IEnumerable<Tchild>> selector, Func<Tchild, Tid> idSelector) where Tchild : class
    {
        var dbItems = selector(dbItem).ToList();
        var newItems = selector(newItem).ToList();

        if (dbItems == null && newItems == null)
            return;

        var original = dbItems?.ToDictionary(idSelector) ?? new Dictionary<Tid, Tchild>();
        var updated = newItems?.ToDictionary(idSelector) ?? new Dictionary<Tid, Tchild>();

        var toRemove = original.Where(i => !updated.ContainsKey(i.Key)).ToArray();
        var removed = toRemove.Select(i => DbContext.Entry(i.Value).State = EntityState.Deleted).ToArray();

        var toUpdate = original.Where(i => updated.ContainsKey(i.Key)).ToList();
        toUpdate.ForEach(i => DbContext.Entry(i.Value).CurrentValues.SetValues(updated[i.Key]));

        var toAdd = updated.Where(i => !original.ContainsKey(i.Key)).ToList();
        toAdd.ForEach(i => DbContext.Set<Tchild>().Add(i.Value));
    }

which you can call with something like:

UpdateChildCollection(dbCopy, detached, p => p.MyCollectionProp, collectionItem => collectionItem.Id)

Unfortunately, this kinda falls over if there are collection properties on the child type which also need to be updated. Considering trying to solve this by passing an IRepository (with basic CRUD methods) which would be responsible for calling UpdateChildCollection on its own. Would call the repo instead of direct calls to DbContext.Entry.

Have no idea how this will all perform at scale, but not sure what else to do with this problem.