Using Composite Keys with Entity Framework Core and Using part of them as foreign keys

Question

I am trying to make several tables that have composite keys (example below). I have been able to acheive this using a code first approach but I want these composite keys to sort of cascade down to child tables. The idea is that each child entity would have the same composite keys as it's parent plus one more column.

| PurchaseOrder |
| ------------- |
| Company (PK)  |
| PONum (PK)    |

| PuchaseOrderLine |
| ---------------- |
| Company (PK/FK)  |
| PONum (PK/FK)    |
| POLine (PK)      |

Technically the PurchaseOrder table would do a similar thing to a company table but that is slightly less important to me and I think if I am able to figure out the POLine to PO connection I will figure that one out too.

This is my attempt so far:

// OnModelCreating method in my class that inherits IdentityDbContext<IdentityUser> 
protected override void OnModelCreating(ModelBuilder builder)
{
      base.OnModelCreating(builder);

      builder.Entity<Company>()
          .DefaultConfigure(c => new { c.Name });

      builder.Entity<PurchaseOrder>()
          .DefaultConfigure(p => new { p.Company, p.PONum })
          .HasMany(e => e.PurchaseOrderLines)
          .WithOne(e => e.PurchaseOrder);

      builder.Entity<PurchaseOrderLine>()
          .DefaultConfigure(p => new { p.Company, p.PONum, p.LineNum })
          .HasOne(e => e.PurchaseOrder)
          .WithMany(e => e.PurchaseOrderLines);
}

// My DefaultConfigure extension method
public static EntityTypeBuilder<T> DefaultConfigure<T>(this EntityTypeBuilder<T> builder, Expression<Func<T, object>> keyExpression)
    where T : AuditableEntity
{
    // Rename table to the types name
    builder.ToTable(typeof(T).Name.ToLower());

    // Configure the keys they passed in
    builder.HasKey(keyExpression);

    // Configure the default alternate key
    builder.HasAlternateKey(i => new { i.RowId });

    // Give the builder back
    return builder;
}

When I ran this migration and updated my database this is what my PurchaseOrderLine table had on it:

| COLUMN_NAME          | CONSTRAINT_NAME                                                            |
| -------------------- | -------------------------------------------------------------------------- |
| RowId                | AK_PurchaseOrderLine_RowId                                                 |
| PurchaseOrderCompany | FK_PurchaseOrderLine_PurchaseOrder_PurchaseOrderCompany_PurchaseOrderPONum |
| PurchaseOrderPONum   | FK_PurchaseOrderLine_PurchaseOrder_PurchaseOrderCompany_PurchaseOrderPONum |
| Company              | PK_PurchaseOrderLine                                                       |
| LineNum              | PK_PurchaseOrderLine                                                       |
| PONum                | PK_PurchaseOrderLine                                                       |

EFCore just added two new columns using the default naming scheme and didn't use the columns I already had. Is there anyway to get efcore to do something like this using the code first approach?

Answer 1

Answering your concrete question

Is there anyway to get efcore to do something like this using the code first approach?

Sure there is. Just the conventional foreign key names apparently don't work, so you have to configure the FK properties explicitly (via HasForeignKey fluent API).

e.g. either

builder.Entity<PurchaseOrderLine>()
    .DefaultConfigure(p => new { p.Company, p.PONum, p.LineNum })
    .HasOne(e => e.PurchaseOrder)
    .WithMany(e => e.PurchaseOrderLines)
    .HasForeignKey(e => { e.Company, e.PONum }); // <--

or

builder.Entity<PurchaseOrder>()
    .DefaultConfigure(p => new { p.Company, p.PONum })
    .HasMany(e => e.PurchaseOrderLines)
    .WithOne(e => e.PurchaseOrder)
    .HasForeignKey(e => { e.Company, e.PONum }); // <--

Note that both Has / With pairs represent one and the same relationship, so it's better to do it only in one place in order to avoid conflicting configurations.