EF在同一主键上有多个外键关系

时间:2021-12-04 11:29:15

I want to create a many-to-many relationship using EF 6 using a code-first approach. My entities use a composite primary key (to handle multi-tenancy).

我想使用EF 6使用代码优先方法创建多对多关系。我的实体使用复合主键(处理多租户)。

Let's take simple and classical example. I have two entities Project and Person which have a many-to-many relationship:

让我们来看一些简单而经典的例子。我有两个实体Project和Person,它们具有多对多的关系:

public class Person
{
   [Key, Column(Order = 1),]
   public Int32 Id { get; set; } 
   [Key, Column(Order = 2)]
   public int TenantId { get; set; }
   public string Name { get; set; }
}

public class Project
{
   [Key, Column(Order = 1),]
   public Int32 Id { get; set; } 
   [Key, Column(Order = 2)]
   public int TenantId { get; set; }
   Public string Name { get; set; }
}

I also have a joining table ProjectPerson like this:

我还有一个像这样的加入表ProjectPerson:

Above I have defined a Project to ProjectPerson relationship. Note that public class ProjectPerson { [Key, Column(Order = 1),] public Int32 Id { get; set; } [Key, Column(Order = 2)] [ForeignKey("Project")] public int TenantId { get; set; }

上面我已经定义了Project to ProjectPerson关系。注意公共类ProjectPerson {[Key,Column(Order = 1),] public Int32 Id {get;组; } [Key,Column(Order = 2)] [ForeignKey(“Project”)] public int TenantId {get;组; }

   [ForeignKey("Project")]
   public int ProjectId { get; set; }

   public DateTime AddedDate{ get; set; }

   public virtual Project Project { get; set; }
}

TenantId is used as a part of the primary and foreign key.

TenantId用作主键和外键的一部分。

Up to this point, the model works as expected. But the Person to ProjectPerson relationship is missing.

到目前为止,该模型按预期工作。但缺少人与ProjectPerson的关系。

I have added following two lines to the ProjectPerson class

我在ProjectPerson类中添加了以下两行

  [ForeignKey("Person")]
  public int PersonId { get; set; }

  public virtual Person Person { get; set; }

Definitely mapping to TenantId is missing. I don't know how to define it

绝对没有映射到TenantId。我不知道如何定义它

Update

I found this. but still im not satisfied as there is additional TenantId ( PersonTenantId) as a foreign key.

我找到了这个。但仍然不满意,因为有额外的TenantId(PersonTenantId)作为外键。

 public class ProjectPerson
 {
   [Key, Column(Order = 1),]
   public Int32 Id { get; set; } 
   [Key, Column(Order = 2)]
   [ForeignKey("Project")]
   public int TenantId { get; set; }

   [ForeignKey("Project")]
   public int ProjectId { get; set; }

   [ForeignKey("Person")]
   public int PersonId { get; set; }
   [ForeignKey("Person")]
   public int PersonTenantId { get; set; } // duplicate 


   public DateTime AddedDate{ get; set; }

   public virtual Project Project { get; set; }
   public virtual Person Person { get; set; }

}

1 个解决方案

#1


Use the fluent API to reuse the TentantId column for both FK's in the junction table. ProjectId should also be included in the junction table's PK. Note that I modified the order of the composite primary key columns to have TenantId as the first column.

使用fluent API为联结表中的两个FK重用TentantId列。 ProjectId也应该包含在联结表的PK中。请注意,我修改了复合主键列的顺序,以将TenantId作为第一列。

    public class Person
    {
        [Key, Column(Order = 0)]
        public int TenantId { get; set; }
        [Key, Column(Order = 1)]
        public int PersonId { get; set; }

        public string Name { get; set; }

        public virtual ICollection<ProjectPerson> ProjectPeople { get; set; }
    }

    public class Project
    {
        [Key, Column(Order = 0)]
        public int TenantId { get; set; }
        [Key, Column( Order = 1 )]
        public int ProjectId { get; set; }

        public string Name { get; set; }

        public virtual ICollection<ProjectPerson> ProjectPeople { get; set; }
    }

    public class ProjectPerson
    {
        [Key, Column( Order = 0 )]
        public int TentantId { get; set; }
        [Key, Column( Order = 1 )]
        public int ProjectId { get; set; }
        [Key, Column( Order = 2 )]
        public int PersonId { get; set; }

        public DateTime AddedDate { get; set; }

        public virtual Project Project { get; set; }
        public virtual Person Person { get; set; }
    }


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

        modelBuilder.Entity<Project>()
            .HasMany(pr => pr.ProjectPeople )
            .WithRequired( pp => pp.Project )
            .HasForeignKey( pp => new { pp.TentantId, pp.ProjectId } );

        modelBuilder.Entity<Person>()
            .HasMany( pe => pe.ProjectPeople )
            .WithRequired( pp => pp.Person )
            .HasForeignKey( pp => new { pp.TentantId, pp.PersonId } );
    }

#1


Use the fluent API to reuse the TentantId column for both FK's in the junction table. ProjectId should also be included in the junction table's PK. Note that I modified the order of the composite primary key columns to have TenantId as the first column.

使用fluent API为联结表中的两个FK重用TentantId列。 ProjectId也应该包含在联结表的PK中。请注意,我修改了复合主键列的顺序,以将TenantId作为第一列。

    public class Person
    {
        [Key, Column(Order = 0)]
        public int TenantId { get; set; }
        [Key, Column(Order = 1)]
        public int PersonId { get; set; }

        public string Name { get; set; }

        public virtual ICollection<ProjectPerson> ProjectPeople { get; set; }
    }

    public class Project
    {
        [Key, Column(Order = 0)]
        public int TenantId { get; set; }
        [Key, Column( Order = 1 )]
        public int ProjectId { get; set; }

        public string Name { get; set; }

        public virtual ICollection<ProjectPerson> ProjectPeople { get; set; }
    }

    public class ProjectPerson
    {
        [Key, Column( Order = 0 )]
        public int TentantId { get; set; }
        [Key, Column( Order = 1 )]
        public int ProjectId { get; set; }
        [Key, Column( Order = 2 )]
        public int PersonId { get; set; }

        public DateTime AddedDate { get; set; }

        public virtual Project Project { get; set; }
        public virtual Person Person { get; set; }
    }


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

        modelBuilder.Entity<Project>()
            .HasMany(pr => pr.ProjectPeople )
            .WithRequired( pp => pp.Project )
            .HasForeignKey( pp => new { pp.TentantId, pp.ProjectId } );

        modelBuilder.Entity<Person>()
            .HasMany( pe => pe.ProjectPeople )
            .WithRequired( pp => pp.Person )
            .HasForeignKey( pp => new { pp.TentantId, pp.PersonId } );
    }