How can I delete a many to many relationship in Entity Framework Core?

Question

I have two Entity Framework Entities. 1) Employee 2) Case

I've modeled a many to many relationship in Entity Framework Core.

I am able to add a relationship in like this:

    [HttpPost]
    public async Task<Employee> AddCaseEmployee([FromBody]EmployeeCase employeeCase)
    {
        Case lcase = await _caseGenericService.GetSingleIncludingAsync(employeeCase.CaseId);

        lcase.EmployeeCases = new List<EmployeeCase>();
        lcase.EmployeeCases.Add(new EmployeeCase
            {
                CaseId = employeeCase.CaseId,
                EmployeeId = employeeCase.EmployeeId
            }
        );

        await _caseGenericService.UpdateAsync(lcase);

        Employee employee = await _employeeGenericService.GetSingleIncludingAsync(employeeCase.EmployeeId);
        return employee;
    }

Now I am trying to remove one of the relationships like this:

    [HttpDelete]
    public async Task<Employee> DeleteCaseEmployee(long employeeId, long caseId)
    {
        Case lcase = await _caseGenericService.GetSingleIncludingAsync(caseId,
            a => a.EmployeeCases);

        //lcase.EmployeeCases.Remove(new EmployeeCase
        //{
        //    CaseId = caseId,
        //    EmployeeId = employeeId
        //}
        //);
        EmployeeCase employeeCase = 
            lcase.EmployeeCases.FirstOrDefault(ec => ec.EmployeeId == employeeId && ec.CaseId == caseId);
        lcase.EmployeeCases.Remove(employeeCase);

        try
        {
            await _caseGenericService.UpdateAsync(lcase);
        }
        catch(Exception ex)
        {

        }

        Employee employee = await _employeeGenericService.GetSingleIncludingAsync(employeeId);
        return employee;
    }

You can see my first attempt commented out. So I tried the second way, just getting back the reference of the record I want to delete, removing it from the collection and then calling update. But I get this error:

"The association between entity types 'Case' and 'EmployeeCase' has been severed but the foreign key for this relationship cannot be set to null. If the dependent entity should be deleted, then setup the relationship to use cascade deletes."

Here I don't need a Case or an Employee deleted. I just need to delete the join record. And the only way to get into that record is to come in from a Case or an Employee to the relationship.

If I make the EmployeeCase Object a Dbset the model won't build.

Here is my relationship set up OnModelBuilding:

    modelBuilder.Entity<EmployeeCase>()
            .HasKey(ec => new { ec.EmployeeId, ec.CaseId });

        modelBuilder.Entity<EmployeeCase>()
            .HasOne(ec => ec.Employee)
            .WithMany(e => e.EmployeeCases)
            .HasForeignKey(ec => ec.EmployeeId);

        modelBuilder.Entity<EmployeeCase>()
            .HasOne(ec => ec.Case)
            .WithMany(c => c.EmployeeCases)
            .HasForeignKey(ec => ec.CaseId);

        // Get rid of Cascading Delete Circular references error.
        var type = modelBuilder.Model.GetEntityTypes().Single(t => t.Name == "Hrsa.Core.Generic.Model.Lerd.EmployeeCase");
        foreach (var relationship in type.GetForeignKeys())
        {
            relationship.DeleteBehavior = DeleteBehavior.Restrict;
        }

I had to turn off Deletes for the join Object, EmployeeCase, and it cannot be a DbSet. If either of these steps are not followed the many to many model just will not build.

All I need to do is to be able to delete this record. I don't need to delete an Employee or Case.

Here are the POCOs.

public class Employee : BaseEntity
{
    [BindNever]
    public string Email { get; set; }
    [BindNever]
    public long OrganizationId { get; set; }
    [BindNever]
    public string Supervisor { get; set; }
    [BindNever]
    public string SupervisorEmail { get; set; }
    [BindNever]
    public string FirstName { get; set; }
    [BindNever]
    public string LastName { get; set; }
    public string Notes { get; set; }
    [BindNever]
    public  long BargainingUnitId { get; set; }
    [BindNever]
    public long PayPlanId { get; set; }
    [BindNever]
    public long GradeRankId { get; set; }
    [BindNever]
    public long PositionTitleId { get; set; }
    [BindNever]
    public long SeriesId { get; set; }
    public bool IsUnionEmployee { get; set; }
    public virtual Organization Organization { get; set; }
    public virtual BargainingUnit BargainingUnit { get; set; }
    public virtual PayPlan PayPlan { get; set; }
    public virtual GradeRank GradeRank { get; set; }
    public virtual PositionTitle PositionTitle { get; set; }
    public virtual  Series Series { get; set; }
    public virtual ICollection<UnionHours> UnionHours { get; set; }
    [NotMapped]
    public string UnionEmployeeYesNo => (IsUnionEmployee) ? "Yes" : "No";

    public virtual ICollection<EmployeeCase> EmployeeCases { get; set; }

}

public class Case : BaseEntity
{
    #region Common Case Properties
    public long InitialContactId { get; set; }
    public virtual Employee InitialContact { get; set; }
    [DataType(DataType.Date)]
    public DateTime? InitialConsultDate { get; set; }
    public string LerSpecialistId { get; set; }
    public virtual AppUser LerSpecialist { get; set; }
    public string Notes { get; set; }

    public bool IsDeleted { get; set; }
    public CaseType CaseType { get; set; }
    #endregion

    #region EmployeeCase Properties
    //public long PrimaryEmployeeId { get; set; }
    //public virtual Employee PrimaryEmployee {get;set;}
    public virtual ICollection<EmployeeCase> EmployeeCases { get; set; }
    #endregion

