C
C#•2mo ago
Angius

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
Denis•2mo 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
Angius
Angius•2mo ago
No rush Also, I'm staying far away from Oracle. This is good ol' code-first Postgres
Omnissiah
Omnissiah•2mo 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
Angius
Angius•2mo ago
Pivot table would not make much sense for a 1-1 relationship
Omnissiah
Omnissiah•2mo ago
why is it 1-1
Angius
Angius•2mo 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
Omnissiah
Omnissiah•2mo ago
ah ok so the ICommentable thing can only be 1 type at a time
Angius
Angius•2mo 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
Omnissiah
Omnissiah•2mo 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)
Angius
Angius•2mo 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
Omnissiah
Omnissiah•2mo 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 😦
Angius
Angius•2mo ago
Ah, yeah, I can see how that could've been confusing
Want results from more Discord servers?
Add your server