Query with n:m relation adds non-existent column

[janschreier]

#n:m relation query statements build incorrectly

when including data from a n:m table the SQL generated includes a non-existent column, resulting in a failing SQL statement
the below code is created by dbScaffold (DDL statements at the bottom)

 var dbContext = new myContext();
 var y = dbContext.Crashtest.
    Include(m => m.Karosserieform)
    .ToList();

this builds the following SQL statement which fails because there is no CrashtestId1 column

SELECT
  [c].[CrashtestId]
 ,[c].[Bezeichnung]
 ,[t].[CrashtestId]
 ,[t].[KarosserieformId]

 ,[t].[CrashtestId1] ''<---- this line should not exist

 ,[t].[KarosserieformId0]
 ,[t].[Bezeichnung]
FROM [Crashtest] AS [c]
LEFT JOIN (SELECT
    [c0].[CrashtestId]
   ,[c0].[KarosserieformId]

   ,[c0].[CrashtestId1] ''<---- this line should not exist

   ,[k].[KarosserieformId] AS [KarosserieformId0]
   ,[k].[Bezeichnung]
  FROM [CrashtestKarosserieform] AS [c0]
  INNER JOIN [Karosserieform] AS [k]
    ON [c0].[KarosserieformId] = [k].[KarosserieformId]) AS [t]
  ON [c].[CrashtestId] = [t].[CrashtestId]
ORDER BY [c].[CrashtestId], [t].[CrashtestId], [t].[KarosserieformId]

Statement used for scaffolding

