1
\$\begingroup\$

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();
}
\$\endgroup\$
1
\$\begingroup\$

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.

\$\endgroup\$
  • \$\begingroup\$ 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! \$\endgroup\$ – Bob Horn Sep 9 '17 at 22:03
  • \$\begingroup\$ 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. \$\endgroup\$ – Bob Horn Sep 10 '17 at 16:05
  • \$\begingroup\$ 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... \$\endgroup\$ – Bob Horn Sep 10 '17 at 16:07
  • 1
    \$\begingroup\$ Thank you for your patient scrutiny! I typed it off the top of my head and a couple of mistakes sneaked in. \$\endgroup\$ – Gert Arnold Sep 10 '17 at 19:19
  • 1
    \$\begingroup\$ 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. \$\endgroup\$ – Bob Horn Sep 12 '17 at 14:03

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.