c# - 添加到多对多关系表会在数据库中产生重复值

Question

我有三个表 zRequest、zFacility 和一个表示两者之间多对多关系的表，其中只有每个表的 ID。我的目标是添加一个具有许多功能的请求。但是每当我添加带有某些设施的新请求时，它会将所选设施添加到 zFacility 并添加到关系表中。

这是 Controller 中的相关代码:

foreach (var facility in Facilities)
{
    var facName = facility.Replace(".", " ");
    var facQry = from fac in db.zFacilities where fac.FacilityName == facName select fac;
    var facList = facQry.ToList();
    var item = new zFacility();
    item.FacilityId = facList.FirstOrDefault().FacilityId;
    item.FacilityName = facList.FirstOrDefault().FacilityName;
    //db.zFacility.Attach(item);
    zrequest.zFacility.Add(item);
}
zrequestRepository.InsertOrUpdate(zrequest);
zrequestRepository.Save();

我做了一些研究并尝试通过注释行将每个设施附加到数据库，但这给了我另一个错误，因为相同类型的另一个实体已经具有相同的主键值

这是来自 zRequestRepository 的代码:

public void InsertOrUpdate(zRequest zrequest)
    {
        if (zrequest.RequestId == default(int)) {
            // New entity
            context.zRequests.Add(zrequest);
        } else {
            // Existing entity
            context.Entry(zrequest).State = System.Data.Entity.EntityState.Modified;
        }
    }

我该怎么做才能解决这个问题？让我知道是否需要提供更多信息...

编辑，按要求提供相关模型。z设施:

using System;
using System.Collections.Generic;

public partial class zFacility
{
    public zFacility()
    {
        this.zRequest = new HashSet<zRequest>();
        this.zRoom = new HashSet<zRoom>();
    }

    public short FacilityId { get; set; }
    public string FacilityName { get; set; }

    public virtual ICollection<zRequest> zRequest { get; set; }
    public virtual ICollection<zRoom> zRoom { get; set; }
}

z请求:

using System;
using System.Collections.Generic;

public partial class zRequest
{
    public zRequest()
    {
        this.zFacility = new HashSet<zFacility>();
        this.zRoom = new HashSet<zRoom>();
    }

    public int RequestId { get; set; }
    public string ModCode { get; set; }
    public short StatusId { get; set; }
    public int WeekId { get; set; }
    public short DayId { get; set; }
    public short PeriodId { get; set; }
    public short SessionLength { get; set; }
    public short Semester { get; set; }
    public short RoundNo { get; set; }
    public string SpecialRequirement { get; set; }
    public short UserId { get; set; }

    public virtual zDay zDay { get; set; }
    public virtual zPeriod zPeriod { get; set; }
    public virtual zRound zRound { get; set; }
    public virtual zStatus zStatus { get; set; }
    public virtual zWeek zWeek { get; set; }
    public virtual ICollection<zFacility> zFacility { get; set; }
    public virtual ICollection<zRoom> zRoom { get; set; }
    public virtual zUser zUser { get; set; }
}

这些都是通过数据库优先生成的。

Answer 1

默认情况下，EF 假设所选设施 (zFacility) 是新的并将它们插入到数据库中。

为避免这种情况，您需要将 zFacility

的状态更改为 Unchanged

像这样

public void InsertOrUpdate(zRequest zrequest)
    {
        if (zrequest.RequestId == default(int)) {
        context.zFacility.Attach(zrequest); // state Unchanged for zFacility  
        // New entity
       context.zRequests.Add(zrequest);
            } else {
            // Existing entity
            context.Entry(zrequest).State = System.Data.Entity.EntityState.Modified;
        }
    }