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?

Ivan Stoev · Accepted Answer · 2020-05-17 18:52:02Z

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.

That is perfect, it is doing exactly what I want it to do. I did exactly what you had in your first example and then only did the .DefaultConfigure() method on my PurchaseOrder Entity Builder and it built the table the exact way I was expecting. Thank you very much.

Gordon Linoff · Accepted Answer · 2020-05-17 18:24:39Z

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.

I know how to use auto-incremented primary keys but for this application they will not due. The information may sit on separate databases for legal reasons and be merged at a later time for auditing. Doing it this way, with each location having a different Company I can still keep things unique, I am also using and alternate key for increased reliability of uniqueness. I understand that this isn't the most efficient route but this is how it needs to be built.

Collectives™ on Stack Overflow

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

2 Answers 2

1 Comment

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Related