I have an Organization entity table
public class Organization
{
public int OrganizationId { get; set; }
public string Name { get; set; }
public int OrganizationTypeId { get; set; }
public OrganizationType OrganizationType { get; set; }
public ICollection<OrganizationRelation> OrganizationRelations { get; set; }
}
then I have my relational table with a self-referencing parent column
public class OrganizationRelation
{
public int OrganizationRelationId { get; set; }
public int OrganizationId { get; set; }
public int? ParentOrganizationId { get; set; }
public Organization Organization { get; set; }
public Organization ParentOrganization { get; set; }
}
public class OrganizationRelationModelConfiguration : IEntityTypeConfiguration<OrganizationRelation>
{
public void Configure(EntityTypeBuilder<OrganizationRelation> builder)
{
builder.HasKey(c => c.OrganizationRelationId);
builder.Property(c => c.OrganizationRelationId).ValueGeneratedOnAdd();
builder.Property(c => c.OrganizationId).IsRequired();
builder.Property(c => c.ParentOrganizationId);
builder.HasOne(r => r.Organization).WithMany().HasForeignKey(fk => fk.OrganizationId);
builder.HasOne(r => r.ParentOrganization).WithMany().HasForeignKey(fk => fk.ParentOrganizationId);
builder.ToTable("OrganizationRelation", "dbo");
}
}
When I deploy my db with migration, I see this table created:
CREATE TABLE [mdo].[OrganizationRelation](
[OrganizationRelationId] [int] IDENTITY(1,1) NOT NULL,
[OrganizationId] [int] NOT NULL,
[ParentOrganizationId] [int] NULL,
[OrganizationId1] [int] NULL,
CONSTRAINT [PK_OrganizationRelation] PRIMARY KEY CLUSTERED
(
[OrganizationRelationId] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
I'm using EF 5.0 I don't get it why is creating the column OrganizationId1
You didn't map OrganizationRelation.ParentOrganization
to Organization.OrganizationRelations
, so EF is adding an additional FK to OrganizationRelation
for the second navigation property.
But that model seems overly complex. Why not just
public class Organization
{
public int OrganizationId { get; set; }
public string Name { get; set; }
public int OrganizationTypeId { get; set; }
public int? ParentOrganizationId { get; set; }
public Organization ParentOrganization { get; set; }
public ICollection<Organization> ChildOrganizations{ get; } = new HashSet<Organization>();
}
which creates
CREATE TABLE [Organization] (
[OrganizationId] int NOT NULL IDENTITY,
[Name] nvarchar(max) NULL,
[OrganizationTypeId] int NOT NULL,
[ParentOrganizationId] int NULL,
CONSTRAINT [PK_Organization] PRIMARY KEY ([OrganizationId]),
CONSTRAINT [FK_Organization_Organization_ParentOrganizationId] FOREIGN KEY ([ParentOrganizationId]) REFERENCES [Organization] ([OrganizationId]) ON DELETE NO ACTION
);
CREATE INDEX [IX_Organization_ParentOrganizationId] ON [Organization] ([ParentOrganizationId]);
?
To factor a foreign key out into a separate table, you just introduce a 1-1 dependent table, where the dependent table's FK and PK are the same as the main table's PK.
So to do the same thing with a separate entity would look like this:
public class Organization
{
public int OrganizationId { get; set; }
public string Name { get; set; }
public int OrganizationTypeId { get; set; }
public OrganizationRelation OrganizationRelation { get; set; }
public ICollection<OrganizationRelation> ChildOrganizations { get; } = new HashSet<OrganizationRelation>();
}
public class OrganizationRelation
{
public int OrganizationId { get; set; }
public int ParentOrganizationId { get; set; }
public Organization Organization { get; set; }
public Organization ParentOrganization { get; set; }
}
protected override void OnModelCreating(ModelBuilder builder)
{
base.OnModelCreating(builder);
builder.Entity<OrganizationRelation>().HasKey(c => c.OrganizationId);
builder.Entity<OrganizationRelation>()
.HasOne(r => r.Organization)
.WithOne(o => o.OrganizationRelation)
.HasForeignKey(nameof(OrganizationRelation), nameof(OrganizationRelation.OrganizationId));
builder.Entity<OrganizationRelation>()
.HasOne(r => r.ParentOrganization)
.WithMany(o => o.ChildOrganizations)
.HasForeignKey(r => r.ParentOrganizationId)
.OnDelete(DeleteBehavior.Restrict);
builder.Entity<OrganizationRelation>().ToTable("OrganizationRelation", "dbo");
base.OnModelCreating(builder);
}