使用多对多关系保存时违反外键

本文关键字:保存 关系 | 更新日期: 2023-09-27 18:12:38

我有三个表:

CREATE TABLE [dbo].[Committees] (
    [committee_id]          INT             IDENTITY (1, 1) NOT NULL,
    [Committee_name]        NVARCHAR (128)  NULL,
    [Committee_email]       NVARCHAR (128)  NULL,
    [Committee_inactive]    INT             NULL,
    [Committee_type]        NVARCHAR (50)   NULL,
    [Committee_description] NVARCHAR (1024) NULL,
    [Committee_chair_id]    INT             NOT NULL,
    [Committee_sponsor_id]  INT             NOT NULL,
    [Committee_end_date]    DATETIME2 (7)   NULL,
    [bMembershipOpen]       BIT             CONSTRAINT [DF_Committees_bMembershipOpen] DEFAULT ((0)) NOT NULL,
    CONSTRAINT [PK_Committees] PRIMARY KEY CLUSTERED ([committee_id] ASC),
    CONSTRAINT [FK_CommitteesChair_ToPersons] FOREIGN KEY ([Committee_chair_id]) REFERENCES [dbo].[Persons] ([Id]),
    CONSTRAINT [FK_CommitteesSponsor_ToPersons] FOREIGN KEY ([Committee_sponsor_id]) REFERENCES [dbo].[Persons] ([Id])
);
CREATE TABLE [dbo].[Persons] (
    [Id]    INT            IDENTITY (1, 1) NOT NULL,
    [Name]  NVARCHAR (128) NULL,
    [Email] NVARCHAR (128) NULL,
    CONSTRAINT [PK_Persons] PRIMARY KEY CLUSTERED ([Id] ASC)
);

CREATE TABLE [dbo].[CommitteeMembers] (
    [CommitteeId] INT NOT NULL,
    [PersonId]    INT NOT NULL,
    PRIMARY KEY CLUSTERED ([CommitteeId] ASC, [PersonId] ASC),
    CONSTRAINT [FK_CommitteeMembers_ToCommittee] FOREIGN KEY ([CommitteeId]) REFERENCES [dbo].[Committees] ([committee_id]),
    CONSTRAINT [FK_CommitteeMembers_ToPerson] FOREIGN KEY ([PersonId]) REFERENCES [dbo].[Persons] ([Id])
);

我有两个与委员会和人员相关的类

 public class Committee {
        [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
        [Column("committee_id")]
        [Key]
        public int Id { get; set; }
        [Column("Committee_chair_id")]
        [ForeignKey("Chairman")]
        [Required(ErrorMessage = "Committee must have a Chairman")]
        public int ChairmanId { get; set; }
        [Column("Committee_sponsor_id")]
        [ForeignKey("Sponsor")]
        [Required(ErrorMessage = "Committee must have a Sponsor")]
        public int SponsorId { get; set; }
        [Column("Committee_name")]
        [MinLength(3, ErrorMessage = "Committee Name must be at least 3 characters long")]
        [MaxLength(128, ErrorMessage = "Committee Name cannot be longer than 128 characters")]
        [Required(ErrorMessage = "Committee must have a name")]
        public string Name { get; set; }
        [Column("Committee_email")]
        [EmailAddress]
        [Required(ErrorMessage = "Committee must have an Email Address")]
        public string Email { get; set; }
        [Column("Committee_inactive")]
        public int? Inactive { get; set; }
        [Column("Committee_type")]
        [Required(ErrorMessage = "Committee must have a Type")]
        public string Type { get; set; }
        [Column("Committee_description")]
        [MaxLength(1000, ErrorMessage = "Committee Name cannot be longer than 1000 characters")]
        public string Description { get; set; }
        [Column("Committee_end_date")]
        public DateTime? EndDate { get; set; }
        [Column("bMembershipOpen")]
        [DefaultValue(false)]
        [Display(Name="Membership Open")]
        [Required]
        public bool MembershipOpen { get; set; }
        public Person Chairman { get; set; }
        public Person Sponsor { get; set; }
        public virtual ICollection<Person> Members { get; set; }
        public virtual ICollection<Note> Notes { get; set; }  
        public virtual ICollection<NextStep> NextSteps { get; set; } 
        public virtual ICollection<Outcome> Outcomes { get; set; } 
        public virtual ICollection<Video> Videos { get; set; } 
    }

public class Person {
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    [Required(ErrorMessage = "You must specify a name")]
    [MaxLength(128, ErrorMessage = "Name cannot be longer than 128 characters")]
    public string Name { get; set; }
    [EmailAddress]
    [MaxLength(128, ErrorMessage = "Email Address cannot be longer than 128 characters")]
    public string Email { get; set; }
    public virtual ICollection<Committee> Committees { get; set; } 
}

和我有以下在我的DbContext

protected override void OnModelCreating(DbModelBuilder modelBuilder) {base.OnModelCreating (modelBuilder);

    modelBuilder.Entity<Committee>()
        .HasMany(c => c.Members)
        .WithMany(m => m.Committees)
        .Map(m=> {
            m.ToTable("CommitteeMembers");
            m.MapLeftKey("PersonId");
            m.MapRightKey("CommitteeId");
        });
    modelBuilder.Entity<Committee>()
        .HasMany(c => c.Notes)
        .WithRequired(n => n.Committee);
    modelBuilder.Entity<Committee>()
        .HasMany(c => c.NextSteps)
        .WithRequired(n => n.Committee);
    modelBuilder.Entity<Committee>()
        .HasMany(c => c.Outcomes)
        .WithRequired(o => o.Committee);
    modelBuilder.Entity<Committee>()
        .HasMany(c => c.Videos)
        .WithRequired(v => v.Committee);
    modelBuilder.Entity<Committee>()
        .HasRequired(c => c.Chairman);
    modelBuilder.Entity<Committee>()
        .HasRequired(c => c.Sponsor);
    modelBuilder.Entity<Person>()
        .Map(t => t.ToTable("Persons"));
}

所有的导航属性都可以在模型上工作,除了Committee。成员和人事委员会。每当我尝试在将Person添加到Committee或将Committee添加到Person后保存DbContext时,我都会得到如下错误:

INSERT语句与FOREIGN KEY约束冲突"FK_CommitteeMembers_ToCommittee"。数据库发生冲突"EquipNetCommitteeDb",表"dbo"。"Committees",列"committee_id"。语句已被终止。

有人知道我做错了什么吗?这应该行得通。我以前已经做过很多次了,我想我只是需要另一个人来关注一下。

使用多对多关系保存时违反外键

所以看起来Entity Framework不喜欢我已经在committemembers表中创建了外键。一旦我删除了这些并让实体框架管理它的工作关系。我不确定这是否应该是如何工作的。如果有人知道为什么我不能在表定义中指定外键,我很想知道答案。