EF Core: multiple many-to-many relationships between the same entities

Question

I'm making a project which requires many-to-many relationships. I know that in order to do this, you should create join tables.

In my project we have users, series and episodes. We will ignore the episodes for simplicity here. The users have favorite series and a watchlist.

public class User 
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string FullName { get; set; }

    public List WatchList { get; }
    public List FavoriteSeries { get; }
}

The join entity:

public class UserSeries
{
    public int UserId { get; set; }
    public User User { get; set; }

    public int SeriesId { get; set; }
    public Series Series { get; set; }
}

The Series entity is not that important, it's a standard type of entity.

Now Im trying to make configurations for EF Core, I do this with Fluent API Configurations (IEntityTypeConfiguration). I first tried to make a UserSeriesConfiguration like this:

class UserSeriesConfiguration : IEntityTypeConfiguration
{
    public void Configure(EntityTypeBuilder builder)
    {
        builder.ToTable("UserSeries");
        builder.HasKey(us => new { us.UserId, us.SeriesId });

        builder.HasOne(us => us.User).WithMany(u => u.FavoriteSeries).HasForeignKey(ue => ue.UserId);
        builder.HasOne(us => us.User).WithMany(u => u.WatchList).HasForeignKey(us => us.UserId);
        builder.HasOne(us => us.Series).WithMany().HasForeignKey(us => us.SeriesId);
    }
}

EF Core complained:

Cannot create a relationship between 'User.WatchList' and 'UserSeries.User', because there already is a relationship between 'User.FavoriteSeries' and 'UserSeries.User'. Navigation properties can only participate in a single relationship.

I tried making a UserFavoriteSeriesConfiguration and a UserWatchListConfiguration, and give them seperate table names, but this is rather ugly, makes for duplicate code and lots of extra configuration classes (especially because with episodes etc. I have this setup too), and worst of all: it still didn't work...

Is there a simple solution to this problem? Worst case I will have to make join entities (in stead of UserSeries can then make UserFavoriteSeries), but this is doubling down on the negatives I described in the previous paragraphs: lots of extra classes and duplicate code.

user_10648865 · Answer

A "faved" serie is a different relationship from a "watchlisted" serie, therefore you need to, somehow, be able to tell apart those two different types of relationships when storing the data in the Database.

Right now, you only have one table to map both relationship, and this is what's happening:

You see the problem? There is a repeated row in the UserSerie table, probably because Diego has marked as Fav the serie Bojack and he also added it to his Watchlist. However, there is no way to tell which row represents the Fav relation and which one represents the Watchlisted relation.

First approach: add an additional attribute to UserSerie as a discriminator

One approach would be to add a new attribute (database table column) to store the type of relationship between User and Serie.

You can see now clearly what type of relationship is each row representing: F is a faved serie and W a watchlisted serie.

It would be very easy to add other types of relationships (series to recommend, series disliked, or whatever).

This mean that now the User entity will have just one List of UserSerie, where each UserSerie has a Type property that can be checked to determine the type of the relationship between that user and that serie.

public class User 
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string FullName { get; set; }

    public List Series { get; }
}

Second approach: add an additional relation table`

Another approach is to have a different table for each different relationship:

public class User 
{
    public int Id { get; set; }
    public string Email { get; set; }
    public string FullName { get; set; }

    public List FavedSeries { get; }
    public List WatchlistedSeries { get; }

}

This means that if, in the future, you wan't to add an additional relationship, you have to create another table.

There are definitely other ways to solve the same problem that I don't mention, and each of them will have a different set of tradeoffs that you will have to analyze in order to choose the solution that best suits your scenario :)

EF Core: multiple many-to-many relationships between the same entities

1 Answers