How to update a Collection in Many-Many by assigning a new Collection?

Question

In entity framework core 2.0, I have many-many relationship between Post and Category (the binding class is PostCategory).

When the user updates a Post, the whole Post object (with its PostCategory collection) is being sent to the server, and here I want to reassign the new received Collection PostCategory (the user may change this Collection significantly by adding new categories, and removing some categories).

Simplified code I use to update that collection (I just assign completely new collection):

var post = await dbContext.Posts
    .Include(p => p.PostCategories)
    .ThenInclude(pc => pc.Category)
    .SingleOrDefaultAsync(someId);

post.PostCategories = ... Some new collection...; // <<<
dbContext.Posts.Update(post);
await dbContext.SaveChangesAsync();

This new collection has objects with the same Id of objects in the previous collection (e.g. the user removed some (but not all) categories). Because of the, I get an exception:

System.InvalidOperationException: The instance of entity type 'PostCategory' cannot be tracked because another instance with the same key value for {'CategoryId', 'PostId'} is already being tracked.

How can I rebuild the new collection (or simply assign a new collection) efficiently without getting this exception?

UPDATE

The answer in this link seems to be related to what I want, but it is a good and efficient method? Is there any possible better approach?

UPDATE 2

I get my post (to edit overwrite its values) like this:

public async Task<Post> GetPostAsync(Guid postId)
{
    return await dbContext.Posts
        .Include(p => p.Writer)
            .ThenInclude(u => u.Profile)
        .Include(p => p.Comments)
        .Include(p => p.PostCategories)
            .ThenInclude(pc => pc.Category)
        .Include(p => p.PostPackages)
            .ThenInclude(pp => pp.Package)
        //.AsNoTracking()
        .SingleOrDefaultAsync(p => p.Id == postId);
}

UPDATE 3 (The code in my controller, which tries to update the post):

var writerId = User.GetUserId();
var categories = await postService.GetOrCreateCategoriesAsync(
    vm.CategoryViewModels.Select(cvm => cvm.Name), writerId);

var post = await postService.GetPostAsync(vm.PostId);
post.Title = vm.PostTitle;
post.Content = vm.ContentText;

post.PostCategories = categories?.Select(c => new PostCategory { CategoryId = c.Id, PostId = post.Id }).ToArray();

await postService.UpdatePostAsync(post); // Check the implementation in Update4.

UPDATE 4:

public async Task<Post> UpdatePostAsync(Post post)
{
    // Find (load from the database) the existing post
    var existingPost = await dbContext.Posts
        .SingleOrDefaultAsync(p => p.Id == post.Id);

    // Apply primitive property modifications
    dbContext.Entry(existingPost).CurrentValues.SetValues(post);

    // Apply many-to-many link modifications
    dbContext.Set<PostCategory>().UpdateLinks(
        pc => pc.PostId, post.Id,
        pc => pc.CategoryId,
        post.PostCategories.Select(pc => pc.CategoryId)
    );

    // Apply all changes to the db
    await dbContext.SaveChangesAsync();

    return existingPost;
}

Answer 1

The main challenge when working with disconnect link entities is to detect and apply the added and deleted links. And EF Core (as of the time of writing) provides little if no help to do that.

The answer from the link is ok (the custom Except method is too heavier for what it does IMO), but it has some traps - the existing links has to be retrieved in advance using the eager / explicit loading (though with EF Core 2.1 lazy loading that might not be an issue), and the new links should have only FK properties populated - if they contain reference navigation properties, EF Core will try to create new linked entities when calling Add / AddRange.

A while ago I answered similar, but slightly different question - Generic method for updating EFCore joins. Here is the more generalized and optimized version of the custom generic extension method from the answer:

