EF Core Many-to-Many self join

Question

I am trying to describe a many-to-many self-referential relationship to Entity Framework Core 2. Essentially what I'm trying to model is a sort of tree structure, in which each element can have an arbitrary number of parent and child elements (so I guess more a graph than a tree). Here's what I have so far:

public class OrgLevel
{
    ...

    public ICollection<OrgLevelOrgLevels> OrgLevelOrgLevelsAsParent { get; set; }

    public ICollection<OrgLevelOrgLevels> OrgLevelOrgLevelsAsChild { get; set; }

    public ICollection<OrgLevel> ParentOrganizationStructureLevels { get; set; }

    public ICollection<OrgLevel> ChildOrganizationStructureLevels { get; set; }
}

public class OrgLevelOrgLevels
{
    public OrgLevel ParentOrgLevel { get; set; }
    public OrgLevel ChildOrgLevel { get; set; }
}

public class ApplicationDbContext : IdentityDbContext<ApplicationUser, ApplicationRole, string>
{
    ...

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        builder.Entity<OrgLevelOrgLevels>()
            .HasKey(t => new { t.ParentOrgLevel, t.ChildOrgLevel });

        builder.Entity<OrgLevelOrgLevels>().HasOne(olol => olol.ChildOrgLevel)
            .WithMany(col => col.OrgLevelOrgLevelsAsChild);

        builder.Entity<OrgLevelOrgLevels>().HasOne(olol => olol.ParentOrgLevel)
            .WithMany(pol => pol.OrgLevelOrgLevelsAsParent);
    }
}

When i try to generate the initial migration, i get the following:

The navigation property 'ChildOrgLevel' cannot be added to the entity type 'OrgLevelOrgLevels' because a property with the same name already exists on entity type 'OrgLevelOrgLevels'.

I am assuming this means that for the join table, it's trying to name both columns the same thing as they reference the same table.

Additionally, I don't really have any idea how to wire the last two navigation properties in the OrgLevel model so that they will use the join table to resolve.

Any help would be appreciated!

Answer 1

The main issue is the following fluent configuration:

builder.Entity<OrgLevelOrgLevels>()
    .HasKey(t => new { t.ParentOrgLevel, t.ChildOrgLevel });

The error message is kind of misleading. This overload expects primitive properties (either explicit or shadow), but you are passing navigation properties.

There are several ways to resolve it.

First, add explicit FK properties to the model (assuming referenced PK property type is int):

public class OrgLevelOrgLevels
{
    public int ParentOrgLevelId { get; set; }
    public OrgLevel ParentOrgLevel { get; set; }
    public int ChildOrgLevelId { get; set; }
    public OrgLevel ChildOrgLevel { get; set; }
}

and use

builder.Entity<OrgLevelOrgLevels>()
    .HasKey(t => new { t.ParentOrgLevelId, t.ChildOrgLevelId });

Another way is to keep the model as is, but use another HasKey overload and pass shadow property names after they are defined:

builder.Entity<OrgLevelOrgLevels>().HasOne(olol => olol.ChildOrgLevel)
    .WithMany(col => col.OrgLevelOrgLevelsAsChild)
    .OnDelete(DeleteBehavior.Restrict);

builder.Entity<OrgLevelOrgLevels>().HasOne(olol => olol.ParentOrgLevel)
    .WithMany(pol => pol.OrgLevelOrgLevelsAsParent);

builder.Entity<OrgLevelOrgLevels>()
     .HasKey("ParentOrgLevelId", "ChildOrgLevelId");

Of course you can define them explicitly in advance:

builder.Entity<OrgLevelOrgLevels>()
    .Property<int>("ParentOrgLevelId");

builder.Entity<OrgLevelOrgLevels>()
    .Property<int>("ChildOrgLevelId");

builder.Entity<OrgLevelOrgLevels>()
     .HasKey("ParentOrgLevelId", "ChildOrgLevelId");

builder.Entity<OrgLevelOrgLevels>().HasOne(olol => olol.ChildOrgLevel)
    .WithMany(col => col.OrgLevelOrgLevelsAsChild)
    .OnDelete(DeleteBehavior.Restrict);

builder.Entity<OrgLevelOrgLevels>().HasOne(olol => olol.ParentOrgLevel)
    .WithMany(pol => pol.OrgLevelOrgLevelsAsParent);

Note that this model introduces multiple cascade paths, so you need to turn cascade delete off at least of one of the two relationships and handle it manually.