Generic method for updating EFCore joins

Question

One thing that I am finding really tedious with the way EFCore handles many-to-many relationships is updating an entities joined collections. It is a frequent requirement that a viewmodel comes from frontend with a new list of nested entities and I have to write a method for each nested entity that works out what needs to be removed, what needs to be added and then do the removes and adds. Sometimes an entity has multiple many-to-many relationships and I have to write out pretty much the same code for each collection.

I think a generic method could be used here to stop me repeating myself but I am struggling to work out how.

First let me show you way I currently do it.

Lets say we have these models:

public class Person
{
    public int Id { get; set; }
    public string Name { get; set; }

    public virtual ICollection<PersonCar> PersonCars { get; set; } = new List<PersonCar>();
}

public class Car
{
    public int Id { get; set; }
    public string Manufacturer { get; set; }

    public virtual ICollection<PersonCar> PersonCars { get; set; } = new List<PersonCar>();
}

public class PersonCar
{
    public virtual Person Person { get; set; }
    public int PersonId { get; set; }
    public virtual Car Car { get; set; }
    public int CarId { get; set; }
}

And a key defined with fluent API

modelBuilder.Entity<PersonCar>().HasKey(t => new { t.PersonId, t.CarId });

And we add a new Person and list of associated cars:

var person = new Person
{
    Name = "John",
    PersonCars = new List<PersonCar>
    {
        new PersonCar { CarId = 1 },
        new PersonCar { CarId = 2 },
        new PersonCar { CarId = 3 }
    }
};

db.Persons.Add(person);

db.SaveChanges();

John owns cars 1,2,3. John updates his cars on frontend so now I get passed a new list of car ids so I update like this (actual code would be using a model and probably call into a method like this):

public static void UpdateCars(int personId, int[] newCars)
{
    using (var db = new PersonCarDbContext())
    {
        var person = db.Persons.Include(x => x.PersonCars).ThenInclude(x => x.Car).Single(x => x.Id == personId);

        var toRemove = person.PersonCars.Where(x => !newCars.Contains(x.CarId)).ToList();
        var toAdd = newCars.Where(c => !person.PersonCars.Any(x => x.CarId == c)).ToList();

        foreach (var pc in toRemove)
        {
            person.PersonCars.Remove(pc);
        }

        foreach (var carId in toAdd)
        {
            var pc = db.PersonCars.Add(new PersonCar { CarId = carId, PersonId = person.Id });
        }

        db.SaveChanges();
    }
}

I work out ones to remove, ones to add then do the actions. All very simple stuff but in real world an entity may have multiple many-to-many collections i.e. tags, categories, options etc.. and an application has several entities. Each update method is pretty much exactly the same and I end up with the same code repeated several times. For instance lets say Person also had a Category entity many-to-many relationship it would look like this:

public static void UpdateCategory(int personId, int[] newCats)
{
    using (var db = new PersonCarDbContext())
    {
        var person = db.Persons.Include(x => x.PersonCategories).ThenInclude(x => x.Category).Single(x => x.Id == personId);

        var toRemove = person.PersonCategories.Where(x => !newCats.Contains(x.CategoryId)).ToList();
        var toAdd = newCats.Where(c => !person.PersonCategories.Any(x => x.CategoryId == c)).ToList();

        foreach (var pc in toRemove)
        {
            person.PersonCategories.Remove(pc);
        }

        foreach (var catId in toAdd)
        {
            var pc = db.PersonCategories.Add(new PersonCategory { CategoryId = catId, PersonId = person.Id });
        }

        db.SaveChanges();
    }
}

It's exactly the same code just referencing different types and properties. I am ending up with this code repeated numerous times. Am I doing this wrong or is this a good case for a generic method?

I feel it's a good place for a generic to be used but i can't quite see how to do it.

It will need the type of entity, type of join entity and type of outer entity so maybe something like:

public T UpdateJoinedEntity<T, TJoin, Touter>(PersonCarDbContext db, int entityId, int[] nestedids)
{
    //.. do same logic but with reflection?
}

Method will then work out the right property and do the required removes and adds.

Is this feasible? I can't see how to do it but it looks like something that is possible.

Answer 1

"All very simple stuff", but not so simple to factorize, especially taking into account different key types, explicit or shadow FK properties etc., at the same time keeping the minimum method arguments.

Here is the best factorized method I can think of, which works for link (join) entities having 2 explicit int FKs:

public static void UpdateLinks<TLink>(this DbSet<TLink> dbSet, 
    Expression<Func<TLink, int>> fromIdProperty, int fromId, 
    Expression<Func<TLink, int>> toIdProperty, int[] toIds)
    where TLink : class, new()
{
    // link => link.FromId == fromId
    var filter = Expression.Lambda<Func<TLink, bool>>(
        Expression.Equal(fromIdProperty.Body, Expression.Constant(fromId)),
        fromIdProperty.Parameters);
    var existingLinks = dbSet.Where(filter).ToList();

    var toIdFunc = toIdProperty.Compile();
    var deleteLinks = existingLinks
        .Where(link => !toIds.Contains(toIdFunc(link)));

    // toId => new TLink { FromId = fromId, ToId = toId }
    var toIdParam = Expression.Parameter(typeof(int), "toId");
    var createLink = Expression.Lambda<Func<int, TLink>>(
        Expression.MemberInit(
            Expression.New(typeof(TLink)),
            Expression.Bind(((MemberExpression)fromIdProperty.Body).Member, Expression.Constant(fromId)),
            Expression.Bind(((MemberExpression)toIdProperty.Body).Member, toIdParam)),
        toIdParam);
    var addLinks = toIds
        .Where(toId => !existingLinks.Any(link => toIdFunc(link) == toId))
        .Select(createLink.Compile());

    dbSet.RemoveRange(deleteLinks);
    dbSet.AddRange(addLinks);
}

All it needs is the join entity DbSet, two expressions representing the FK properties, and the desired values. The property selector expressions are used to dynamically build query filters as well as composing and compiling a functor to create and initialize new link entity.

The code is not that hard, but requires System.Linq.Expressions.Expression methods knowledge.

The only difference with handwritten code is that

Expression.Constant(fromId)

inside filter expression will cause EF generating a SQL query with constant value rather than parameter, which will prevent query plan caching. It can be fixed by replacing the above with

Expression.Property(Expression.Constant(new { fromId }), "fromId")

With that being said, the usage with your sample would be like this:

public static void UpdateCars(int personId, int[] carIds)
{
    using (var db = new PersonCarDbContext())
    {
        db.PersonCars.UpdateLinks(pc => pc.PersonId, personId, pc => pc.CarId, carIds);
        db.SaveChanges();
    }
}

and also other way around:

public static void UpdatePersons(int carId, int[] personIds)
{
    using (var db = new PersonCarDbContext())
    {
        db.PersonCars.UpdateLinks(pc => pc.CarId, carId, pc => pc.PersonId, personIds);
        db.SaveChanges();
    }
}