    #region NonEmployeeCase Properties
    //public long PrimaryNonEmployeeId { get; set; }
    //public virtual Employee PrimaryEmployee {get;set;}
    public virtual ICollection<NonEmployeeCase> NonEmployeeCases { get; set; }
    #endregion
}

and the join table (which again, is not a DbSet, but everything has been working as far as adding relationships. Now just can't delete.)

public class EmployeeCase
{
    [ForeignKey("Id")]
    public long EmployeeId { get; set; }
    public Employee Employee { get; set; }
    [ForeignKey("Id")]
    public long CaseId { get; set; }
    public Case Case { get; set; }

}

How can I delete this many to many relationship record?

Juglandaceous

I had to turn off Deletes for the join Object, EmployeeCase, and it cannot be a DbSet. If either of these steps are not followed the many to many model just will not build. Why? What's the problem? Normally it builds. – Clemence

Not sure why. Are you using Core? Does it normally build in Core? – Juglandaceous

I do. It's pretty standard (and the only currently supported) many-to-many model in EF Core. May be the other fields (or the commented) are messing-up something. You may try providing a minimal reproducible example. – Clemence

Answer 1

I came across the same need, and found this post here. Since the real solution (using EF) hasn't been posted yet, I decided to add this answer:

See also Updating many to many relationships in Entity Framework Core; that's for updating, but the same principle works for deleting.

Here is my code (my scenario is users N↔N groups):

var groupToUpdate = _userGroupsContext.Groups.Include(g => g.UserGroups).Single(u => u.Id == userVm.groupsIds[0]);
var userToUpdate = _userGroupsContext.Users.Single(u => u.Id == userVm.user.Id);

groupToUpdate.UserGroups.Remove(groupToUpdate.UserGroups.Where(ugu => ugu.UserId == userToUpdate.Id).FirstOrDefault());
_userGroupsContext.SaveChanges();

The UserGroups field is a list of the intermediate class (table) that contains the group and groupId, user and userId properties.

Saurel

It do not work for me. The Remove function has a bool return how to manage that ?. I follow this an the article aproach and my .NET Core crash and expose: "Step into: Stepping over non-user code 'DataAccess.Repositories.ProjectRepository.<>c__DisplayClass9_0..ctor' The program '[22616] testhost.exe: Program Trace' has exited with code 0 (0x0). The program '[22616] testhost.exe' has exited with code -1073741819 (0xc0000005) 'Access violation'." Did you use ICollection<Entity> or List<Entity> in yours Class ? – Perimeter

Answer 2

I had almost the same problem (with almost the same underlying classes; A<->B many-to-many with an intermediate AB class); The way I solved it, when deleting an "A" record:

var targetToDelete = await context.A.Include(a =>a.B)                
            .Where(a => a.Id == idToDelte)
            .FirstOrDefaultAsync();
context.A.Remove(targetToDelete );

So, when B is included, EntityFrameworkCore seems to solve the problem automatically. (To use 'Include' don't forget to add 'using Microsoft.EntityFrameworkCore;')

Exceedingly

Answer 3

The solution is quite simple. Instead of finding the entity within another and trying to remove it. Have the relationship set to DeleteBehaviour.Cascase.

For example.

public class EntityA
{
//my properties
public virtual ICollection<EntityB> EntityBCollection {get;set;}
}


public class EntityB
{
//my properties
public virtual ICollection<EntityA> EntityACollection {get;set;}
}

public class EntityAConfiguration: IEntityTypeConfiguration<EntityA>
{
 public void Configure(EntityTypeBuilder<EntityA> entity)
 {
    entity.HasMany(e => e.EntityBCollection).WithMany(e => e.EntityAConfiguration).OnDelete(DeleteBehaviour.Cascade)
 }

//do the same for EntityB Configuration
}

Then to delete with the correct relationships

public async Task SomeFunction(int idToDeleteFromEntityB)
{
var entityb = context.Set<EntityB>().FindAsync(x => x.id == idToDeleteFromEntityB);

context.set<EntityB>().Remove(entityb);
context.SaveChanges();

}

this will delete the relationship for the many to many as well due to the Cascade behaviour.

Doing it any other way would throw a duplicate key exception or something else along the lines.

hope this helps someone

Unpeople

Answer 4

I ended up just using raw sql:

    [HttpPost]
    public async Task<Employee> AddCaseEmployee([FromBody]EmployeeCase employeeCase)
    {
        string queryString = "INSERT INTO EmployeeCase VALUES (@EmployeeId, @CaseId)";
        SqlParameter[] sqlParams = new SqlParameter[]
        {
            new SqlParameter { ParameterName = "EmployeeId", Value = employeeCase.EmployeeId },
            new SqlParameter { ParameterName = "CaseId", Value = employeeCase.CaseId },
        };
        _context.Database.ExecuteSqlCommand(queryString, sqlParams);
        Employee employee = await _employeeGenericService.GetSingleIncludingAsync(employeeCase.EmployeeId);

        return employee;
    }

    [Authorize(Roles = "LERSpecialist, Admin")]
    [HttpDelete]
    public async Task<IActionResult> DeleteCaseEmployee(long employeeId, long caseId)
    {

        string queryString = "DELETE FROM EmployeeCase WHERE EmployeeId = @EmployeeId AND CaseId = @CaseId";
        SqlParameter[] sqlParams = new SqlParameter[]
        {
            new SqlParameter { ParameterName = "EmployeeId", Value = employeeId },
            new SqlParameter { ParameterName = "CaseId", Value = caseId },
        };
        _context.Database.ExecuteSqlCommand(queryString, sqlParams);

        return NoContent();
    }

I'm starting to lean towards querying with Entity Framework. (Let .Net Serialize to JSON for me on Querying.) But all other CRUD (data manipulation) with either RAW Sql or Stored Procedures.

Juglandaceous