EF Core navigation property without foreign key relations

Question

In my application using EF Core 8.0.7 with a code-first approach, I have a Content table that contains application content based on language. The Content table references records in parent entities through ParentId, with each Content record uniquely identified by the combination of Key, ParentId and LanguageId.

I want to add a navigation property to all entity models that have associated content. Specifically, I want the navigation property to look like this:

public virtual ICollection<Content>? Contents { get; set; }

Here's my Content model class:

public class Content : EntityBase
{
    [MaxLength(40)]
    public string Key { get; set; }
    [MaxLength(2000)]
    public string Value { get; set; }
    public Guid ParentId { get; set; }
    public Guid LanguageId { get; set; }
    [MaxLength(40)]
    public string ParentTable { get; set; }
    public virtual Language? Language { get; set; }
}

Given that EF Core manages navigation properties through foreign keys, how should I configure these relationships? Should I set up the navigation property in a specific way to ensure that the Content records are properly associated with their parent entities? Are there any specific EF Core configurations or conventions I should follow?

Note: I am aware of related posts on this topic, but they did not resolve my issue.

Answer 1

Short answer: No. Avoid this denomalized design. While it looks simple and compact you cannot leverage FK relationships between a single content table and other related ones, and similarly, the database cannot enforce referential integrity either. It is also the worst performing option given the lack of, or complex indexing, and more importantly the fact that all content records are placed in 1 table. If you have 1M content rows across 10 related entities, queries will be more performant with dedicated FKs & indexes across separate content tables (100k here, 250k there, 25k for type C, etc.) than always querying across the 1M rows.

Better:

public abstract class Content : EntityBase
{
    [MaxLength(40)]
    public string Key { get; set; }
    [MaxLength(2000)]
    public string Value { get; set; }
    public Guid LanguageId { get; set; }
    [MaxLength(40)]
    public virtual Language? Language { get; set; }
}

public class CompanyContent : Content
{
    public virtual Company Company { get; set; }
}

public class Company : EntityBase
{
    // ...
    public virtual ICollection<CompanyContent> Contents { get; } = [];
}

Using a TPC or TPT inheritance model. I tend to lean to TPC for simpler, faster joins, but TPT would be potentially more useful if you have the requirement to query across all content, despite relationship.

It doesn't matter that you end up with 10x the number of tables where each table only differs from the FK to the type it supports. The "cost" to the database is extra table definitions, but the data storage cost for total rows remains the same. Querying content for any one smaller table does not involve querying across an the entire content set, just the content rows relating to that table.