How can a Foreign Key refer to a property on an Entity Splitting child table?

Question

I am creating a new IdentityDbContext based on an existing customized database. Instead of adding custom properties to AspNetUsers, it was decided years ago to add them to another table, CustomUser, along with its own identity column, UserId. UserId is then used as the foreign key to CustomUserAccept, which records when a user accepted the terms of service, cookie policy, etc.

public void CreateCustomUser(EntityTypeBuilder<CustomUser> entity)
{
    entity
        .ToTable("AspNetUsers")
        .SplitToTable("CustomUser", CreateCustomUser);

    entity.HasAlternateKey(e => e.UserId);

    entity.HasMany(d => d.Accepts).WithMany()
        .UsingEntity<CustomUserAccept>(
            "CustomUserAccept",
            r => r.HasOne(d => d.Accept).WithMany()
                .HasForeignKey(d => d.AcceptId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_CustomUserAccept_AcceptId"),
            l => l.HasOne(d => d.User).WithMany()
                .HasPrincipalKey(d => d.UserId)
                .HasForeignKey(d => d.UserId)
                .OnDelete(DeleteBehavior.ClientSetNull)
                .HasConstraintName("FK_CustomUserAccept_UserId"),
            CreateCustomUserAccept);
}

public void CreateCustomUser(SplitTableBuilder<CustomUser> entity)
{
    entity.Property(e => e.UserId).UseIdentityColumn();
    entity.Property(e => e.Id).HasColumnName("IdentityId");
    
    entity.Property(e => e.CompanyId);
    entity.Property(e => e.FirstName);
    entity.Property(e => e.LastName);
    entity.Property(e => e.LastLogonDate);
    entity.Property(e => e.PasswordExpirationDate);
    entity.Property(e => e.ChangePassword);
}

public void CreateCustomUserAccept(EntityTypeBuilder<CustomUserAccept> entity)
{
    entity.HasKey(e => new { e.UserId, e.AcceptId });

    entity.ToTable("CustomUserAccept");

    entity.Property(e => e.CreatedDate)
        .HasDefaultValueSql("(getutcdate())")
        .HasColumnType("datetime");
}

I used Entity Splitting to combine AspNetUsers and SecureUser into one SecureUser model. It worked great... until I added the many to many reference to CustomAccept.

'Microsoft.EntityFrameworkCore.Model.Validation.ForeignKeyPropertiesMappedToUnrelatedTables': The foreign key {'UserId'} on the entity type 'CustomUserAccept (CustomUserAccept)' targeting 'CustomUser' cannot be represented in the database. Either the properties {'UserId'} aren't mapped to table 'CustomUserAccept', or the principal properties {'UserId'} aren't mapped to table 'AspNetUsers'. All foreign key properties must map to the table to which the dependent type is mapped, and all principal properties must map to a single table to which the principal type is mapped.

Is there any way around the "principal properties must map to a single table" maybe by manipulating the Metadata or Overrides? Is there anything I can do other than abandon Entity Splitting?

Answer 1

I'm open to other ideas, but when I flipped CustomUser to the primary table, it worked and I was able to log in.

    public void CreateCustomUser(EntityTypeBuilder<CustomUser> entity)
    {
        entity
            .ToTable("CustomUser")
            .SplitToTable("AspNetUsers", CreateCustomUser);

        entity.Property(e => e.Id).HasColumnName("IdentityId");
        entity.HasAlternateKey(e => e.UserId);

        entity.HasMany(d => d.Accepts).WithMany()
            .UsingEntity<CustomUserAccept>(
                "CustomUserAccept",
                r => r.HasOne(d => d.Accept).WithMany()
                    .HasForeignKey(d => d.AcceptId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_CustomUserAccept_AcceptId"),
                l => l.HasOne(d => d.User).WithMany()
                    .HasPrincipalKey(d => d.UserId)
                    .HasForeignKey(d => d.UserId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_CustomUserAccept_UserId"),
                CreateCustomUserAccept);
    }

    public void CreateCustomUser(SplitTableBuilder<CustomUser> entity)
    {
        entity.Property(e => e.Id).HasColumnName("Id");
        entity.Property(e => e.UserName);
        entity.Property(e => e.NormalizedUserName);
        entity.Property(e => e.Email);
        entity.Property(e => e.NormalizedEmail);
        entity.Property(e => e.EmailConfirmed);
        entity.Property(e => e.PasswordHash);
        entity.Property(e => e.SecurityStamp);
        entity.Property(e => e.ConcurrencyStamp);
        entity.Property(e => e.PhoneNumber);
        entity.Property(e => e.PhoneNumberConfirmed);
        entity.Property(e => e.TwoFactorEnabled);
        entity.Property(e => e.LockoutEnd);
        entity.Property(e => e.LockoutEnabled);
        entity.Property(e => e.AccessFailedCount);
    }