EF Core delete data in many to many relationship

Question

I have the following tables:

public class Team 
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

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

    public virtual ICollection<UserTeam> UserTeams { get; set; }
}

public class User
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }

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

    public virtual ICollection<UserTeam> UserTeams { get; set; }
}

public class UserTeam
{
    public long UserId { get; set; }
    public User User { get; set; }

    public long TeamId { get; set; }
    public Team Team { get; set; }
}

the many to many relationship is defined in context:

modelBuilder.Entity<UserTeam>()
    .HasKey(bc => new { bc.UserId, bc.TeamId });

modelBuilder.Entity<UserTeam>()
    .HasOne(bc => bc.User)
    .WithMany(b => b.UserTeams)
    .HasForeignKey(bc => bc.UserId)
    .OnDelete(DeleteBehavior.Restrict);

modelBuilder.Entity<UserTeam>()
    .HasOne(bc => bc.Team)
    .WithMany(c => c.UserTeams)
    .HasForeignKey(bc => bc.TeamId)
    .OnDelete(DeleteBehavior.Restrict);

I am trying to delete some users from a team with the following code:

public async Task RemoveUsersFromTeam(int teamId, List<long> users)
{
    Team existingTeam = await dbContext.Team.Include(x => x.UserTeams).FirstOrDefaultAsync(x => x.Id == teamId);
    foreach (var user in users)
    {
        existingTeam.UserTeams.Remove(new UserTeam { UserId = user });
    }

    await dbContext.SaveAsync();
}

but this query is not deleting the users that I pass. Anyone knows why this happens?

Don't you have to attach the `UserTeam` to the context first? — Dave Williams, Nov 28 '19 at 07:55
but it is not a disconnected scenario. First I query the team (which is attached) then I modify it. Shouldn't be tracking changes? — pantonis, Nov 28 '19 at 07:57
You are creating the `UserTeam` so you need to let EF know it is something that already exists e.g. attach it to the graph. At least in EF 6. See https://stackoverflow.com/questions/2471433/how-to-delete-an-object-by-id-with-entity-framework — Dave Williams, Nov 28 '19 at 07:58
I get ```The instance of entity type 'UserTeam' cannot be tracked because another instance with the key value '{UserId: 1, TeamId: 14}' is already being tracked. When attaching existing entities, ensure that only one entity instance with a given key value is attached``` — pantonis, Nov 28 '19 at 08:02
Take the include off, or you need to actually select the `UserTeam` from `Team` and remove it. But attaching is more efficient as your initial select will return less data. — Dave Williams, Nov 28 '19 at 08:03
I get ```Object Reference not set to an instance..``` because by removing include the UsersTeams is null. — pantonis, Nov 28 '19 at 08:08
Yeah, your Team object probably needs a constructor to initialize `UserTeams` to an empty collection. Which should be there anyway. — Dave Williams, Nov 28 '19 at 08:09
Are you sure it is a good idea to initialize collection in the constructor of a database class? — pantonis, Nov 28 '19 at 08:11
How would you add to it otherwise? https://www.entityframeworktutorial.net/code-first/configure-many-to-many-relationship-in-code-first.aspx — Dave Williams, Nov 28 '19 at 08:13
Let us [continue this discussion in chat](https://chat.stackoverflow.com/rooms/203222/discussion-between-dave-williams-and-pantonis). — Dave Williams, Nov 28 '19 at 08:19
Ok it is clear thanks. Please answer the question to mark it as answered — pantonis, Nov 28 '19 at 08:20
Does this answer your question? [How to delete an object by id with entity framework](https://stackoverflow.com/questions/2471433/how-to-delete-an-object-by-id-with-entity-framework) — Dave Williams, Nov 28 '19 at 08:24
You only need one modification: `dbContext.UserTeams.Remove(new UserTeam { UserId = user, TeamId = teamId });`. Instead of `existingTeam.UserTeams.Remove...`. And you don't even need to query `existingTeam` ! — Gert Arnold, Nov 28 '19 at 12:21

score 1 · Accepted Answer · edited Jun 20 '20 at 09:12

You can delete objects by Id using the new and attach method, or by passing the actual entity.

Passing Entity

public async Task RemoveUsersFromTeam(int teamId, List<long> userIds)
{
    Team existingTeam = await dbContext.Team.Include(x => x.UserTeams).FirstOrDefaultAsync(x => x.Id == teamId);
    foreach (var userId in userIds)
    {
        var userTeam = existingTeam.UserTeams.FirstOrDefault(x => x.UserId == userId);
        if(userTeam != null)
        {
            existingTeam.UserTeams.Remove(userTeam);
        }
    }

    await dbContext.SaveAsync();
}

Delete By Id

public async Task RemoveUsersFromTeam(int teamId, List<long> userIds)
{
    Team existingTeam = await dbContext.Team.FirstOrDefaultAsync(x => x.Id == teamId);
    foreach (var userId in userIds)
    {
        var userTeam = new UserTeam { UserId = userId });
        dbContext.UserTeams.Attach(userTeam);
        existingTeam.UserTeams.Remove(userTeam);
    }

    await dbContext.SaveAsync();
}

Option two does not require selecting UserTeams from the database and will be slightly more efficient in that respect. However option one may be more understandable. You should choose which best fits your situation.

Personally I prefer option two, as include will select the whole entity and in some cases that could be a lot more than necessary.

Same. option 2 is much better. However when I try to do the same but with ```existingTeam.UserTeams.Add(userTeam)``` method the entry is not inserted to db — pantonis, Nov 28 '19 at 08:47
When adding, you should not attach the entity as that would be saying that it already exists and issue an update rather than insert. — Dave Williams, Nov 28 '19 at 08:54

EF Core delete data in many to many relationship

1 Answers1

Passing Entity

Delete By Id