How to handle children updates in EF

Question

I have an action

[HttpPost]
public string Edit(Member member)

and Member has a collection of children entities ICollection<AgeBracket> AgeBrackets.

Currently I do retrieve all AgeBrackets associated with the member, mark everyone as deleted, then loop through new collection and create a new entry for each. Then I update my parent entity. It works, but there should be a better way to do it:

for example, if I would wrote SQL, I could delete all existing children with just one line

DELETE FROM AgeBrackets WHERE MemberId = @MemberId

In my situation it makes a select to retrieve existing items, then generate delete for each of them, then generate insert for each new child and then it generates update for parent.

Here is how my code looks now:

IList<AgeBracket> ageBrackets = db.AgeBrackets.Where<AgeBracket>(x => x.MemberId == member.MemberId).ToList();
        foreach (AgeBracket ab in ageBrackets)
            db.Entry(ab).State = EntityState.Deleted;
        if (member.AgeBrackets != null)
        foreach (AgeBracket ab in member.AgeBrackets)
        {
            ab.MemberId = member.MemberId;                  
            db.AgeBrackets.Add(ab);
        }
        db.Entry(member).State = EntityState.Modified;

Initially I was trying to query existing children and compare each of them to new set, but it seems to be over-complicated.

What is the best way to update member and all it's children?

Answer 1

There's another way to do

var originalAgeBrackets = db.AgeBrackets.Where(x => x.MemberId == member.MemberId).ToArray();
var currentAgeBrackets = member.AgeBrackets;

foreach (var original in originalAgeBrackets) {
    // check if the original age brackets were modified ou should be removed
    var current = currentAgeBrackets.FirstOrDefault(c => c.AgeBracketId == original.AgeBracketId);

    if(current != null) {
        var entry = db.Entry(original);
        entry.OriginalValues.SetValues(original);
        entry.CurrentValues.SetValues(current);
    } else {
        db.Entry(original).State = EntityState.Deleted;
    }
}

// add all age brackets not listed in originalAgeBrackets
foreach (var current in currentAgeBrackets.Where(c => !originalAgeBrackets.Select(o => o.AgeBracketId).Contains(c.AgeBracketId))) {
    db.AgeBrackets.Add(current);
}

db.SaveChanges();

Unfortunately what you want to do haven't native support to EF Code First. What will help you would be EntityFramework.Extended. This will allow you to do something like:

db.AgeBrackets.Delete(a => a.MemberId == member.MemberId);

You should take care of change-tracks by yourself. Hope it helps you.