dotnet ef dbcontext scaffold "data source=localhost;initial catalog=dbname;integrated security=True;MultipleActiveResultSets=True;App=EntityFramework" Microsoft.EntityFrameworkCore.SqlServer `
--output-dir "database" --table dbo.Crashtest --table dbo.CrashtestKarosserieform --table dbo.Karosserieform --data-annotations --use-database-names --force  --no-pluralize 

DDL-Statements:

CREATE TABLE dbo.Crashtest (
  CrashtestId INT IDENTITY
 ,Bezeichnung NVARCHAR(50) NULL
 ,CONSTRAINT pkct PRIMARY KEY CLUSTERED (CrashtestId)
) ON [PRIMARY] 
GO

CREATE TABLE dbo.Karosserieform (
  KarosserieformId INT IDENTITY
 ,Bezeichnung NVARCHAR(255) NULL
 ,CONSTRAINT pk_kf PRIMARY KEY CLUSTERED (KarosserieformId)
) ON [PRIMARY]
GO

CREATE TABLE dbo.CrashtestKarosserieform (
  CrashtestId INT NOT NULL
 ,KarosserieformId INT NOT NULL
 ,CONSTRAINT pk_link PRIMARY KEY CLUSTERED (CrashtestId, KarosserieformId)
) ON [PRIMARY]
GO

ALTER TABLE dbo.CrashtestKarosserieform
ADD CONSTRAINT fk_kf FOREIGN KEY (KarosserieformId) REFERENCES dbo.Karosserieform (KarosserieformId)
GO

ALTER TABLE dbo.CrashtestKarosserieform
ADD CONSTRAINT fk_ct FOREIGN KEY (CrashtestId) REFERENCES dbo.Crashtest (CrashtestId) ON DELETE CASCADE
GO

provider and version information

EF Core version: 6.0
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 6.0
Operating system: win 10
IDE: (e.g. Visual Studio 20202 current)

[DevTrevi]

I had the same issue when I updated my project from efcore5 to efcore6.
In my case, I was using Fluent Api for configuring relationships, and I inadvertitely configured the same 1..N relationship twice (on both the parent and the child entity side):

// On Order (parent):

// This was a duplicate, removing it solved the problem
// The "phantom" property for me was named OrderRowId, that was wrong anyway as it ignored the explicit mapping specified with .HasForeignKey()
builder.HasMany(e => e.DetailsPrice).WithOne()
                .HasForeignKey(e => e.OrderNumber).HasPrincipalKey(e => e.OrderNumber);
...

// On Detail: (the one that i kept in my mapping)
builder.HasOne(e => e.Order).WithMany(e => e.DetailsPrice)
                .HasForeignKey(e => e.OrderNumber).HasPrincipalKey(e => e.OrderNumber);

Please check your mapping, maybe the scaffolder made a similar mistake

[janschreier]

Thanks for looking into this. I didn't touch the Model code at all here's what was scaffolded:

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    modelBuilder.UseCollation("SQL_Latin1_General_CP1_CI_AS");

    modelBuilder.Entity<Crashtest>(entity =>
    {
        entity.HasMany(d => d.Karosserieform)
            .WithMany(p => p.Crashtest)
            .UsingEntity<Dictionary<string, object>>(
                "CrashtestKarosserieform",
                l => l.HasOne<Karosserieform>().WithMany().HasForeignKey("KarosserieformId").OnDelete(DeleteBehavior.ClientSetNull).HasConstraintName("fk_kf"),
                r => r.HasOne<Crashtest>().WithMany().HasForeignKey("CrashtestId").HasConstraintName("fk_ct"),
                j =>
                {
                    j.HasKey("CrashtestId", "KarosserieformId").HasName("pk_link");

                    j.ToTable("CrashtestKarosserieform");
                });
    });

    OnModelCreatingPartial(modelBuilder);
}

so I don't think this is the issue.

[smitpatel]

Min repro:

public partial class Blog
    {
        public int BlogId { get; set; }
        [ForeignKey("BlogId")]
        public virtual ICollection<Post> Posts { get; set; }
    }

    public partial class Post
    {
        public int PostId { get; set; }
        [ForeignKey("PostId")]
        public virtual ICollection<Blog> Blogs { get; set; }
    }

modelBuilder.Entity<Blog>(entity =>
            {
                entity.HasMany(d => d.Posts)
                    .WithMany(p => p.Blogs)
                    .UsingEntity<Dictionary<string, object>>(
                        "BlogPost",
                        l => l.HasOne<Post>().WithMany().HasForeignKey("PostId"),
                        r => r.HasOne<Blog>().WithMany().HasForeignKey("BlogId"),
                        j =>
                        {
                            j.HasKey("BlogId", "PostId");

                            j.ToTable("BlogPost");
                        });
            });

Remove either FKAttributes or manual configuration and it generates correct model.
With above model config it generates following model

Model:
  EntityType: BlogPost (Dictionary<string, object>) CLR Type: Dictionary<string, object>
    Properties:
      BlogId (no field, int) Indexer Required PK FK AfterSave:Throw
      PostId (no field, int) Indexer Required PK FK Index AfterSave:Throw
      BlogsBlogId (no field, int) Indexer Required
    Keys:
      BlogId, PostId PK
    Foreign keys:
      BlogPost (Dictionary<string, object>) {'BlogId'} -> Blog {'BlogId'} Cascade
      BlogPost (Dictionary<string, object>) {'PostId'} -> Post {'PostId'} Cascade
    Indexes:
      PostId
  EntityType: Blog
    Properties:
      BlogId (int) Required PK AfterSave:Throw ValueGenerated.OnAdd
    Skip navigations:
      Posts (ICollection<Post>) CollectionPost Inverse: Blogs
    Keys:
      BlogId PK
  EntityType: Post
    Properties:
      PostId (int) Required PK AfterSave:Throw ValueGenerated.OnAdd
    Skip navigations:
      Blogs (ICollection<Blog>) CollectionBlog Inverse: Posts
    Keys:
      PostId PK

The join entity has unused property BlogsBlogId

[smitpatel]

cc: @AndriySvyryd

[ajcvickers]

FYI for those impacted by this issue: EF Core 6.0.1 is now available from NuGet.