"Ghost" shadow-key in EF Core fluent api

Question

I have this setup (roughly, I've edited out the irrelevant code for readability).

public class LevelSection
{
    public Guid Id { get; set; }
    public Guid UserDataId { get; set; }
}

and then

public class UserData
{
    public Guid Id { get; set; }
    public LevelSection SkillsSection { get; set; }
    public LevelSection LanguageSection { get; set; }
    public IList<LevelSection> CustomLevelSections { get; set; }
}

And I am using the code-first approach in EF core. I want these two tables to be "connected" so that the UserData table has only its Id, and then to have LevelSection table which would have the UserDataId as a foreign key.

I've tried many fluent api configurations in OnModelCreating, and many of them were giving me different errors (mostly along the lines of - there is already defined relationship between these two). However, I managed to find something that looked like what i wanted to achieve:

modelBuilder.Entity<UserData>()
      .HasMany(a => a.CustomLevelSections)
      .WithOne()
      .HasForeignKey(c => c.UserDataId)
      .OnDelete(DeleteBehavior.Cascade);

modelBuilder.Entity<UserData>()
      .HasOne(a => a.SkillsSection)
      .WithOne()
      .HasForeignKey<LevelSection>(e => e.UserDataId)
      .OnDelete(DeleteBehavior.Cascade);

modelBuilder.Entity<UserData>()
      .HasOne(a => a.LanguageSection)
      .WithOne()
      .HasForeignKey<LevelSection>(e => e.UserDataId)
      .OnDelete(DeleteBehavior.Cascade);

But now I am getting a shadow property on UserDataId - The foreign key property 'LevelSection.UserDataId1' was created in shadow state because a conflicting property with the simple name 'UserDataId' exists in the entity type, but is either not mapped, is already used for another relationship, or is incompatible with the associated primary key type. See https://aka.ms/efcore-relationships for information on mapping relationships in EF Core.

Above are all of the occurrences of the LevelSection objects in UserData so I wasn't expecting this. And can't seem to figure out where does this come from. Alternate approaches to this, with same results are also very welcome (or any other ideas at this point).

Answer 1

As mentioned in the comments, the FK can only serve one relationship. You are linking 2x 1-to-1 relationships and one 1-to-many relationship between the tables. Any way you cut that, it is 3x FKs.

public class UserData
{
    public Guid Id { get; set; }
    [ForeignKey(nameof(SkillSection)]
    public Guid SkillLevelSectionId { get; set; }
    public LevelSection SkillsSection { get; set; }
    [ForeignKey(nameof(LanguageSection)]
    public Guid LanguageLevelSectionId { get; set; }
    public LevelSection LanguageSection { get; set; }
    public IList<LevelSection> CustomLevelSections { get; set; }
}

Those 1-to-1 FKs can exist on either the UserData or the linking table, but it would probably make more sense on the UserData. On the LevelData table it would be something like UserDataIdIAmALanguageSectionFor and UserDataIdIAmASkillSectionFor to be able to tell them apart rather than UserDataId1 and UserDataId2 alongside UserDataId.

The alternative is to express the Skill and Language sections as separate tables. Trying to put data that is merely "similar" in a single table can backfire. It's rarely a good idea in modern data systems where storage space is cheap given it is faster to index / read 200k records split across 3 tables with unique indexes than trying to read from 200k records in a single table where you have to make compromises.