Invalid singularization when scaffolding a manytomany table

[MariovanZeist]

Assume the following Database:

CREATE TABLE Blogs (Id int IDENTITY CONSTRAINT [PK_Blogs] PRIMARY KEY,) 
go 

CREATE TABLE Posts (Id int IDENTITY CONSTRAINT [PK_Posts] PRIMARY KEY,) 
go 

CREATE TABLE BlogPosts (
  BlogId int NOT NULL CONSTRAINT [FK_BlogPosts_Blogs] REFERENCES Blogs ON DELETE CASCADE,
  PostId int NOT NULL CONSTRAINT [FK_BlogPosts_Posts] REFERENCES Posts ON DELETE CASCADE,
  CONSTRAINT [PK_BlogPosts ] PRIMARY KEY (BlogId, PostId)
)
go 

When scaffolding in EF7 the following Configuration will be created.

 modelBuilder.Entity<Blog>(entity =>
 {
     entity.HasMany(d => d.Posts).WithMany(p => p.Blogs)
         .UsingEntity<Dictionary<string, object>>(
             "BlogPost",       //<-note the missing s from the original Tablename(BlogPosts)
             r => r.HasOne<Post>().WithMany()
                 .HasForeignKey("PostId")
                 .HasConstraintName("FK_BlogPosts_Posts"),
             l => l.HasOne<Blog>().WithMany()
                 .HasForeignKey("BlogId")
                 .HasConstraintName("FK_BlogPosts_Blogs"),
             j =>
             {
                 j.HasKey("BlogId", "PostId").HasName("PK_BlogPosts ");
                 //The following line of code is NOT generated (but was generated in earlier versions) and might be what is actually missing here
                 //j.ToTable("BlogPosts");
             });
 });

When executing the following statement:

  var allposts = await ctx.Blogs.SelectMany(b => b.Posts).ToArrayAsync();

The following SQL will be generated:

SELECT [t].[Id]
FROM [Blogs] AS [b]
INNER JOIN (
    SELECT [p].[Id], [b0].[BlogId]
    FROM [BlogPost] AS [b0]    --  <-Invalid table name
    INNER JOIN [Posts] AS [p] ON [b0].[PostId] = [p].[Id]
) AS [t] ON [b].[Id] = [t].[BlogId]

resulting in
Microsoft.Data.SqlClient.SqlException: 'Invalid object name 'BlogPost'.'

EF Core version: 7.0
Database provider: (e.g. Microsoft.EntityFrameworkCore.SqlServer)
Target framework: (e.g. .NET 7.0)
Operating system:
IDE: (e.g. Visual Studio 2022 17.4)

[ErikEJ]

Is you model configuration being loaded?

Which command do you run exactly?

[MariovanZeist]

@ErikEJ
I am running the following command:
dotnet ef dbcontext scaffold "Data Source=localhost;Initial Catalog=EFCoreBugs;Integrated Security=True;Encrypt=False" Microsoft.EntityFrameworkCore.SqlServer -f --no-onconfiguring --no-build -c EFDbContext

And if you mean by if my model get's loaded that the above configuration is used, then yes it is.

also running dotnet-ef --version
will return: 7.0.0

[ErikEJ]

Thanks, I am able to repro

[ajcvickers]

/cc @bricelam We should consider patching this.

[ajcvickers]

Note from triage: prepare a patch for this.

[taipignas]

not sure if ite helps, but i have this issue too. after upgrading from ef core 6 to ef core 7 scaffolding generates a different result.
j.ToTable("TableName");
got replace with
j.ToTable(tb =>
{
tb.HasTrigger("TableName_some_trigger"); //we use triggers
});

some queries stopped working because of this change.

[bricelam]

@taipignas Can you file a new issue with more information? That looks like a different bug.

[bricelam]

🩹 Workaround

Unfortunately, the only good workaround here is to manually add the ToTable call after the code is generated.

[MariovanZeist]

@bricelam As a workaround for this issue and some others (#29563 for example), I turned to the new T4 templates. (awesome addition to efcore)
They were a live saver as they allowed me to circumvent these issues and allowed me to migrate to net7.