CODEWITHSUNDEEP
c#sqlentity-frameworkef-code-firstef-core-3.1
JDB
JDB

Reputation: 77

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

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?

Upvotes: 4

Views: 8013

Answers (2)

Ivan Stoev
Ivan Stoev

Reputation: 205849

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.

Upvotes: 3

Gordon Linoff
Gordon Linoff

Reputation: 1271003

Don't do this. Use auto-incremented primary keys. Look up the values that you need in the parent tables.

Composite primary keys just make foreign keys cumbersome. They are also less efficient and generally use more space, if widely used as foreign keys.

So, PurchaseOrder should have a PurchaseOrderId. Then PurchaseOrderLine should reference PurchaseOrderId and look up the relevant information, such as the company, when that information is needed.

Upvotes: 1

Related Questions