Efcore: Docs: Better docs on implications of alternate keys (vs unique indexes)

[iberodev]

I am using EF Core SQL Server to delete entities and immediately create new ones but I am getting an exception. See below.
I managed to create a toy project here in this GitHub repository to illustrate the issue. (Simply clone the repository and dotnet run to trigger the error)

The scenario is simple. I have a table User, a table Group and a join table GroupMember in a typical scenario one-to-many and many-to-one: User --< GroupMember >-- Group.

I am trying to remove ALL the groupMembers for a given user and immediately after I am trying to add new groupMembers (its Guid Id is auto-generated, so there shouldn't be any conflict there..)

The code is this, but feel free to check the source code.
And my question is: What's the right way to achieve what I am trying to do during the same http request? (during the scoped lifetime of my context). I have tried saving the changes in context just before adding the new entities, but the problem persists.

public async Task UpdateGroupMembershipsAsync(Guid userId)
{
    var user = await _context.Users
        .Include(u => u.GroupMembers).ThenInclude(gm => gm.Group)
        .Where(u => u.Id == userId)
        .SingleOrDefaultAsync();

    _context.GroupMembers.RemoveRange(user.GroupMembers);
    user.GroupMembers.Clear();
    var groupMembersToAdd = GetGroupMembershipsToAdd();
    user.GroupMembers.AddRange(groupMembersToAdd);
    //this will fail when saving changes in DB.
    //Notice that if the groupMembership to add are not for any of the current groups it will succeed.
    await _context.SaveChangesAsync(); 
}

private IEnumerable<GroupMember> GetGroupMembershipsToAdd()
{
    var groupIdsToAdd = AppSettings.GetTheWhoMetallicaAndPinkFloydList();
    var groupMemberships = groupIdsToAdd.Select(id => new GroupMember
    {
        GroupId = id
    });
    return groupMemberships;
}

Steps to reproduce

Created sample project here in this GitHub repository

The issue

I am trying to bulk delete entities and then add new ones but there seems to be a problem with my approach. View log and project.

System.InvalidOperationException: The instance of entity type 'GroupMember' cannot be tracked because another instance of this type with the same key is already being tracked. When adding new entities, for most key types a unique temporary key value will be created if no key is set (i.e. if the key property is assigned the default value for its type). If you are explicitly setting key values for new entities, ensure they do not collide with existing entities or temporary values generated for other new entities. When attaching existing entities, ensure that only one entity instance with a given key value is attached to the context.

Further technical details

EF Core version: 1,0,1
Operating system: Win10
Visual Studio version: 2015 Update 3

[iberodev]

I have found out what may be causing this problem.
In my join table GroupMember I have the following map:

public GroupMemberMap(EntityTypeBuilder<GroupMember> entityBuilder)
{
    entityBuilder
        .HasKey(t => t.Id);

    entityBuilder.Property(t => t.Id)
        .IsRequired()
        .ValueGeneratedOnAdd()
        .HasDefaultValueSql("NEWID()");

    entityBuilder.HasAlternateKey(t => new { t.UserId, t.GroupId });

    entityBuilder.Property(t => t.UserId);

    entityBuilder.Property(t => t.GroupId);

    entityBuilder.HasOne(t => t.User)
        .WithMany(u => u.GroupMembers)
        .HasForeignKey(t => t.UserId)
        .OnDelete(DeleteBehavior.Cascade);

    entityBuilder.HasOne(t => t.Group)
        .WithMany(group => group.GroupMembers)
        .HasForeignKey(t => t.GroupId)
        .OnDelete(DeleteBehavior.Cascade);
}

Notice that I am creating an Alternate Key composed of userId and groupId to prevent more than want record relating the same user and group. However I just read at the docs that

If you just want to enforce uniqueness of a column then you want a unique index rather than an alternate key, see Indexes. In EF, alternate keys provide greater functionality than unique indexes because they can be used as the target of a foreign key.

Since I just wanted to enforce uniqueness I replaced my alternate key with an index:

//entityBuilder.HasAlternateKey(t => new { t.UserId, t.GroupId }); //this causes problems
entityBuilder.HasIndex(t => new { t.UserBusinessId, t.GroupId }).IsUnique(true);

and the problem is gone!

But the most interesting thing is that as soon as I replace in my code the alternate key with the index, without even applying the migration (so the DB still has the alternate key), the problem is also gone! This does not make much sense to me, why some code on my mapping that has not be reflected yet on DB modifies the behavior of Entity Framework..

Whether this is or not a bug I'll leave it for the EF team to decide. Please feel free to advice on these issues.

NOTE: Also I'd be keen to know if the approach to delete and add entities I am taking is recommended or whether there's a better way.

[rowanmiller]

This is by-design, and alternate key is the same as a primary key in that it can't be mutated and there should only ever be one entity with the given value(s). If you just want to enforce uniqueness, then a unique index is what you are after.

Leaving this open to add some more info on the implications to the note about alternate key vs. unique index in https://docs.efproject.net/en/latest/modeling/alternate-keys.html and then add the same text the Intellisense docs for the HasAlternateKey method.

[rowanmiller]

We added a more explicit note to the alternate key docs