In this article we demonstrate how to configure many-to-many relationship between two entities. We will explore various approaches depending on our needs, providing code examples using Entity Framework to save and retrieve data.
Each approach will be based on configuring a many-to-many relationship between a User and Book entity. This allows us to track which books a user has read and which users have read a specific book. We’ll be utilizing the Microsoft.EntityFrameworkCore and Microsoft.EntityFrameworkCore.InMemory NuGet packages.
The code is available on GitHub
Configuring Many-to-Many Relationship with a Join Table
In every relational database, when we create a many-to-many relationship between two tables, an intermediate (join) table is also created to connect, with extra foreign keys, those two entities. This approach involves having the join table as a separate entity in our code. First, let’s create the User and Book entities as shown below:
public class User {
public int Id { get; set; }
public string Name { get; set; }
public ICollection<UserBook> BooksRead { get; set; }
}
public class Book {
public int Id { get; set; }
public string Name { get; set; }
public ICollection<UserBook> BooksRead { get; set; }
}Next, let’s create the join table which will have two references, one to the Book and another to the User:
public class UserBook {
public int Id { get; set; }
public User User { get; set; }
public Book Book { get; set; }
}This establishes two one-to-many relationships: one between User and UserBook and another between Book and UserBook. This results in a many-to-many relationship between User and Book.
The UserBook entity links a User with a particular Book. Each User can have many UserBook instances, representing the books the User has read, and each Book can also have many UserBook instances, denoting which users have read the particular book. This approach is also more compatible with a relational database because the join table will be a separate table.
However, this approach clutters our code because the sole purpose of UserBook is to link User with the Books they have read. This requirement is driven by the relational nature of the underlying database and not by our business logic.
Creating the DbContext
Next, we create a DbContext to configure, write, and retrieve data from the database. In this example, we will use an in-memory database.
public class AppContext : DbContext
{
public DbSet<User> Users { get; set; }
public DbSet<Book> Books { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseInMemoryDatabase("db");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{}
}In the OnModelCreating method where we would typically put our configuration, because we explicitly set the intermediate join table, we don’t need to configure anything further. Also, the Entity Framework uses reflection and automatically configures many aspects of our Entities, such as setting keys and relationships.
Read and Write
To save and retrieve data from the database, we can create the entities and use the DbContext to add the created objects and call the SaveChangesAsync method:
await using var ctx = new AppContext();
User jim = new User() { Name = "Jim" };
User nick = new User() { Name = "Nick" };
Book designPatterns = new Book() { Name = "Design Patterns" };
Book refactoring = new Book() { Name = "refactoring" };
UserBook userBook1 = new UserBook() { User = jim, Book = designPatterns };
UserBook userBook2 = new UserBook() { User = jim, Book = refactoring };
UserBook userBook3 = new UserBook() { User = nick, Book = refactoring };
ctx.AddRange(jim, nick, designPatterns, refactoring, userBook1, userBook2, userBook3);
await ctx.SaveChangesAsync();For retrieving data, we can use LINQ in the appropriate entity list in our DbContext, as shown below:
var users = await ctx.Users.Where(u =>
u.BooksRead.Any(us => us.Book.Name == "Design Patterns")).ToListAsync();
foreach(var user in users)
{
Console.WriteLine("User: " + user.Name);
}As we can see, the intermediate table makes our query a bit more complex than it should be. In the next section, we explore how to get rid of the join table. If we don’t need to store any additional data in the join table, such as a timestamp for when a book was read, then this approach might not be ideal.
Configuring Many-to-Many Relationship Without a Join Table
In this approach, we don’t have the join table as a separate entity in our code and let Entity Framework handle the creation of the join table in the database, managing the mappings automatically. First, we remove the UserBook class representing the join table. Additionally, this time, the User will directly have a collection of Book entities, and vice versa:
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Book> BooksRead { get; set; }
}
public class Book
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<User> Users { get; set; }
}Read and Write
There’s no change to AppContext, data entry will be simpler this time as the UserBook entity no longer exists:
await using var ctx = new AppContext();
User jim = new User() { Name = "Jim" };
User nick = new User() { Name = "Nick" };
Book designPatterns = new Book() {
Name = "Design Patterns",
Users = new List<User> { jim, nick }
};
Book refactoring = new Book() {
Name = "refactoring",
Users = new List<User> { jim }
};
ctx.AddRange(jim, nick, designPatterns, refactoring);
await ctx.SaveChangesAsync();
// Select the users that have read the Design Patterns book.
var users = await ctx.Users.Where(u =>
u.BooksRead.Any(b => b.Name == "Design Patterns")).ToListAsync();
foreach (var user in users)
{
Console.WriteLine("User: " + user.Name);
}
// Output:
// User: Jim
// User: Nick Without the join table, our code is simpler. If we don’t need any further information to be stored in the join table, then this approach is preferred.
Creating Many-to-Many Relationship with Configurable Join Table
This approach combines the previous methods with an extra configuration for added flexibility.
If we require the join table to be a separate entity in our code, yet still want to utilize collections between User and Book entities without referencing the join table directly, then we can use a join table and add a specific configuration.
This method allows us to maintain a many-to-many relationship, as seen in the previous example, while also utilizing the join table if additional properties (payload properties) are needed, such as a timestamp indicating when the user read a particular book.
First, we create our entities: User, Book and UserBook. We also add a new ReadOn property to the UserBook entity to store when the book was read by the user:
public class User
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<Book> BooksRead { get; set; }
}
public class Book
{
public int Id { get; set; }
public string Name { get; set; }
public ICollection<User> Users { get; set; }
}
public class UserBook
{
public int id { get;set; }
public User User { get; set; }
public Book Book { get; set; }
public DateTime ReadOn { get; set; }
}Creating the DbContext
Next, we need to provide a specific fluent configuration for the join table in our DbContext, ensuring that Entity Framework recognizes it as the join table between User and Book entities:
public class AppContext : DbContext
{
public DbSet<User> Users { get; set; }
public DbSet<Book> Books { get; set; }
public DbSet<UserBook> UserBook { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
optionsBuilder.UseInMemoryDatabase("db");
}
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<User>()
.HasMany(u => u.BooksRead)
.WithMany(b => b.Users)
.UsingEntity<UserBook>(
l => l.HasOne(ub => ub.Book).WithMany().HasForeignKey("BookId")
.HasPrincipalKey(nameof(Book.Id)),
r => r.HasOne(ub => ub.User).WithMany().HasForeignKey("UserId")
.HasPrincipalKey(nameof(User.Id)),
j => j.HasKey("BookId", "UserId"));
}
}This time, we provide configuration because there is no clear indication that the UserBook is the join table for the many-to-many relationship between User and Book. However, the configuration we provided is very explicit and allows us control many aspects of the relationship. We can adjust parts of the configuration, such as HasForeignKey and HasPrincipalKey, or let the Entity Framework configure the relationship by convention.
Read and Write
Next, we create some data and save them to the database:
await using var ctx = new AppContext();
var jim = new User() { Name = "Jim" };
var nick = new User() { Name = "Nick" };
var designPatterns = new Book() { Name = "Design Patterns" };
var refactoring = new Book() { Name = "refactoring" };
var userBook1 = new UserBook()
{
User = jim,
Book = designPatterns,
ReadOn = new DateTime(2020, 1, 1)
};
var userBook2 = new UserBook()
{
User = jim,
Book = refactoring,
ReadOn = new DateTime(2010, 1, 1)
};
var userBook3 = new UserBook()
{
User = nick,
Book = refactoring,
ReadOn = new DateTime(2020, 2, 1)
};
ctx.AddRange(jim, nick, designPatterns,
refactoring, userBook1, userBook2, userBook3);
await ctx.SaveChangesAsync();
Now, we can select every User who has read a book before 5/5/2010:
var readers = await ctx.UserBook.Where(u =>
u.ReadOn > new DateTime(2010, 5, 5)).Select(x => x.User).ToListAsync();
foreach (var user in readers)
{
Console.WriteLine("Reader: " + user.Name);
}
// Output:
// Reader: Jim
// Reader: NickAs well as, use the collection of Book in the User entity and select every User that has read a particular book:
var users = await ctx.Users.Where(u =>
u.BooksRead.Any(b => b.Name == "Design Patterns")).ToListAsync();
foreach (var user in users)
{
Console.WriteLine("User: " + user.Name);
}In conclusion, this method allows us to utilize many-to-many mappings and queries, while also utilizing the join entity for storing additional information when needed.
