EF DB First refresh is not detecting FKs to a particular table

Question

Not sure what's happened to my setup, but of late, when I update my database with new fields or new tables that have a foreign key to a particular table (Person), my database refresh picks up the new fields, but does not recognize the FK relationships to Person. Foreign keys to most other tables work fine.

What setting might be missing?

Perhaps a clue: Person acts as the base type for several other tables, e.g. Manager, Customer etc. which all share the basic characteristics of having name, birth date, gender etc., and the descendant tables have a primary key that also acts as foreign key to Person. FK relationships to the descendant tables are also not being recognized.

Answer 1

Entity Framework can be rather limited when mapping complex inheritance models. However you basically have three options when mapping inheritance:

TPH (table per hierarchy) mapping: This mapping generates a single table for all your fields in the hierarchy and conflicting or "same-name" fields will be appended by a numberic seed. For example Name1 (Person), Name2 (Manager) etc.

TPT (table-per-type) mappping: This mapping generates individual tables for each object, however conflicting property names are only mapped in the base class. In your instance the Person class. Note: TPT is discouraged due to the complex join queries and in some cases the anomalies you are experiencing

TPC (table-per-concrete-type) mapping: Similar to the TPT, except all properties of a class, including inherited properties, map to columns of the corresponding table

Solution:

The assumption is that you are using TPT and this means your inherited class members are not being mapped. I would suggest you look at TPC for generating your mapping and migration. You will need to look at the Fluent API in EF to achieve the above mentioned mappings:

TPC Example:

Declare this in your DbCOntext object:

 protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Person>() 
        .Property(c => c.CourseID) 
        .HasDatabaseGeneratedOption(DatabaseGeneratedOption.None); 

        modelBuilder.Entity<Manager>().Map(m => 
        { 
            m.MapInheritedProperties(); 
            m.ToTable("Manager"); 
        }); 

        modelBuilder.Entity<Customer>().Map(m => 
        { 
           m.MapInheritedProperties(); 
           m.ToTable("Customer"); 
        });
    }

DISCLAIMER: Its rather difficult to know exactly what your problem is without seeing the inheritance structure or executed migrations, however I find the most common issue in EF relationships is mentioned above.

Answer 2

I believe I have stumbled across the answer myself. The table had an index on the ID field in addition to the primary key. For some reason, all the other tables that had foreign keys to my table were referring to the index rather the PK... and that apparently confused the hell out of EF. I dropped all the FKs, dropped the index, recreated the FKs and recreated the index... and voila! The associations now all appear in the EDMX!