One-to-one mapping in multiple tables

Question

I'm trying to solve one puzzle, but with no luck so far.

I have an article (or blog post) and comment entities, they both have content. In order to support lazy loading for content (there is no need to load the content when I need to display a list of articles or comments) I decided to move content to separate table and organize one-to-one mapping. Here is an example of what I think:

public class Content {
    [Key]
    public int ID { get; set; }
    public string RawContent { get; set; }
    // a bunch of scalar properties, like content type and so on
}

public class BlogArticle {
    [Key]
    public int ID { get; set; }
    public int ContentID { get; set; }
    [ForeignKey(nameof(ContentID)]
    public virtual Content Text { get; set; }
    // other properties related to BlogArticle
}

public class Comment {
    [Key]
    public int ID { get; set; }
    public int ContentID { get; set; }
    [ForeignKey(nameof(ContentID)]
    public virtual Content Text { get; set; }
    // other properties related to comment
}
<...>

From first look it seems ok: I can create blog articles, comments and attach content (at first I insert content, obviously). Update works as well. However, deletion doesn't work: when I delete blog article or comment, content is not deleted (but I want to delete it when blog article or comment are deleted, not opposite).

From what I understand my biggest issue because of relationship direction: in my case, Content entity is principal end and BlogArticle and Comment are dependent ends. In order to solve the puzzle, I need to change principal/dependent relationship. Again, from what I understand in order to change relationship direction I need to have a foreign key in Content entity and use fluent API to describe who is parent (principal) and who is child (dependent) in one-to-one relationship. Since many tables (there might be other entities with content property) are pointing to Content table, it doesn't seem very easy. Am I correct in my understanding?

One possible solution I could imagine is to create multiple foreign keys in Content table and point to each related table:

public class Content {
    [Key]
    public int ID { get; set; }
    public string RawContent { get; set; }
    // foreign keys
    public int BlogArticleID { get; set; }
    public int CommentID { get; set; }
    public int WebWidgetID { get; set; }
    // other foreign keys if necessary
}

probably, foreign keys must be nullable (because only single foreign key is used at once). Then use Entity Framework fluent API to describe relationship directions and organize cascade delete. For me it looks ugly, but I have no other ideas.

My question: is my proposed solution good/reliable? Are there other options I can look at?

Thanks in advance!

Answer 1

All your thoughts are correct. And your proposed solution is the only way with traditional relational design. The drawback of course is the need of multiple mutually exclusive nullable FKs.

The other options I see are as follows:

(1) Using EF inheritance for the entities holding Content. e.g.

public abstract class EntityWithContent
{
    [Key]
    public int ID { get; set; }
    public virtual Content Text { get; set; }
}

public class BlogArticle : EntityWithContent
{
    // other specific properties
}

public class Comment : EntityWithContent
{
    // other specific properties
}

and configured one-to-one relationship between Content (dependent) and EntityWithContent (principal) using either shared PK association or FK association.

But since EF Core currently supports only TPH strategy (i.e. all the derived entities share one and the same table with union of all fields), I won't recommend it.

(2) Making Content owned type.

This is closer to the intent, but unfortunately EF Core currently always loads the owned entity data along with the owner data (even if they are configured to be provided by different database tables), which is against your original goal, so I won't suggest that either.

(3) Using table splitting feature.

If the main goal is simple to support controlled (lazy/eager/explicit) loading and the Content is always required, then this might be the best solution so far.

It would require a bit more configuration, but at the end it will give you the original table design (single table per entity) with the desired loading behavior:

Model:

public abstract class Content
{
    [Key]
    public int ID { get; set; }
    public string RawContent { get; set; }
    // a bunch of scalar properties, like content type and so on
}

public class BlogArticle
{
    [Key]
    public int ID { get; set; }
    public virtual BlogArticleContent Text { get; set; }
    // other properties related to BlogArticle
}

public class BlogArticleContent : Content
{
}

public class Comment
{
    [Key]
    public int ID { get; set; }
    public virtual CommentContent Text { get; set; }
    // other properties related to comment
}

public class CommentContent : Content
{
}

Note that here Content class is not part of EF inheritance hierarchy, but simple base class with the common properties (abstract modifier is not strongly necessary). The actual derived classes might or might not define their own properties.

Configuration:

modelBuilder.Entity<BlogArticle>().ToTable("BlogArticles");
modelBuilder.Entity<BlogArticle>()
    .HasOne(e => e.Text)
    .WithOne()
    .HasForeignKey<BlogArticleContent>(e => e.ID);
modelBuilder.Entity<BlogArticleContent>().ToTable("BlogArticles");

modelBuilder.Entity<Comment>().ToTable("Comments");
modelBuilder.Entity<Comment>()
    .HasOne(e => e.Text)
    .WithOne()
    .HasForeignKey<CommentContent>(e => e.ID);
modelBuilder.Entity<CommentContent>().ToTable("Comments");