3

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.

Ramin Bateni
  • 16,499
  • 9
  • 69
  • 98
  • 1
    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. – Camilo Terevinto Sep 19 '20 at 14:36
  • 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 – Aluan Haddad Sep 20 '20 at 07:07
  • @AluanHaddad may u explain the implementation of what you wrote in your comment in a answer? – Ramin Bateni Sep 21 '20 at 08:03
  • 1
    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 Notifications { get; set; } = new (); }` etc. Then remove `NotifTypes` – Aluan Haddad Sep 21 '20 at 10:43
  • And if you're querying notifications directly, you can write `from pn db.Notifications.OfType() where pn.Poll.Answer1 == "Biden or Trump" select pn`; – Aluan Haddad Sep 21 '20 at 10:45
  • @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? – Ramin Bateni Sep 21 '20 at 16:13
  • 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. – Aluan Haddad Sep 21 '20 at 16:21

1 Answers1

5

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

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

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.

Bill Karwin
  • 538,548
  • 86
  • 673
  • 828