Cannot configure Many to Many Relationship correctly

[Angelinsky7]

I'm trying to follow the microsoft doc for configuring many to many relationships : https://learn.microsoft.com/en-us/ef/core/modeling/relationships/many-to-many

The creation of the table is not correct if i specify a ManyToMany configuration with UsingEntity and left + right configuration...

I hope i didn't miss anything... Thanks !

Include your code

public class Post {
    public int Id { get; set; }

    public List<PostTag> PostTags { get; } = new List<PostTag>();
    public List<Tag> Tags { get; } = new List<Tag>();
}

public class Tag {
    public int Id { get; set; }

    public List<PostTag> PostTags { get; } = new List<PostTag>();
    public List<Post> Posts { get; } = new List<Post>();
}

public class PostTag {
    public int PostId { get; set; }
    public int TagId { get; set; }
    public Post Post { get; set; } = null!;
    public Tag Tag { get; set; } = null!;
}

Working example :

protected override void OnModelCreating(ModelBuilder modelBuilder) {
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Post>()
            .HasMany(e => e.Tags)
            .WithMany(e => e.Posts)
            .UsingEntity<PostTag>();
}

create this :

CREATE TABLE [PostTag] (
          [PostId] int NOT NULL,
          [TagId] int NOT NULL,
          CONSTRAINT [PK_PostTag] PRIMARY KEY ([PostId], [TagId]),
          CONSTRAINT [FK_PostTag_Posts_PostId] FOREIGN KEY ([PostId]) REFERENCES [Posts] ([Id]) ON DELETE CASCADE,
          CONSTRAINT [FK_PostTag_Tags_TagId] FOREIGN KEY ([TagId]) REFERENCES [Tags] ([Id]) ON DELETE CASCADE
);

NOT Working example:

protected override void OnModelCreating(ModelBuilder modelBuilder) {
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Post>()
      .HasMany(e => e.Tags)
      .WithMany(e => e.Posts)
      .UsingEntity<PostTag>(
          l => l.HasOne<Tag>().WithMany().HasForeignKey(e => e.TagId),
          r => r.HasOne<Post>().WithMany().HasForeignKey(e => e.PostId));
}

create this :

CREATE TABLE [PostTag] (
    [PostId] int NOT NULL,
    [TagId] int NOT NULL,
    [PostId1] int NOT NULL,
    [TagId1] int NOT NULL,
    CONSTRAINT [PK_PostTag] PRIMARY KEY ([PostId], [TagId]),
    CONSTRAINT [FK_PostTag_Posts_PostId] FOREIGN KEY ([PostId]) REFERENCES [Posts] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_PostTag_Posts_PostId1] FOREIGN KEY ([PostId1]) REFERENCES [Posts] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_PostTag_Tags_TagId] FOREIGN KEY ([TagId]) REFERENCES [Tags] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_PostTag_Tags_TagId1] FOREIGN KEY ([TagId1]) REFERENCES [Tags] ([Id]) ON DELETE CASCADE
);

with this kind of error : Introducing FOREIGN KEY constraint 'FK_PostTag_Posts_PostId1' on table 'PostTag' may cause cycles or multiple cascade paths. Specify ON DELETE NO ACTION or ON UPDATE NO ACTION, or modify other FOREIGN KEY constraints.

And if we try to overcome the cascade delete...

protected override void OnModelCreating(ModelBuilder modelBuilder) {
    base.OnModelCreating(modelBuilder);

    modelBuilder.Entity<Post>()
    .HasMany(e => e.Tags)
    .WithMany(e => e.Posts)
    .UsingEntity<PostTag>(
        l => l.HasOne<Tag>().WithMany().HasForeignKey(e => e.TagId).OnDelete(DeleteBehavior.NoAction),
        r => r.HasOne<Post>().WithMany().HasForeignKey(e => e.PostId).OnDelete(DeleteBehavior.NoAction),
        j => {
            j.HasKey(p => new { p.PostId, p.TagId });
        });
}

create this :

CREATE TABLE [PostTag] (
    [PostId] int NOT NULL,
    [TagId] int NOT NULL,
    [PostId1] int NOT NULL,
    [TagId1] int NOT NULL,
    CONSTRAINT [PK_PostTag] PRIMARY KEY ([PostId], [TagId]),
    CONSTRAINT [FK_PostTag_Posts_PostId] FOREIGN KEY ([PostId]) REFERENCES [Posts] ([Id]),
    CONSTRAINT [FK_PostTag_Posts_PostId1] FOREIGN KEY ([PostId1]) REFERENCES [Posts] ([Id]) ON DELETE CASCADE,
    CONSTRAINT [FK_PostTag_Tags_TagId] FOREIGN KEY ([TagId]) REFERENCES [Tags] ([Id]),
    CONSTRAINT [FK_PostTag_Tags_TagId1] FOREIGN KEY ([TagId1]) REFERENCES [Tags] ([Id]) ON DELETE CASCADE
);

Include example code

TestEfCoreManyToManyBug.zip

Include provider and version information

EF Core version: 7.0.15 Database provider: Microsoft.EntityFrameworkCore.SqlServer: 7.0.15 Target framework: NET 7.0 Operating system: Window 11 IDE: Visual Studio 2022 17.4

[ajcvickers]

@Angelinsky7 The navigations to and from the join entity need to be used in this call:

modelBuilder.Entity<Post>()
      .HasMany(e => e.Tags)
      .WithMany(e => e.Posts)
      .UsingEntity<PostTag>(
          l => l.HasOne<Tag>().WithMany().HasForeignKey(e => e.TagId),
          r => r.HasOne<Post>().WithMany().HasForeignKey(e => e.PostId));

For example:

modelBuilder.Entity<Post>()
      .HasMany(e => e.Tags)
      .WithMany(e => e.Posts)
      .UsingEntity<PostTag>(
          l => l.HasOne<Tag>(e => e.Tag).WithMany(e => e.PostTags).HasForeignKey(e => e.TagId),
          r => r.HasOne<Post>(e => e.Post).WithMany(e => e.PostTags).HasForeignKey(e => e.PostId));

[Angelinsky7]

@ajcvickers oh ! wahou ! Thank you so much ! I'm sorry for the noise (i should have known). Where can i ask to make the change in the documentation ? (for the next one)

[ajcvickers]

@Angelinsky7 The example is already in the docs: https://learn.microsoft.com/en-us/ef/core/modeling/relationships/many-to-many#many-to-many-with-navigations-and-changed-foreign-keys