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<UserSeries> WatchList { get; }
    public List<UserSeries> 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<UserSeries>
{
    public void Configure(EntityTypeBuilder<UserSeries> 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.

I think you need to have 2 different join entity respectively for favourites and watchlist. You can't use the same join table. It doesn't make sense because imagine the data in your join table when the a user has the same movie in favourite and also watchlist. U can't have 2 rows with the same value in the join table. — Kes
– Kes, Commented Mar 24, 2020 at 14:26

dglozano · Accepted Answer · 2020-03-24 20:56:21Z

3

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<UserSeries> 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<UserFavedSeries> FavedSeries { get; }
    public List<UserWatchlistedSeries> 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 :)

edited Mar 24, 2020 at 20:56

answered Mar 24, 2020 at 15:12

dglozano

6,6472 gold badges25 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Dante De Ruwe Over a year ago

Thanks for the effort and very well structured explanation. I think your "type" approach is interesting. However, currently the UserSeries table has a composite key {userId, seriesId}. should I add the "type" to this composite key (as the combination of the three will be unique), or should this be an alternate key and should I add a PK to the UserSeries table?

dglozano Over a year ago

@Dante the easiest thing would be to add a PK to the UserSeries table. Btw, keep in mind that with the "discriminator" approach, you will have a "denormalized" model (en.wikipedia.org/wiki/Database_normalization) which in the DB world is something not desired. Think that if you have some properties that are exclusive of the "Fav" relationship (for example, a column rating saying how much you liked the series) and some other properties that are exlusive of the "Watchlist" relationship (for example, a date you would like to watch it), then ....

dglozano Over a year ago

... you will have a lot of null depening on the type of the row: fav rows will have null on the date column, whereas watchlisted rows will have null on the fav column. That being said, that approach however is many times being chosen because it's really simple to make queries whan all the data is in a single table

dglozano Over a year ago

The "problem" you faced is related to another popular problem on the ORM world: how to map inheritance relationships in a DB model. The discriminator column would be the same as the "Type per hierarchy" strategy. Check learn.microsoft.com/en-us/archive/blogs/alexj/… or huagati.blogspot.com/2010/10/…

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related