public static class EFCoreExtensions
{
    public static void UpdateLinks<TLink, TFromId, TToId>(this DbSet<TLink> dbSet,
        Expression<Func<TLink, TFromId>> fromIdProperty, TFromId fromId,
        Expression<Func<TLink, TToId>> toIdProperty, IEnumerable<TToId> toIds)
        where TLink : class, new()
    {
        // link => link.FromId == fromId
        Expression<Func<TFromId>> fromIdVar = () => fromId;
        var filter = Expression.Lambda<Func<TLink, bool>>(
            Expression.Equal(fromIdProperty.Body, fromIdVar.Body),
            fromIdProperty.Parameters);
        var existingLinks = dbSet.AsTracking().Where(filter);

        var toIdSet = new HashSet<TToId>(toIds);
        if (toIdSet.Count == 0)
        {
            //The new set is empty - delete all existing links 
            dbSet.RemoveRange(existingLinks);
            return;
        }

        // Delete the existing links which do not exist in the new set
        var toIdSelector = toIdProperty.Compile();
        foreach (var existingLink in existingLinks)
        {
            if (!toIdSet.Remove(toIdSelector(existingLink)))
                dbSet.Remove(existingLink);
        }

        // Create new links for the remaining items in the new set
        if (toIdSet.Count == 0) return;
        // toId => new TLink { FromId = fromId, ToId = toId }
        var toIdParam = Expression.Parameter(typeof(TToId), "toId");
        var createLink = Expression.Lambda<Func<TToId, TLink>>(
            Expression.MemberInit(
                Expression.New(typeof(TLink)),
                Expression.Bind(((MemberExpression)fromIdProperty.Body).Member, fromIdVar.Body),
                Expression.Bind(((MemberExpression)toIdProperty.Body).Member, toIdParam)),
            toIdParam);
        dbSet.AddRange(toIdSet.Select(createLink.Compile()));
    }
}

It uses a single database query to retrieve the exiting links from the database. The overhead are few dynamically built expressions and compiled delegates (in order to keep the calling code simplest as possible) and a single temporary HashSet for fast lookup. The performance affect of the expression / delegate building should be negligible, and can be cached if needed.

The idea is to pass just a single existing key for one of the linked entities and list of exiting keys for the other linked entity. So depending of which of the linked entity links you are updating, it will be called differently.

In you sample, assuming you are receiving IEnumerable<PostCategory> postCategories, the process would be something like this:

var post = await dbContext.Posts
    .SingleOrDefaultAsync(someId);

dbContext.Set<PostCategory>().UpdateLinks(pc => 
    pc.PostId, post.Id, pc => pc.CategoryId, postCategories.Select(pc => pc.CategoryId));

await dbContext.SaveChangesAsync();

Note that this method allows you to change the requirement and accept IEnumerable<int> postCategoryIds:

dbContext.Set<PostCategory>().UpdateLinks(pc => 
    pc.PostId, post.Id, pc => pc.CategoryId, postCategoryIds);

or IEnumerable<Category> postCategories:

dbContext.Set<PostCategory>().UpdateLinks(pc => 
    pc.PostId, post.Id, pc => pc.CategoryId, postCategories.Select(c => c.Id));

or similar DTOs / ViewModels.

Category posts can be updated in a similar manner, with corresponding selectors swapped.

Update: In case you a receiving a (potentially) modified Post post entity instance, the whole update procedure cold be like this:

// Find (load from the database) the existing post
var existingPost = await dbContext.Posts
    .SingleOrDefaultAsync(p => p.Id == post.Id);

if (existingPost == null)
{
    // Handle the invalid call
    return;
}

// Apply primitive property modifications
dbContext.Entry(existingPost).CurrentValues.SetValues(post);

// Apply many-to-many link modifications
dbContext.Set<PostCategory>().UpdateLinks(pc => pc.PostId, post.Id, 
    pc => pc.CategoryId, post.PostCategories.Select(pc => pc.CategoryId));

// Apply all changes to the db
await dbContext.SaveChangesAsync();

Note that EF Core uses separate database query for eager loading related collecttions. Since the helper method does the same, there is no need to Include link related data when retrieving the main entity from the database.