EF Code First Single foreign key to multiple parents

Stringbean68

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 ?

Slauma

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.

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

EF Code First foreign key with multiple keys

From Dev

Disable Foreign Key Constraint Code First EF

From Dev

EF Code First Foreign Key Same Table

From Dev

EF Code First Foreign Key Relationship

From Java

EF Code First foreign key without navigation property

From Dev

EF Code First The INSERT statement conflicted with the FOREIGN KEY constraint

From Dev

EF Code First Migrations creating extra foreign key

From Dev

EF Code First Fluent API specifying the Foreign Key property

From Dev

EF code first foreign key ignored, getting "Invalid column name"

From Dev

EF-Code First navigation property foreign key in complex type

From Dev

EF Code First The INSERT statement conflicted with the FOREIGN KEY constraint

From Dev

Renaming foreign key properties in code first EF classes causes an exception

From Dev

Foreign key is created incorrrectly using EF code first

From Dev

MVC modelbuilding: multiple foreign keys in table, code first EF

From Dev

MVC modelbuilding: multiple foreign keys in table, code first EF

From Dev

Defining multiple Foreign Key for the Same table in Entity Framework Code First

From Dev

Multiple Foreign Key for Same table in Entity Framework Code First

From Dev

EF Code First Approach: Confused in EF Foreign Key constraint by fluent syntax

From Dev

Multiplicity is not valid in Role in relationship: EF code first one to one relationship with same primary key and foreign key

From Dev

EF Code First - mapping two foreign key columns in child table to the same primary key

From Dev

Circular foreign key code first

From Dev

EF6 Code First: Using Fluent API to declare a Foreign Key

From Dev

How to define a table that its primary key is constructed from 2 foreign keys with EF code-first

From Dev

Remove table name prefix in EF code first foreign key table column

From Dev

How to expose Foreign Key property to existing entity having navigational property using EF6 Code First

From Dev

Laravel multiple records at once for single foreign key

From Dev

insert multiple records at once for single foreign key

From Dev

Select in template parents foreign key

From Dev

Entity Framework Code First Foreign Key issue

Related Related

  1. 1

    EF Code First foreign key with multiple keys

  2. 2

    Disable Foreign Key Constraint Code First EF

  3. 3

    EF Code First Foreign Key Same Table

  4. 4

    EF Code First Foreign Key Relationship

  5. 5

    EF Code First foreign key without navigation property

  6. 6

    EF Code First The INSERT statement conflicted with the FOREIGN KEY constraint

  7. 7

    EF Code First Migrations creating extra foreign key

  8. 8

    EF Code First Fluent API specifying the Foreign Key property

  9. 9

    EF code first foreign key ignored, getting "Invalid column name"

  10. 10

    EF-Code First navigation property foreign key in complex type

  11. 11

    EF Code First The INSERT statement conflicted with the FOREIGN KEY constraint

  12. 12

    Renaming foreign key properties in code first EF classes causes an exception

  13. 13

    Foreign key is created incorrrectly using EF code first

  14. 14

    MVC modelbuilding: multiple foreign keys in table, code first EF

  15. 15

    MVC modelbuilding: multiple foreign keys in table, code first EF

  16. 16

    Defining multiple Foreign Key for the Same table in Entity Framework Code First

  17. 17

    Multiple Foreign Key for Same table in Entity Framework Code First

  18. 18

    EF Code First Approach: Confused in EF Foreign Key constraint by fluent syntax

  19. 19

    Multiplicity is not valid in Role in relationship: EF code first one to one relationship with same primary key and foreign key

  20. 20

    EF Code First - mapping two foreign key columns in child table to the same primary key

  21. 21

    Circular foreign key code first

  22. 22

    EF6 Code First: Using Fluent API to declare a Foreign Key

  23. 23

    How to define a table that its primary key is constructed from 2 foreign keys with EF code-first

  24. 24

    Remove table name prefix in EF code first foreign key table column

  25. 25

    How to expose Foreign Key property to existing entity having navigational property using EF6 Code First

  26. 26

    Laravel multiple records at once for single foreign key

  27. 27

    insert multiple records at once for single foreign key

  28. 28

    Select in template parents foreign key

  29. 29

    Entity Framework Code First Foreign Key issue

HotTag

Archive