CLI dbscaffold not correctly scaffolding many-to-many relationships

[cbrenberg]

I'm in the process of upgrading a project from EF5 to EFCore 6.0.9. According to the docs and Issue #22475, simple join tables should be mapped directly as a many to many relationship between the two tables being joined. This is not the behavior I'm seeing. Instead, all navigation/join tables are being scaffolded as their own entity types. Why is the scaffolding tool creating a separate entity type for the LicensedUserModules join table in the example below?

For example, we have a table 'LicensedUsers' with a many to many relationship to an 'ApplicationModules' table that's defined by a simple join table 'LicensedUserModules' that contains two columns: 'LicensedUserID' (FK to LicensedUsers) and 'ApplicationModuleID' (FK to ApplicationModules). Both columns comprise the PK for this join table.

After running the 'dotnet ef dbscaffold' command, I would expect the LicensedUser type to have a collection of ApplicationModules, but instead it has a collection of LicensedUserModules.

Here's the scaffolded context for the LicensedUser table:

modelBuilder.Entity<LicensedUser>(entity =>
            {
                entity.Property(e => e.LicensedUserId).HasColumnName("LicensedUserID");

                entity.Property(e => e.LastActivityTimeUtc).HasColumnType("datetime");

                entity.Property(e => e.LicenseId).HasColumnName("LicenseID");

                entity.Property(e => e.SeatActivityTimeUtc).HasColumnType("datetime");

                entity.Property(e => e.UserId)
                    .IsRequired()
                    .HasMaxLength(128)
                    .HasColumnName("UserID");

                entity.Property(e => e.UserTypeId)
                    .HasColumnName("UserTypeID")
                    .HasDefaultValueSql("((1))");

                entity.HasOne(d => d.License)
                    .WithMany(p => p.LicensedUsers)
                    .HasForeignKey(d => d.LicenseId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_LicensedUsers_Licenses");

                entity.HasOne(d => d.UserType)
                    .WithMany(p => p.LicensedUsers)
                    .HasForeignKey(d => d.UserTypeId)
                    .OnDelete(DeleteBehavior.ClientSetNull)
                    .HasConstraintName("FK_LicensedUsers_UserTypes");
            });

And the LicensedUser entity type:

public partial class LicensedUser
    {
        public LicensedUser()
        {
            LicensedUserModules = new HashSet<LicensedUserModule>();
            LicensedUserPermissions = new HashSet<LicensedUserPermission>();
            SubscriptionAllocations = new HashSet<SubscriptionAllocation>();
        }

        public Guid LicenseId { get; set; }
        public string UserId { get; set; }
        public DateTime? LastActivityTimeUtc { get; set; }
        public int LicensedUserId { get; set; }
        public int UserTypeId { get; set; }
        public DateTime? SeatActivityTimeUtc { get; set; }

        public virtual License License { get; set; }
        public virtual UserType UserType { get; set; }
        public virtual ICollection<LicensedUserModule> LicensedUserModules { get; set; }
        public virtual ICollection<LicensedUserPermission> LicensedUserPermissions { get; set; }
        public virtual ICollection<SubscriptionAllocation> SubscriptionAllocations { get; set; }
    }

EF Core version: 6.0.9
Database provider: Microsoft.EntityFrameworkCore.SqlServer 6.0.9
Entity Framework Core .NET Command-line Tools version: 6.0.9
Target framework: .NET 6.0
Operating system: Windows 10

[ajcvickers]

@cbrenberg Can you post the SQL for the tables you are scaffolding from so that we can investigate?

[cbrenberg]

Sure, here's the SQL for these three tables (as generated by SQL Server Management Studio, with nonrelevant portions removed for clarity):

CREATE TABLE [dbo].[LicensedUsers](
	[LicenseID] [uniqueidentifier] NOT NULL,
	[UserID] [nvarchar](128) NOT NULL,
	[LastActivityTimeUtc] [datetime] NULL,
	[LicensedUserID] [int] IDENTITY(1,1) NOT NULL,
	[UserTypeID] [int] NOT NULL,
	[SeatActivityTimeUtc] [datetime] NULL,
 CONSTRAINT [PK_LicensedUsers] PRIMARY KEY CLUSTERED 
(
	[LicensedUserID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

CREATE TABLE [dbo].[ApplicationModules](
	[ApplicationModuleID] [int] NOT NULL,
	[ApplicationID] [uniqueidentifier] NOT NULL,
	[Name] [nvarchar](100) NOT NULL,
 CONSTRAINT [PK_ApplicationModules] PRIMARY KEY CLUSTERED 
(
	[ApplicationModuleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]

--JOIN TABLE: LicensedUserModules
CREATE TABLE [dbo].[LicensedUserModules](
	[LicensedUserID] [int] NOT NULL,
	[ApplicationModuleID] [int] NOT NULL,
 CONSTRAINT [PK_LicensedUserModules] PRIMARY KEY CLUSTERED 
(
	[LicensedUserID] ASC,
	[ApplicationModuleID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[LicensedUserModules]  WITH CHECK ADD  CONSTRAINT [FK_LicensedUserModules_ApplicationModules] FOREIGN KEY([ApplicationModuleID])
REFERENCES [dbo].[ApplicationModules] ([ApplicationModuleID])
GO

ALTER TABLE [dbo].[LicensedUserModules] CHECK CONSTRAINT [FK_LicensedUserModules_ApplicationModules]
GO

ALTER TABLE [dbo].[LicensedUserModules]  WITH CHECK ADD  CONSTRAINT [FK_LicensedUserModules_LicensedUsers] FOREIGN KEY([LicensedUserID])
REFERENCES [dbo].[LicensedUsers] ([LicensedUserID])
GO

ALTER TABLE [dbo].[LicensedUserModules] CHECK CONSTRAINT [FK_LicensedUserModules_LicensedUsers]
GO

[ajcvickers]

/cc @smitpatel

[smitpatel]

Sql script wise it should not generate join table. Can you verify if you are using 6.0 packages? Since this is tooling on command line, you would need to upgrade the packages in your project (upgrading CLI tool alone won't work).

[cbrenberg]

There's another project within this solution that still has a reference to EF 6.4.4, but that shouldn't be an issue here since I pointed the CLI tool at the csproj for a different project. The project that I'm upgrading is using Microsoft.EntityFrameworkCore.Design v6.0.9 and Microsoft.EntityFrameworkCore.SqlServer v6.0.9 (see screenshot below)

[ajcvickers]

@cbrenberg When I create those tables and then scaffold with:

dotnet ef dbcontext scaffold "Data Source=(LocalDb)\MSSQLLocalDB;Database=AllTogetherNow" Microsoft.EntityFrameworkCore.SqlServer

I get the many-to-many relationship as expected; see below.

Can you post the full --verbose output from running the scaffolding command?

public partial class ApplicationModule
{
    public ApplicationModule()
    {
        LicensedUsers = new HashSet<LicensedUser>();
    }

    public int ApplicationModuleId { get; set; }
    public Guid ApplicationId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<LicensedUser> LicensedUsers { get; set; }
}

public partial class LicensedUser
{
    public LicensedUser()
    {
        ApplicationModules = new HashSet<ApplicationModule>();
    }

    public Guid LicenseId { get; set; }
    public string UserId { get; set; }
    public DateTime? LastActivityTimeUtc { get; set; }
    public int LicensedUserId { get; set; }
    public int UserTypeId { get; set; }
    public DateTime? SeatActivityTimeUtc { get; set; }

    public virtual ICollection<ApplicationModule> ApplicationModules { get; set; }
}

public partial class AllTogetherNowContext : DbContext
{
    public AllTogetherNowContext()
    {
    }

    public AllTogetherNowContext(DbContextOptions<AllTogetherNowContext> options)
        : base(options)
    {
    }

    public virtual DbSet<ApplicationModule> ApplicationModules { get; set; }
    public virtual DbSet<LicensedUser> LicensedUsers { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        if (!optionsBuilder.IsConfigured)
        {
#warning To protect potentially sensitive information in your connection string, you should move it out of source code. You can avoid scaffolding the connection string by using the Name= syntax to read it from configuration - see https://go.microsoft.com/fwlink/?linkid=2131148. For more guidance on storing connection strings, see http://go.microsoft.com/fwlink/?LinkId=723263.
            optionsBuilder.UseSqlServer("Data Source=(LocalDb)\\MSSQLLocalDB;Database=AllTogetherNow");
        }
    }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<ApplicationModule>(entity =>
        {
            entity.Property(e => e.ApplicationModuleId)
                .ValueGeneratedNever()
                .HasColumnName("ApplicationModuleID");

            entity.Property(e => e.ApplicationId).HasColumnName("ApplicationID");

            entity.Property(e => e.Name)
                .IsRequired()
                .HasMaxLength(100);
        });

        modelBuilder.Entity<LicensedUser>(entity =>
        {
            entity.Property(e => e.LicensedUserId).HasColumnName("LicensedUserID");

            entity.Property(e => e.LastActivityTimeUtc).HasColumnType("datetime");

            entity.Property(e => e.LicenseId).HasColumnName("LicenseID");

            entity.Property(e => e.SeatActivityTimeUtc).HasColumnType("datetime");

            entity.Property(e => e.UserId)
                .IsRequired()
                .HasMaxLength(128)
                .HasColumnName("UserID");

            entity.Property(e => e.UserTypeId).HasColumnName("UserTypeID");

            entity.HasMany(d => d.ApplicationModules)
                .WithMany(p => p.LicensedUsers)
                .UsingEntity<Dictionary<string, object>>(
                    "LicensedUserModule",
                    l => l.HasOne<ApplicationModule>().WithMany().HasForeignKey("ApplicationModuleId").OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_LicensedUserModules_ApplicationModules"),
                    r => r.HasOne<LicensedUser>().WithMany().HasForeignKey("LicensedUserId").OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("FK_LicensedUserModules_LicensedUsers"),
                    j =>
                    {
                        j.HasKey("LicensedUserId", "ApplicationModuleId");

                        j.ToTable("LicensedUserModules");

                        j.IndexerProperty<int>("LicensedUserId").HasColumnName("LicensedUserID");

                        j.IndexerProperty<int>("ApplicationModuleId").HasColumnName("ApplicationModuleID");
                    });
        });

        OnModelCreatingPartial(modelBuilder);
    }

    partial void OnModelCreatingPartial(ModelBuilder modelBuilder);
}

[cbrenberg]

After trying it again with the verbose output, I may have found the issue. I've been running the scaffolding command from a .bat file a colleague put together, and I just noticed that it uses the --no-build flag. The verbose output showed that the CLI tool was referencing a *.deps.json file in the project's 'bin' folder, which still contains an out of date build from before I started the upgrade process, so that deps file is still pointing to EFCore v5.0.17, which I assume means that my code is being scaffolded by EFCore 5 instead of EFCore 6, which would explain the behavior I've been seeing this whole time.

I'll need to fix several build errors before I can test this out again and verify whether that outdated build is the root cause of my issues, but I'll keep you posted here once I find out one way or the other.

[cbrenberg]

Good news! I was able to generate a successful build after upgrading the project to .NET 6 and EntityFrameworkCore 6.0.9, and then the scaffolding tool skipped the join table as expected. Thanks so much for the time you both you spent looking into this.

Since the problem has been resolved, I'll go ahead and close out this issue.