EF Core: Define foreign key for part of a composite PK using a chained property

Question

I have the following model:

internal class SchemaEfEntity
{
    [Required]
    [MaxLength(128)]
    public string Name { get; set; }

    [Required]
    public DatabaseEfEntity Database { get; set; }
}

internal class DatabaseEfEntity
{
    [Required]
    public string Name { get; set; }

    [Required]
    public InstanceEfEntity Instance { get; set; }

    public ICollection<SchemaEfEntity> Schemas { get; set; }
}

internal class InstanceEfEntity : IEfIdEntity
{
    public long Id { get; set; }

    [Required]
    public string Name { get; set; }

    public ICollection<DatabaseEfEntity> Databases { get; set; }
}

public sealed class MyDbContext : DbContext
{
        internal DbSet<InstanceEfEntity> Instances { get; set; }
        internal DbSet<DatabaseEfEntity> Databases { get; set; }
        internal DbSet<SchemaEfEntity> Schemas { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<InstanceEfEntity>().HasKey(x => x.Id);
            modelBuilder.Entity<InstanceEfEntity>().HasIndex(x => x.Name).IsUnique();

            modelBuilder.Entity<DatabaseEfEntity>().HasKey(
                $"{nameof(DatabaseEfEntity.Instance)}{nameof(InstanceEfEntity.Id)}",
                $"{nameof(DatabaseEfEntity.Name)}");

            modelBuilder.Entity<SchemaEfEntity>().HasKey(
                $"{nameof(SchemaEfEntity.Database)}{nameof(DatabaseEfEntity.Instance)}{nameof(InstanceEfEntity.Id)}",
                $"{nameof(SchemaEfEntity.Database)}{nameof(DatabaseEfEntity.Name)}",
                $"{nameof(SchemaEfEntity.Name)}");
        }
}

The SchemaEfEntity has a composite PK defined that consists of (InstanceId, DatabaseName and SchemaName).

I'd like to define a foreign key from schema.InstanceId to instance.Id. I've tried this:

        modelBuilder.Entity<SchemaEfEntity>()
            .HasOne(c => c.Database.Instance)
            .WithMany()
            .HasForeignKey(
                $"{nameof(SchemaEfEntity.Database)}{nameof(DatabaseEfEntity.Instance)}{nameof(InstanceEfEntity.Id)}"
            );

But getting the below error when creating a migration:

The expression 'c => c.Database.Instance' is not a valid property expression. The expression should represent a simple property access: 't => t.MyProperty'. Parameter name: propertyAccessExpression

Any ideas? Is it possible at all? Is there a way to specify it "by hand", i.e. not using the properties chain but just specifying the table and a column as a plain text?

Answer 1

It can be done using the following syntax:

modelBuilder.Entity<SchemaEfEntity>()
    .HasOne(typeof(InstanceEfEntity))
    .WithMany()
    .HasForeignKey(
        $"{nameof(SchemaEfEntity.Database)}{nameof(DatabaseEfEntity.Instance)}{nameof(InstanceEfEntity.Id)}")
    .OnDelete(DeleteBehavior.Restrict);