我如何在实体框架核心中删除许多关系到许多关系

Question

我有两个实体框架实体。1(员工2(案例

我在实体框架核心中建模了许多与许多关系。

我能够在这样添加一个关系：

    [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;
    }

现在我正在尝试删除这样的关系之一：

    [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;
    }

您可以看到我的第一次尝试发表评论。因此，我尝试了第二种方式，只是恢复了要删除的记录的参考，将其从集合中删除，然后调用更新。但是我得到了这个错误：

"实体类型'案例'和'imhoseeecase'之间的关联已被切断，但这种关系的外键不能设置为无效。如果应删除因实体，请设置使用级联删除的关系。"

在这里，我不需要案件或雇员删除。我只需要删除加入记录。进入该记录的唯一方法是从案件或员工进入关系。

如果我使imploeecase对象成为DBSET，则该模型不会构建。

这是我的关系建立了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;
        }

我必须关闭Join对象，imhoseecase的删除，并且不能是DBSET。如果没有遵循这些步骤中的任何一个，那么许多模型都不会构建。

我要做的就是能够删除此记录。我不需要删除员工或案件。

这是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
}

和加入表(同样不是DBSET，但一切都在增加关系方面一直在起作用。现在无法删除。(

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; }
}

如何将这么多的关系记录删除？

Answer 1

我遇到了同样的需求，并在此处找到了这篇文章。由于尚未发布真正的解决方案(使用EF(，因此我决定添加此答案：

另请参阅"实体框架核心中的许多关系"。这是用于更新，但相同的原则可用于删除。

这是我的代码(我的方案是用户N↔n组(：

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();

UserGroups字段是包含group和groupId，user和userId Properties的中间类(表(的列表。

Answer 2

我有几乎相同的问题(几乎相同的基础类；删除" a"记录时，我解决的方式：

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

因此，当包含B时，EntityFrameWorkCore似乎会自动解决该问题。(要使用'include'不要忘记使用Microsoft.entityframeworkcore;'(

Answer 3

解决方案非常简单。而不是在另一个内部找到实体并试图将其删除。将关系设置为DeleteBehaviour.Cascase。

例如。

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
}

然后删除正确的关系

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

由于级联行为，这将删除许多与许多人的关系。

这样做的任何其他方法都会抛出重复的密钥异常或其他方式。

希望这对某人有帮助

Answer 4

我最终只是使用了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();
    }

我开始倾向于使用实体框架查询。(让.net序列化与我的查询序列化。(但是所有其他带有原始SQL或存储过程的CRUD(数据操作(。