I've searched stackoverflow for a proper solution on generating a many-to-many relationship, using EF Core 2.0, Code first and Fluent API.
A simple scenario would be:
public class Person
{
public Person() {
Clubs = new HashSet<Club>();
}
public int PersonId { get; set; }
public virtual ICollection<Club> Clubs { get; set; }
}
public class Club
{
public Club() {
Persons = new HashSet<Person>();
}
public int ClubId { get; set; }
public virtual ICollection<Person> Persons { get; set; }
}
Please correct me if im wrong but I could honestly not find a question that contains an elaborate explanation on how to do this using the described tools. Can anyone explain how this is done?
The correct "setup" for this is:
So, this block for configuring the "glue-table" is not necessary as in @Kirk example:
So every
Person
has zero or moreClubs
and everyClub
has zero or morePersons
. As you stated correctly, this is a proper many-to-many relation.You probably know that a relational database needs an extra table to implement this many-to-many relationship. The nice thing about entity framework, is that it recognizes this relationship and creates this extra table for you.
At first glance it seems a problem that this extra table is not a
dbSet
in yourDbContext
: "How to perform a join with this extra table if I don't have aDbSet
for it?".Luckily, you don't need to mention this extra table in your queries.
If you need a query like "Give me all 'Clubs' that ... from every 'Person' who ..." don't think in joins. Instead use the ICollections!
Get all "John Doe" persons with all Country clubs they attend:
Entity framework will recognize that a join with the extra many-to-many table is needed, and will perform this join, without you mentioning this extra table.
The other way round: Get all country clubs with their "John Doe" Persons:
I've experienced that once I started to think in the resulting collections that I want instead of the joins I needed to get these collections I found that I hardly use the joins. This is the case for one-to-many relations as well as many-to-many relations. Entity framework will internally use the proper joins.
This is not yet possible in EF Core without using an explicit class for the join. See here for an example of how to do that.
There's an open issue on Github asking for the ability to do this without the need for an explicit class, but it has not yet been completed.
Using your scenario, the example I linked would recommend the following entity classes:
The following
OnModelCreating
would then be used for setup:Be sure to go to the open issue I linked and voice your frustration if you feel the need.
EDIT: The open issue suggests using a simple
Select
to navigate through this somewhat cumbersome hierarchy. In order to get from aPersonId
to a collection ofClub
s, you can useSelectMany
. e.g.:I can't vouch for whether this is truly a "best practice", but it should certainly do the trick and I think its fair to say it's not overly ugly.