Self Relationship in EF

Question

I’m having the following problem creating two self relationships.

When rotating the Update-Database The following error message appears:

Unable to determine the main end of an Association between the types 'Self-relation.Franchise' and 'Self-relation.Franchise'. The main end of this Association must be explicitly configured using either the Relationship Fluent API or data Annotations.

My Class

public class Franquia
    {
        [Key]
        public int FranquiaId { get; set; }

        public int? FranquiaMasterId { get; set; }

        public int? FranquiaSeniorId { get; set; }

        [ForeignKey("FranquiaMasterId")]
        public virtual Franquia FranquiaMaster { get; set; }

        [ForeignKey("FranquiaSeniorId")]
        public virtual Franquia FranquiaSenior { get; set; }
    }

Detail, when running only one self relationship works perfectly, however with both appears error.

Answer 1

Following his line of reasoning, a Franquia may have a franchise list, and that’s exactly what’s missing when you use it DataAnnotations.

For this, you should also use the annotation InverseProperty to perform the appropriate mapping of navigation properties.

Your code will look like this:

public class Franquia
    {
        [Key]
        public int FranquiaId { get; set; }

        public int? FranquiaMasterId { get; set; }

        public int? FranquiaSeniorId { get; set; }

        [ForeignKey("FranquiaMasterId")]
        public virtual Franquia FranquiaMaster { get; set; }

        [ForeignKey("FranquiaSeniorId")]
        public virtual Franquia FranquiaSenior { get; set; }

        [InverseProperty("FranquiaMaster")]
        public ICollection<Franquia> FranquiasMasters { get; set; }

        [InverseProperty("FranquiaSenior")]
        public ICollection<Franquia> FranquiasSeniors { get; set; }
    }

There is the possibility to make Fluent API, that would look this way:

modelBuilder.Entity<Franquia>()
    .HasOptional(s => s.FranquiaMaster)
    .WithMany(s => s.FranquiasMasters)
    .HasForeignKey(s => s.FranquiaMasterId);

modelBuilder.Entity<Franquia>()
    .HasOptional(s => s.FranquiaSenior)
    .WithMany(s => s.FranquiasSeniors)
    .HasForeignKey(s => s.FranquiaSeniorId);

If using Migrations and whenever the table is created, and no change, the generated Migration will have this code:

public partial class addFranquias : DbMigration
{
    public override void Up()
    {
        CreateTable(
            "dbo.Franquias",
            c => new
                {
                    FranquiaId = c.Int(nullable: false, identity: true),
                    FranquiaMasterId = c.Int(),
                    FranquiaSeniorId = c.Int(),
                })
            .PrimaryKey(t => t.FranquiaId)
            .ForeignKey("dbo.Franquias", t => t.FranquiaMasterId)
            .ForeignKey("dbo.Franquias", t => t.FranquiaSeniorId)
            .Index(t => t.FranquiaMasterId)
            .Index(t => t.FranquiaSeniorId);

    }

    public override void Down()
    {
        DropForeignKey("dbo.Franquias", "FranquiaSeniorId", "dbo.Franquias");
        DropForeignKey("dbo.Franquias", "FranquiaMasterId", "dbo.Franquias");
        DropIndex("dbo.Franquias", new[] { "FranquiaSeniorId" });
        DropIndex("dbo.Franquias", new[] { "FranquiaMasterId" });
        DropTable("dbo.Franquias");
    }
}

And to top it off, you’ll have the following SQL which will be executed at the bank:

CREATE TABLE [dbo].[Franquias] (
    [FranquiaId] [int] NOT NULL IDENTITY,
    [FranquiaMasterId] [int],
    [FranquiaSeniorId] [int],
    CONSTRAINT [PK_dbo.Franquias] PRIMARY KEY ([FranquiaId])
)
CREATE INDEX [IX_FranquiaMasterId] ON [dbo].[Franquias]([FranquiaMasterId])
CREATE INDEX [IX_FranquiaSeniorId] ON [dbo].[Franquias]([FranquiaSeniorId])
ALTER TABLE [dbo].[Franquias] ADD CONSTRAINT [FK_dbo.Franquias_dbo.Franquias_FranquiaMasterId] FOREIGN KEY ([FranquiaMasterId]) REFERENCES [dbo].[Franquias] ([FranquiaId])
ALTER TABLE [dbo].[Franquias] ADD CONSTRAINT [FK_dbo.Franquias_dbo.Franquias_FranquiaSeniorId] FOREIGN KEY ([FranquiaSeniorId]) REFERENCES [dbo].[Franquias] ([FranquiaId])