How do I model an alternate foreign key in Entity Framework Core

ef-migrations entity-framework-core foreign-key-relationship


I'm trying to model a structure in EF, where we have multiple entities, each of which have localization content. All this localization content exists in a single table. (See example below)

I'm running into an issue, when I define the foreign keys with fluent syntax, and when EF runs its migration to build the DB, it will apply multiple foreign keys on the LKey field in the Localization table. i.e FK_Localization_Foo1_LKey/FK_Localization_Foo2_LKey. This is incorrect, as now its impossible to insert any records into the localization table.

How can I stop this behavior, or what should I change to achieve what I'm looking for in EF?

p.s. I've seen examples suggested where an intermediate table is created, which would only contain LKey as its primary key, and the other tables reference this. I'd like to avoid this if possible to keep our DBA happy.

  public class Foo
      public string LKey { get; set; }
      public ICollection<Localization> Localizations { get; set; }
  // many entities similar to 'foo' with the same navigation

  public class Localization
      public int LocalizationId { get; set; }

      public string LKey { get; set; }
      public string LangISO { get; set; }

// inside the OnModelCreating method, there are multiple entries like the below, one for each 'Foo' type entity above

    .HasMany(pt => pt.Localizations)
    .HasPrincipalKey(pt => pt.LKey)
    .HasForeignKey(l => l.LKey);

Update: Below are examples of queries which currently run against this DB(and have ran for years), so its safe to say, the model is not incompatible with a RDB.

When I went asking, our DBA followed up with "this is absolutely possible. It works, as there is a relationship, but no foreign key constraint. Its not NF, but it works just fine."

Perhaps I wasn't clear in my initial question, apologies if this is the case.What we're looking to do, is model the relationship, and allow the normal EF navigation on the model (which does work with the above code), but not have migrations create the FKeys.

select f1.Foo1Id, ll.Text from Foo1 f1
inner join LocalizationLanguage ll on ll.DescriptionKey = f1.DescriptionKey

select f2.Foo2Id, ll.Text from Foo2 f2
inner join LocalizationLanguage ll on ll.DescriptionKey = f2.DescriptionKey
7/5/2017 10:52:58 AM

Popular Answer

You are trying to implement data structure absolutely incompatible with relational databases.

You can't have one table (Localizations) with one field (LKey) which is foreign key to one of many other tables. Relational DB does not support this. DB Engine (and you too) will never know what table (Foo1, Foo2,... FooX) contain "parent" record for particular child record LKey='ABCD'.

You have two choices:

  1. If your Foo tables have similar fields - use Table Per Hierarchy pattern - all your Foo entities will be stored in one physical table with additional field with class name.
    (patterns table per type (TPT) and table per concrete type (TPC) will be supported later)

  2. Reverse direction of your relationships. Make Localizations your parent tale (with LKey key) and all Foo tables - child with LKey as foreign key.

6/19/2017 8:50:44 AM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow