Entity Framework - Add or Update Parent Entity with Children

Question

This code saves an DomainEntityRecord (parent) and DomainEntityDetailRecords (child), and it works. It feels odd that I get the record from the DB, then remove the entities that don't exist in the domain model version, then when I save, I save (AddOrUpdate) the domain entities, and not the EF version. Maybe this is the right way?

public void Handle(SaveDomainEntity message)
{
 var domainEntityRecord = mapper.Map<DomainEntityRecord>(message);
 var existingDomainEntity = dbContext.Set<DomainEntityRecord>()
 .Where(x => x.Id == domainEntityRecord.Id)
 .Include(x => x.DomainEntityDetailRecords)
 .SingleOrDefault();
 if (existingDomainEntity != null)
 {
 // Delete detail records that no longer exist.
 foreach (var existingDetail in existingDomainEntity.DomainEntityDetailRecords.ToList())
 {
 if (domainEntityRecord.DomainEntityDetailRecords.All(
 x => x.DetailId != existingDetail.DetailId))
 {
 dbContext.Set<DomainEntityDetailRecord>().Remove(existingDetail);
 }
 }
 }
 dbContext.Set<DomainEntityRecord>().AddOrUpdate(domainEntityRecord);
 domainEntityRecord.DomainEntityDetailRecords.ForEach(
 record => dbContext.Set<DomainEntityDetailRecord>().AddOrUpdate(record));
 dbContext.SaveChanges();
}

Answer 1

The line ...

 dbContext.Set<DomainEntityRecord>().AddOrUpdate(domainEntityRecord);

... will always try to find an existing record in the database in order to determine whether domainEntityRecord should be marked as Added or as Modified.

But you already fetch the existing record by the statement:

var existingDomainEntity = dbContext.Set<DomainEntityRecord>()
 .Where(x => x.DomainEntityId == domainEntityRecord.DomainEntityId)
 .Include(x => x.DomainEntityDetailRecords)
 .SingleOrDefault();

So there is a redundant database roundtrip in your code. After this statement var existingDomainEntity = ... you know everything that AddOrUpdate is going to find out again. So you may as well do it yourself: if the record exist: modify it and its details, if it doesn't: add it. To modify the existing records, use CurrentValues.SetValues:

 var domainEntityRecord = mapper.Map<DomainEntityRecord>(message);
 var existingDomainEntity = dbContext.Set<DomainEntityRecord>()
 .Where(x => x.DomainEntityId == domainEntityRecord.DomainEntityId)
 .Include(x => x.DomainEntityDetailRecords)
 .SingleOrDefault();
 if (existingDomainEntity != null)
 {
 // Delete detail records that no longer exist.
 foreach (var existingDetail in existingDomainEntity.DomainEntityDetailRecords.ToList())
 {
 if (domainEntityRecord.DomainEntityDetailRecords.All(
 x => x.DomainEntityDetailId != existingDetail.DomainEntityDetailId))
 {
 dbContext.Set<DomainEntityDetailRecord>().Remove(existingDetail);
 }
 }
 // Copy current (incoming) values to db entry:
 dbContext.Entry(existingDomainEntity).CurrentValues.SetValues(domainEntityRecord);
 var detailPairs = from curr in domainEntityRecord.DomainEntityDetailRecords
 join db in existingDomainEntity.DomainEntityDetailRecords 
 on curr.DomainEntityDetailId equals db.DomainEntityDetailId into grp
 from db in grp.DefaultIfEmpty()
 select new { curr, db };
 foreach(var pair in detailPairs)
 {
 if (pair.db != null)
 dbContext.Entry(pair.db).CurrentValues.SetValues(pair.curr);
 else
 dbContext.Set<DomainEntityDetailRecord>().Add(pair.curr);
 }
 }
 else
 {
 dbContext.Set<DomainEntityRecord>().Add(domainEntityRecord);
 // This also adds its DomainEntityDetailRecords
 }
 dbContext.SaveChanges();

As you see, for the details I use a GroupJoin (join - into which serves as an outer join) to determine the existing and the new details. The existing ones are modified, the new ones are added.

• Bob Horn: That's an excellent point about the unnecessary DB trip. I'll apply these improvements tonight or tomorrow and then accept/upvote this answer. Thanks!
• Bob Horn: I tried fixing two typos, but I got an error that the code wasn't formatted properly. Tried to fix, but still wouldn't take. The foreach should use detailPairs, not just pairs. And the Add a couple of lines later should use pair.curr, not just curr.
• Bob Horn: Also, when there is a new detail record, I don't think the join is recognizing it. The existing three details get updated, but the new detail doesn't get added. Looking into that now...
• Gert Arnold: Thank you for your patient scrutiny! I typed it off the top of my head and a couple of mistakes sneaked in.
• Bob Horn: I used SQL Profiler to check out the difference between my original code and the code in this answer. They each generate 5 updates (1 header, 4 detail). However, my original code also generates a SELECT TOP 2... for each AddOrUpdate(). So these findings confirm what Gert said. The original code has 5 extra SELECT statements; one for each AddOrUpdate(). Good stuff.