Specifying a Many-to-many that handles two join tables (that previously handled many-to-manys) w/ entity framework core

I have never been in the situation where I originally had two many-to-many’s that were resolved. But then down another level is another many-to-many on both of those join tables. I am wondering if there is a better way to architect this w/ entity framework core. Here is what I got. I am trying to figure out which Project Owners are part of which Project Products.

I have 3 tables:

Project
Product
Owner

A Project can have many Products and a Project can have many Owners. A Product can have many Projects and an Owner can have many Projects. I resolved these two many-to-many relationships by doing the following:

ProjectProduct
Two keys: ProjectId, ProductId

ProjectOwner
Two keys: ProjectId, OwnerId

Additionally a ProjectProduct can have many ProjectOwners and a ProjectOwner can have many ProjectsProducts.

I thought the solution would be to add a unique Id to both ProjectOwner and ProjectProduct and create a new entity called ProjectProductOwner with the following keys:
ProjectProductId, ProjectOwnerId

Here is what my DBContext looks like:

// Key Specifications
modelBuilder.Entity<ProjectProductOwner>()
    .HasKey(x => new { x.ProjectProductId, x.ProjectOwnerId });

// Project Product
modelBuilder.Entity<ProjectProduct>()
    .HasOne(x => x.Project)
    .WithMany(x => x.Products)
    .HasForeignKey(x => x.ProjectId);

modelBuilder.Entity<ProjectProduct>()
    .HasOne(x => x.Product)
    .WithMany(x => x.Projects)
    .HasForeignKey(x => x.ProductId);

// Project Owner
modelBuilder.Entity<ProjectOwner>()
    .HasOne(x => x.Project)
    .WithMany(x => x.Owners)
    .HasForeignKey(x => x.ProjectId);

modelBuilder.Entity<ProjectOwner>()
    .HasOne(x => x.Owner)
    .WithMany(x => Projects)
    .HasForeignKey(x => x.OwnerId);

// Project Product Owner
modelBuilder.Entity<ProjectProductOwner>()
    .HasOne(x => x.ProjectProduct)
    .WithMany(x => x.ProjectOwners)
    .HasForeignKey(x => x.ProjectProductId);

modelBuilder.Entity<ProjectProductOwner>()
    .HasOne(x => x.ProjectOwner)
    .WithMany(x => x.ProjectProducts)
    .HasForeignKey(x => x.ProjectOwnerId);  

I am getting the error: Introducing FOREIGN KEY constraint ‘FK_ProjectProductOwner_ProjectProducts_ProjectProductId’ on table ‘ProjectProductOwner’ may cause cycles or multiple cascade paths.

additionally:

‘FK_ProjectProductOwner_ProjectOwners_ProjectOwnerId’ on table ‘ProjectProductOwner’ may cause cycles or multiple cascade paths.

Answer

You can “merge” the ProjectProduct with the ProjectProductOwner into one table. Since you add Owners to the Project first, then you add one or more Products for each Owner in the Project I don’t see a need for a third many-to-many Entity, thus simplifying it a bit 🙂

Note: I didn’t even bother with the error, because as you said and I agree, most often these errors show up when your model isn’t correct.

Owner

public class Owner
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<ProjectOwner> ProjectOwners { get; set; }
    public ICollection<ProjectProductOwner> ProjectProductOwners { get; set; }
}

Product

public class Product
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<ProjectProductOwner> ProjectProductOwners { get; set; }
}

Project and many-to-many tables

public class Project
{
    public int Id { get; set; }
    public string Name { get; set; }
    public ICollection<ProjectOwner> ProjectOwners { get; set; }
    public ICollection<ProjectProductOwner> ProjectProductOwners { get; set; }

    public Project()
    {
        ProjectOwners = new List<ProjectOwner>();
        ProjectProductOwners = new List<ProjectProductOwner>();
    }
}

public class ProjectOwner
{
    public int OwnerId { get; set; }
    public Owner Owner { get; set; }
    public int ProjectId { get; set; }
    public Project Project { get; set; }
}

public class ProjectProductOwner
{
    public int ProductId { get; set; }
    public Product Product { get; set; }
    public int OwnerId { get; set; }
    public Owner Owner { get; set; }
    public int ProjectId { get; set; }
    public Project Project { get; set; }
}

DbContext configuration

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    // Configure Owners in a Project
    modelBuilder.Entity<ProjectOwner>()
        .HasKey(p => new { p.ProjectId, p.OwnerId });

    modelBuilder.Entity<ProjectOwner>()
        .HasOne(bc => bc.Project)
        .WithMany(b => b.ProjectOwners)
        .HasForeignKey(bc => bc.ProjectId);

    modelBuilder.Entity<ProjectOwner>()
        .HasOne(bc => bc.Owner)
        .WithMany(c => c.ProjectOwners)
        .HasForeignKey(bc => bc.OwnerId);

    // Configure Products for each owner in a Project
    modelBuilder.Entity<ProjectProductOwner>()
        .HasKey(p => new { p.ProjectId, p.ProductId, p.OwnerId });

    modelBuilder.Entity<ProjectProductOwner>()
        .HasOne(bc => bc.Project)
        .WithMany(b => b.ProjectProductOwners)
        .HasForeignKey(bc => bc.ProjectId);

    modelBuilder.Entity<ProjectProductOwner>()
        .HasOne(bc => bc.Product)
        .WithMany(c => c.ProjectProductOwners)
        .HasForeignKey(bc => bc.ProductId);

    modelBuilder.Entity<ProjectProductOwner>()
        .HasOne(bc => bc.Owner)
        .WithMany(c => c.ProjectProductOwners)
        .HasForeignKey(bc => bc.OwnerId);
}

Finally, you can add new projects and query it with something like this:

using (var db = new ProjectContext())
{
    var project = new Project();
    project.Name = "My project";

    // assumes there's 3 owners and 2 products already inserted in the DB

    // Add the 3 owners to the project
    project.ProjectOwners.Add(new ProjectOwner { OwnerId = 1});
    project.ProjectOwners.Add(new ProjectOwner { OwnerId = 2});
    project.ProjectOwners.Add(new ProjectOwner { OwnerId = 3});

    // Add Product 1 to Owner 1 and 2
    project.ProjectProductOwners.Add(new ProjectProductOwner { ProductId = 1, OwnerId = 1 });
    project.ProjectProductOwners.Add(new ProjectProductOwner { ProductId = 1, OwnerId = 2 });

    // Add Product 2 to Owner 1 and 3
    project.ProjectProductOwners.Add(new ProjectProductOwner { ProductId = 2, OwnerId = 1 });
    project.ProjectProductOwners.Add(new ProjectProductOwner { ProductId = 2, OwnerId = 3 });

    db.Add(project);
    db.SaveChanges();

    var projects = db.Project
        .Include(p => p.ProjectOwners)
        .ThenInclude(p => p.Owner)
        .Include(p => p.ProjectProductOwners)
        .ThenInclude(p => p.Product)
        .FirstOrDefault();
}