Efcore: How to define a many to many relationship over a full entity (aka 'objectified relationship') ?

[FransBouma]

EF Core version: 5.0

According to the documentation: https://docs.microsoft.com/en-us/ef/core/modeling/relationships?tabs=fluent-api%2Cfluent-api-simple-key%2Csimple-key#many-to-many I have to define the intermediate entity of the many to many relationship using the UsingEntity method.

However, what if the intermediate entity is a full entity with non-pk attributes/fields? Consider the many to many relationship between Customer and Employee over Order in Northwind: Order m:1 Customer and Order m:1 Employee defines a m:n relationship between Customer and Employee, and as the intermediate entity has non-pk normal fields, the customer-employee relationship is considered 'objectified'. (Probably a better example is Employee - Department where an employee can work for multiple departments and you want to store the start date an employee started working for a department; that date is part of the relationship so ends up being a field in the intermediate entity 'EmployeeDepartment' which defines the m:n relationship)

As the 'Order' entity in this case is a normal entity, it's unclear what to do here, the documentation is unclear as it suggests I define the m:1 relationships of Order to Customer and Employee in the UsingEntity method call:

    // Snippet from the documentation 
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasMany(p => p.Tags)
            .WithMany(p => p.Posts)
            .UsingEntity<PostTag>(
                j => j
                    .HasOne(pt => pt.Tag)
                    .WithMany(t => t.PostTags)
                    .HasForeignKey(pt => pt.TagId),
                j => j
                    .HasOne(pt => pt.Post)
                    .WithMany(p => p.PostTags)
                    .HasForeignKey(pt => pt.PostId),
                j =>
                {
                    j.Property(pt => pt.PublicationDate).HasDefaultValueSql("CURRENT_TIMESTAMP");
                    j.HasKey(t => new { t.PostId, t.TagId });
                });
    }

But if the entity is an intermediate entity in multiple m:n relationships this feels pretty redundant, as Order then needs to be defined in full in all m:n relationships it's the intermediate entity of (not to speak of the fact that it's unclear you need to define the entity 'Order' in a method call of an m:n relationship to begin with).

The documentation does give a hint:

    // snippet from the documentation
    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<PostTag>()
            .HasKey(t => new { t.PostId, t.TagId });

        modelBuilder.Entity<PostTag>()
            .HasOne(pt => pt.Post)
            .WithMany(p => p.PostTags)
            .HasForeignKey(pt => pt.PostId);

        modelBuilder.Entity<PostTag>()
            .HasOne(pt => pt.Tag)
            .WithMany(t => t.PostTags)
            .HasForeignKey(pt => pt.TagId);
    }

But this doesn't specify the m:n relationship between Post and Tag as in: which navigators to use.

So what to do? I'm currently stuck with this on how to define EF Core 5 mappings for m:n in LLBLGen Pro.

[roji]

You may be looking for something like this (from the .NET Conf talk):
```c#
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity()
.HasMany(u => u.Groups)
.WithMany(g => g.Users)
.UsingEntity(
j => j.HasOne(m => m.Group).WithMany(g => g.Memberships),
j => j.HasOne(m => m.User).WithMany(u => u.Memberships));
}

Note that the Membership type can have any payload properties with additional info (so I think that's the "objectified relationship" you're referring to).

<details>
<summary>Full runnable code sample</summary>

```c#
using System;
using System.Collections.Generic;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Diagnostics;

await using var ctx = new UserContext();
await ctx.Database.EnsureDeletedAsync();
await ctx.Database.EnsureCreatedAsync();

// Insert some data

var jane = new User { Name = "Jane" };
var john = new User { Name = "John" };

var football = new Group { Name = "Football" };
var movies = new Group { Name = "Movies" };

var membership1 = new Membership { User = jane, Group = football };
var membership2 = new Membership { User = john, Group = football };
var membership3 = new Membership { User = jane, Group = movies };

ctx.AddRange(jane, john, football, movies, membership1, membership2, membership3);
await ctx.SaveChangesAsync();

// Query!

var users = await ctx.Users.Where(u => u.Memberships.Any(m => m.Group.Name == "Movies")).ToListAsync();
foreach (var user in users)
{
    Console.WriteLine("User: " + user.Name);
}

public class UserContext : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Group> Groups { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(@"Server=localhost;Database=test;User=SA;Password=Abcd5678")
            .LogTo(Console.WriteLine, new[] { RelationalEventId.CommandExecuted })
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<User>()
            .HasMany(u => u.Groups)
            .WithMany(g => g.Users)
            .UsingEntity<Membership>(
                j => j.HasOne(m => m.Group).WithMany(g => g.Memberships),
                j => j.HasOne(m => m.User).WithMany(u => u.Memberships));
    }
}

