Entity Framework 6 custom many-to-many child with implicit insert and delete

Question

I have parent object (LoanApplication) with a child (LoanApplicationQualificationTypes) that is a custom many-to-many table. The reason I have a custom is that it has two audit columns that need to be populated (ModifiedBy, ModifiedDate).

To get the children that were added or removed from the child collection to be persisted in the database correctly, I had to explicitly handle.

Below is the code (simplified by removing other properties that were germane to the question).

Parent (part of many-to-many):

[Serializable]
[Table("LoanApplication")]
public class LoanApplication : BaseDomainModelWithId, ILoanApplication
{
    [Key]
    [Column("LoanApplicationId")]
    public override int? Id { get; set; }

    [ForeignKey("LoanApplicationId")]
    public virtual ICollection<LoanApplicationQualificationTypes> LoanApplicationQualificationTypes { get; set; }

    IReadOnlyCollection<ILoanApplicationQualificationTypes> ILoanApplication.LoanApplicationQualificationTypes
    {
        get
        {
            var loanApplicationQualificationTypes = new List<ILoanApplicationQualificationTypes>();

            if (LoanApplicationQualificationTypes == null) return loanApplicationQualificationTypes;

            loanApplicationQualificationTypes.AddRange(LoanApplicationQualificationTypes);

            return loanApplicationQualificationTypes.AsReadOnly();
        }
        set
        {
            foreach (var item in value)
            {
                LoanApplicationQualificationTypes.Add((LoanApplicationQualificationTypes)item);
            }
        }
    }

    public LoanApplication() : base()
    {
        LoanApplicationQualificationTypes = new List<LoanApplicationQualificationTypes>();
    }

}

public interface ILoanApplication : IDomainModel, ILoanApplicationBase, IKeyIntId
{
    IReadOnlyCollection<ILoanApplicationQualificationTypes> LoanApplicationQualificationTypes { get; set; }
}

Object part of many-to-many:

[Serializable]
[Table("QualificationType")]
public class QualificationType : IQualificationType
{
    [Key]
    [Column("QualificationTypeId")]
    public override int? Id { get; set; }

    [Required]
    public string TypeName { get; set; }

    [Required]
    public bool IsActive { get; set; }

    public virtual string ModifiedBy { get; set; }

    public virtual DateTimeOffset? ModifiedDate { get; set; }

    public QualificationType() : { }
}

Custom Many-to-Many:

[Serializable]
[Table("LoanApplicationQualificationTypes")]
public class LoanApplicationQualificationTypes : ILoanApplicationQualificationTypes
{
    [Key]
    [Column(Order = 1)]
    public int? LoanApplicationId { get; set; }

    [ForeignKey("LoanApplicationId")]
    public virtual LoanApplication LoanApplication { get; set; }

    ILoanApplication ILoanApplicationQualificationTypes.LoanApplication
    {
        get
        {
            return this.LoanApplication;
        }
        set
        {
            this.LoanApplication = (LoanApplication)value;
        }
    }

    [Required]
    [Key]
    [Column(Order = 2)]
    public int QualificationTypeId { get; set; }

    [ForeignKey("QualificationTypeId")]
    public virtual QualificationType QualificationType { get; set; }

    IQualificationType ILoanApplicationQualificationTypes.QualificationType
    {
        get
        {
            return this.QualificationType;
        }
        set
        {
            this.QualificationType = (QualificationType)value;
        }
    }

    public virtual string ModifiedBy { get; set; }

    public virtual DateTimeOffset? ModifiedDate { get; set; }

    public LoanApplicationQualificationTypes() { }
}

Update method in LoanApplication Repository:

