C#•4mo ago

EF Core 1-1 relationship with automatic discriminator

I have a CommentsThread entity and multiple ICommentable entities (Blogpost, Profile, Document, etc.) that all reference it:

public sealed class CommentsThread
{
    public long Id { get; init; }
    //...
}
public interface ICommentable
{
    long CommentsThreadId { get; set; }
    CommentsThread CommentsThread { get; set; }
}
public sealed Blogpost : ICommentable
{
    public long CommentsThreadId { get; set; }
    public CommentsThread CommentsThread { get; set; }
}

public sealed class CommentsThread
{
    public long Id { get; init; }
    //...
}
public interface ICommentable
{
    long CommentsThreadId { get; set; }
    CommentsThread CommentsThread { get; set; }
}
public sealed Blogpost : ICommentable
{
    public long CommentsThreadId { get; set; }
    public CommentsThread CommentsThread { get; set; }
}

now, I would like a way to easily find what the comments thread belongs to. Does it contain comments of a blogpost? User profile? Document? One way I figured was to reverse the relationship, and instead have a bunch of nullable FKs in the CommentsThread entity, pointing to all the ICommentables. That way, I could do some

string type;
if (thread.BlogpostId is not null) type = "blogpost";
else if (thread.ProfileId is not null) type = "profile";
else if (thread.DocumentId is not null) type = "document";
else type = "unknown";

string type;
if (thread.BlogpostId is not null) type = "blogpost";
else if (thread.ProfileId is not null) type = "profile";
else if (thread.DocumentId is not null) type = "document";
else type = "unknown";

but it all seems iffy at best. Ideally, what I would like instead, was some

public CommentsSource Source { get; private set; }
public long SourceId { get; private set; }

public CommentsSource Source { get; private set; }
public long SourceId { get; private set; }

properties on the CommentsThread, with CommentsSource being an enum. Problem is, I have nary a clue how to do that. I considered maybe computed columns, but they seemingly can only reference the current entity.

12 Replies

Denis•4mo ago

I believe I've solved this problem at some point. I'll check it out asap. If I don't in the next 3 hours, dm or ping me. Worst case scenario you design this database using Oracle DB SQL Developer, deploy it, and then reverse engineering using EF Core

AngiusOP•4mo ago

No rush Also, I'm staying far away from Oracle. This is good ol' code-first Postgres

HtmlCompiler•4mo ago

isn't what you want just a relationship table with an id to CommentsThread and an id to ICommentable? (maybe with some added data for ease of use) or maybe i'm not understanding the question

AngiusOP•4mo ago

Pivot table would not make much sense for a 1-1 relationship

HtmlCompiler•4mo ago

why is it 1-1

AngiusOP•4mo ago

No, I want 1-1 where Foo, Bar, Baz have a foreign key to Slurp, and Slurp has a column that has a value of either "foo", "bar", "baz" respectively

HtmlCompiler•4mo ago

ah ok so the ICommentable thing can only be 1 type at a time

AngiusOP•4mo ago

yep ICommentable is really just for convenience and strictness here, on the code side Doesn't really matter that much for EF code It's not inheritance with discriminators or what have you

HtmlCompiler•4mo ago

ok but still what you are asking for ("what the comments thread belongs to") is a collection of elements, because CommentsThread belongs to multiple ICommentables am i right on this iirc postgres has arrays as a type (although managing sync arrays wouldn't be that nice -- and ef has no support for it)

AngiusOP•4mo ago

It does not belong to multiple How could it? Foo has a foreign key to one and only one Slurp And each and every Slurp is only ever referenced by either one single Foo, one single Bar, or one single Baz

HtmlCompiler•4mo ago

i got confused by the last part "I have a CommentsThread entity and multiple ICommentable that all reference it:" now that we've estabilished that the relationship is 1-1 as the title said i can go be low iq somewhere else 😦

AngiusOP•4mo ago

Ah, yeah, I can see how that could've been confusing

Gaming

Programming

EF Core 1-1 relationship with automatic discriminator