EF Code First Single foreign key to multiple parents

Question

Hope you can help

I have 3 (and more to come) tables [Customer] [Order] and [Invoice].

All of these tables have an ID column [ID - Guid].

I have a table [Notes] and it is comprised of 2 columns (for brevity): -

[ID - Guid]
[ParentFKID - Guid]
[Comment - String]

I wish to create a collection of [Notes] on each of the 3 (and more to come) "parent" entities and configure each of these entities to populate the collection from the [Notes] table where the primary key of the parent table in question is pointing to the [ParentFKID] column.

So essentially the column [ParentFKID] is a foreign key, but a foreign key to multiple other tables. This is possible as we are using GUID keys.

This is essentially so we don't have replicated tables of [CustomerNotes] [OrderNotes] and [InvoiceNotes] AND ALSO to avoid the other alternative of having the notes table forever growing with the foreign keys [Customer_ID] [Order_ID] and [Invoice_ID]

Surely there must be a way to cater for this with a Fluent API mapping but as I'm so new to EF Code First and the Fluent API then I'm struggling to see it.

I don't care that the constraints may be unavailable - this is preferable anyway and seemingly impossible with the structure. This surely is a common scenario.

Can anyone help and provide an example of how to configure the entities for the model to enable this scenario ?

Answer 1

You can try this mapping with Fluent API:

modelBuilder.Entity<Customer>()
    .HasMany(c => c.Notes)
    .WithRequired()
    .HasForeignKey(n => n.ParentFKID);

modelBuilder.Entity<Order>()
    .HasMany(o => o.Notes)
    .WithRequired()
    .HasForeignKey(n => n.ParentFKID);

modelBuilder.Entity<Invoice>()
    .HasMany(i => i.Notes)
    .WithRequired()
    .HasForeignKey(n => n.ParentFKID);

I expect here that ParentFKID is a property for the FK in your Note class. If you don't want that replace HasForeignKey by: .Map(m => m.MapKey("ParentFKID"));

---

Edit

Using independent associations (i.e. using MapKey without a FK property in the Note class) with a shared FK column does not work and throws exceptions when the mapping is defined.

---

Keep in mind that if you are going to create the database schema with Code-First EF will enforce all the FK constraints. You have to disable that manually in the database then (or maybe it's also possible with a code-based customized migration, but I don't know if and how).

I also expected that the ParentFKID column is not nullable (hence WithRequired) because, I guess, a note without a parent doesn't make sense.

I would suggest to avoid having navigation properties to the parent - customer, order and invoice - in the Note class. If you would attempt to load a note including its parent - and you had to include all three possible parent navigation properties because you can't know from a given note which type the parent has - EF will create INNER JOIN queries (due to the required relationship and because it expects that the constraint is enforced) and this will return no result, even not the parent. (You could possibly hack around the INNER JOIN by using WithOptional instead of WithRequired - despite of the FK not allowing NULL values. EF would create a LEFT OUTER JOIN then when eager loading the parents.)

I'm really not sure if all that will work and doesn't have unwished side effects. Although you are saying it's a common scenario EF does not have explicit support for relationships without enforced constraints.

A scenario which EF would support better is having a base entity EntityWithNotes that carries the Notes collection and which Customer, Order and Invoice derive from. You would define only a single relationship between EntityWithNotes and Note. On database side you have the choice to put Customer, Order and Invoice into a single table (Table-Per-Hierarchy (TPH) mapping) which honestly sounds ridiculous considering how different the business meaning of those entities is. I wouldn't even think about it. Or you put Customer, Order, Invoice and EntityWithNotes into different tables (Table-Per-Type (TPT) mapping). But then, TPT is not really known for the best performance. I wouldn't really consider this for such important entities and probably fast growing tables.

Edit

While the mapping with shared a FK property like shown at the beginning works I get other exceptions when I remove the enforcement of the foreign key constraints in the database. Although I can save data successfully I get exceptions about inconsistent states of the ObjectContext because apparently the context always expects that the constraints are enforced.

I would suggest to stay away from this model and use separate tables for CustomerNotes, OrderNotes and InvoiceNotes. If that isn't possible (existing and unchangable database schema?) it is questionable if Entity Framework is an appropriate tool for such a schema.