public bool Update(ILoanApplication entity)
{
    using (var db = new MainContext())
    {
        entity.ModifiedDate = DateTime.UtcNow;
        entity.ModifiedBy  = UserOrProcessName;

        // Add / Remove LoanApplicationQualificationTypes and populate audit columns
        if (entity.LoanApplicationQualificationTypes?.Count > 0)
        {
            var existingItems = db.LoanApplicationQualificationTypes.Where(q => q.LoanApplicationId == entity.Id.Value).ToList();
            var newItems = entity.LoanApplicationQualificationTypes.Where(q => existingItems.All(e => e.QualificationTypeId != q.QualificationTypeId));
            var deletedItems = existingItems.Where(q => entity.LoanApplicationQualificationTypes.All(e => e.QualificationTypeId != q.QualificationTypeId));

            foreach (var newItem in newItems)
            {
                newItem.ModifiedBy = UserOrProcessName;
                newItem.ModifiedDate = DateTime.UtcNow;

                db.LoanApplicationQualificationTypes.Add((LoanApplicationQualificationTypes)newItem);
            }

            foreach (var deletedItem in deletedItems)
            {
                db.LoanApplicationQualificationTypes.Remove((LoanApplicationQualificationTypes)deletedItem);
            }

            // Need to clear to avoid duplicate objects
            ((LoanApplication)entity).LoanApplicationQualificationTypes.Clear();
        }

        db.Entry(entity).State = EntityState.Modified;
        db.SaveChanges();
    }

    return true;
}

Is there a way implement the Update without the explicitly handling adds/updates?

Answer 1

The way I understand it, the question is how to apply the (potential) modifications to the link table without explicitly detecting added/removed links. Also I assume the other part of the link must exist.

It's possible with the following sequence of operations:

First load the actual entity from the database into context, including the links:

var dbEntity = db.LoanApplication
    .Include(e => e.LoanApplicationQualificationTypes)
    .FirstOrDefault(e => e.Id == entity.Id);

This will allow change tracker to determine the correct add/update/delete link operations for you later.

Then apply the primitive master data changes:

db.Entry(dbEntity).CurrentValues.SetValues(entity);
dbEntity.ModifiedDate = DateTime.UtcNow;
dbEntity.ModifiedBy = UserOrProcessName;

Finally, replace the links with the ones from the incoming entity. To avoid navigation property references pointing to different objects (and in particular to prevent EF trying to create the new records for the other side objects of the relation), do not use directly the incoming objects, but create stub objects with only FK properties set:

dbEntity.LoanApplicationQualificationTypes = entity.LoanApplicationQualificationTypes
    .Select(e => new LoanApplicationQualificationTypes
    {
        LoanApplicationId = e.LoanApplicationId,
        QualificationTypeId = e.QualificationTypeId,
        ModifiedDate = DateTime.UtcNow,
        ModifiedBy = UserOrProcessName,
    })
    .ToList();

And that's it. At this point the change tracker has all the necessary information to produce the correct commands when you call db.SaveChanges().

One thing to mention. If you look at db.ChangeTracker.Entries at this point, you'll probably notice that all the old links are marked as Deleted, all the incoming as Added and there are no Modified entries. Don't worry. EF is smart enough and will convert Deleted + Added pairs with the same PK to single update commands.

The whole method:

public bool Update(ILoanApplication entity)
{
    using (var db = new MainContext())
    {
        var dbEntity = db.LoanApplication
            .Include(e => e.LoanApplicationQualificationTypes)
            .FirstOrDefault(e => e.Id == entity.Id);

        if (dbEntity == null) return false;

        db.Entry(dbEntity).CurrentValues.SetValues(entity);
        dbEntity.ModifiedDate = DateTime.UtcNow;
        dbEntity.ModifiedBy = UserOrProcessName;

        dbEntity.LoanApplicationQualificationTypes = entity.LoanApplicationQualificationTypes
            .Select(e => new LoanApplicationQualificationTypes
            {
                LoanApplicationId = e.LoanApplicationId,
                QualificationTypeId = e.QualificationTypeId,
                ModifiedDate = DateTime.UtcNow,
                ModifiedBy = UserOrProcessName,
            })
            .ToList();

        db.SaveChanges();
        return true;
    }
}