public class User
{
    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<Membership> Memberships { get; set; }
    public ICollection<Group> Groups { get; set; }
}

public class Group
{
    public int Id { get; set; }
    public string Name { get; set; }

    public ICollection<Membership> Memberships { get; set; }
    public ICollection<User> Users { get; set; }
}

public class Membership
{
    public int Id { get; set; }
    public User User { get; set; }
    public Group Group { get; set; }
    public DateTime MemberSince { get; set; }
}

[FransBouma]

@roji yes that's indeed the example I was looking for :) I assumed this would be the case but instead of diving into the trial/error rabbit hole I thought let's ask first :) If that example could be added to the docs it would be better I think :)

Edit: My question has been answered, but not sure if the issue should be closed (as it might be used for a workitem). So I'll leave that to you folks :)

[FransBouma]

@roji I overlooked that the relationships are all defined once in your example. In the case of others being still confused:
You can normally define the m:1 relationships at the entity level, so Order -> Employee and Order -> Customer and define the m:n relationship at either side, or just once, it doesn't matter. For clarity that might help (or when you're generating them :P ). I still hate the approach of defining relationships on just 1 side of the relationship, instead of having them defined all at once separately, but alas...

example:

/// <summary>Defines the mapping information for the entity 'Customer'</summary>
/// <param name="config">The configuration to modify.</param>
protected virtual void MapCustomer(EntityTypeBuilder<Customer> config)
{
    config.ToTable("Customers");
    config.HasKey(t => t.CustomerId);
    config.Property(t => t.CustomerId).HasColumnName("CustomerID").HasMaxLength(5);
        //... 
    config.HasMany(c => c.Employees)
          .WithMany(e => e.Customers)
          .UsingEntity<Order>(o => o.HasOne(a => a.Employee).WithMany( x => x.Orders),
                           o => o.HasOne(a => a.Customer).WithMany(y => y.Orders));
}

/// <summary>Defines the mapping information for the entity 'Employee'</summary>
/// <param name="config">The configuration to modify.</param>
protected virtual void MapEmployee(EntityTypeBuilder<Employee> config)
{
    config.ToTable("Employees");
    config.HasKey(t => t.EmployeeId);
    config.Property(t => t.EmployeeId).HasColumnName("EmployeeID").ValueGeneratedOnAdd();
        //....
    config.Property(t => t.ReportsTo);
    config.Property(t => t.PhotoPath).HasMaxLength(255);
    config.Property(t => t.RegionId).HasColumnName("RegionID");
    config.HasOne(t => t.Manager).WithMany(t => t.Employees).HasForeignKey(t => t.ReportsTo);
    config.HasMany(e => e.Customers)
          .WithMany(c => c.Employees)
          .UsingEntity<Order>(o => o.HasOne(a => a.Customer).WithMany(y => y.Orders), 
                           o => o.HasOne(a => a.Employee).WithMany( x => x.Orders));
}

/// <summary>Defines the mapping information for the entity 'Order'</summary>
/// <param name="config">The configuration to modify.</param>
protected virtual void MapOrder(EntityTypeBuilder<Order> config)
{
    config.ToTable("Orders");
    config.HasKey(t => t.OrderId);
    config.Property(t => t.OrderId).HasColumnName("OrderID").ValueGeneratedOnAdd();
    config.Property(t => t.CustomerId).HasColumnName("CustomerID").HasMaxLength(5);
    config.Property(t => t.EmployeeId).HasColumnName("EmployeeID");
        //...
    config.HasOne(t => t.Customer).WithMany(t => t.Orders).HasForeignKey(t => t.CustomerId);
    config.HasOne(t => t.Employee).WithMany(t => t.Orders).HasForeignKey(t => t.EmployeeId);
}

[FransBouma]

Btw:
The example given currently:

modelBuilder
    .Entity<Post>()
    .HasMany(p => p.Tags)
    .WithMany(p => p.Posts)
    .UsingEntity(j => j.ToTable("PostTags"));

doesn't work if 'PostTags' is an existing table, and doesn't obey the unwritten rules for how the pk/fk fields have to be named in that table. This is understandable of course, but the error might be puzzling why it fails. (so in the case of an existing table to be used as intermediate table it might be better to suggest to map the intermediate entity explicitly.