EF, How to conditionally include a navigation property that type of it related to value of another property?

Question

I have the bellow entities:

public class Notification
{
    public int Id { get; set; }
    public string Title { get; set; }
    public Guid RefId { get; set; }
    public Object Ref { get; set; } //  << The navigation property: Sometime its type is Poll and sometime is Test, maybe I add other types too 
    public NotifTypes Type { get; set; }
}

public enum NotifTypes
{
    Poll=1,
    Test=2,
    // Other NotifTypes here
}

//-------------------------------------------------------------------

public class Test
{
    public int Id { get; set; }
    public string Title { get; set; }

    public IEnumerable<Notification> { get; set; }
}

public class Poll
{
    public int Id { get; set; }
    public string Answer1 { get; set; }
    public string Answer2 { get; set; }

    public IEnumerable<Notification> { get; set; }
}

OK,

• When the Type property of Notification object is equal Poll, the RefId will fill by a PollId
• When type is equal Test, the refId will fill by a TestId.

Now I want conditionally include the related Poll or Test in Ref property. How should I implement it?

I want prevent to add separate Ids like PollId, TestId and.... to Notification because I'm sure that each time just one of them has value, so I want have one RefId and one Ref property instead of them.

Syrinx

That's not a good design and I'm pretty sure there's no way you'll get it to work with EF. You should have a separate entity (the one Notification points to) that has the links to the relevant tables. – Quadripartite

That can only work if Poll and Test have a common base type and are mapped to the database via DB inheritance, such as TPH or TPT, also, your NotifTypes enum is problematic – Decare

@AluanHaddad may u explain the implementation of what you wrote in your comment in a answer? – Syrinx

I mean class Notification { public string Title { get; set; } public int Id { get; set; } } then class PollNotification: Notification { public Poll Poll { get; set;} } and class TestNotification: Notification { public Test Test { get; set; } } and class Poll { public ICollection<PollNotification> Notifications { get; set; } = new (); } etc. Then remove NotifTypes – Decare

And if you're querying notifications directly, you can write from pn db.Notifications.OfType<PollNotification>() where pn.Poll.Answer1 == "Biden or Trump" select pn; – Decare

@AluanHaddad, in my design I just created one table for notifications in DB. How many tables will be generated in DB with your suggested design for notifications? and what are their relations? – Syrinx

No new tables will be added. That is how EF Core works by default. The two Notification subclasses will introduce foreign key properties into the Notifications table, one referencing Poll and the other Test. Otherwise, you won't be able to have a navigation property as you describe. – Decare

Answer 1

I don't know EntityFramework, but you asked me to answer this.

You're basically reinventing polymorphic-associations which is not a good relational design. You can read a few of my past answers about this concept:

• Possible to do a MySQL foreign key to one of two possible tables?
• Why can you not have a foreign key in a polymorphic association?
• MySQL - Conditional Foreign Key Constraints

I tend to answer MySQL questions, but the answer is the same for any other brand of RDBMS. The fact that you cannot declare an actual foreign key constraint that references multiple tables should be a clue that this design is not right.

The easiest solution from a data modeling perspective is to create an independent attribute for each of your potential table references. All but one of these will be NULL on a given row.

I have no idea how EntityFramework might support this. @AluanHaddad's advice sounds good.

Try not to break relational concepts. Down that path is the Inner-Platform Effect antipattern.

Slumber

Thank u Bill 4 your clean answer. – Syrinx