SQL output can contain navigation properties as columns - EF Core 7

[JanKotschenreuther]

File a bug

After upgrading from EF Core 6 to 7, generated SQL can contain navigation properties as columns.
This behavior leads to SqlException 'Invalid column name 'ColumnName'.

Database Schema

The EdiSource FK points to a non PK column on EdiSourceType.
EdiSourceType.EdiType is not a Id, which is why there is no Id Suffix.

CREATE TABLE dbo.[EdiSourceType]
(
	Id bigint IDENTITY(1,1) NOT NULL,
	EdiType bigint NOT NULL UNIQUE,
	Number nvarchar(100) NULL CONSTRAINT DF_EdiSourceType_RegularColumnName DEFAULT(0),
	CONSTRAINT PK_EdiSourceType PRIMARY KEY CLUSTERED (Id ASC)
)

CREATE TABLE dbo.[EdiSource]
(
	Id bigint IDENTITY(1,1) NOT NULL,
	EdiType bigint NOT NULL,
	Name nvarchar(100) NULL CONSTRAINT DF_EdiSource_RegularColumnName DEFAULT(0),
	CONSTRAINT PK_EdiSource PRIMARY KEY CLUSTERED (Id ASC),
	CONSTRAINT FK_EdiSource_EdiType FOREIGN KEY (EdiType) REFERENCES [EdiSourceType] (EdiType)
)

Database First Script

dotnet `
ef `
dbcontext `
scaffold `
'Data Source=(local)\SQL2019;Initial Catalog=MyTestDb;Integrated Security=True;Multiple Active Result Sets=True;Trust Server Certificate=True;' `
Microsoft.EntityFrameworkCore.SqlServer `
--no-onconfiguring `
--no-pluralize `
--use-database-names `
--data-annotations `
--project ModelLib.csproj `
--namespace ModelLib.Test.Model `
--context TestModelContext `
--context-namespace ModelLib.Test.Model `
--context-dir TestModelContext `
--output-dir TestModelContext\Models `
 `
--table EdiSource `
--table EdiSourceType `
 `
--force

Error reproduction

var options = new DbContextOptionsBuilder<TestModelContext>()
    .UseSqlServer("Data Source=(local)\\SQL2019;Initial Catalog=MyTestDb;Integrated Security=True;Multiple Active Result Sets=True;Trust Server Certificate=True;")
    .Options;

using var ctx = new TestModelContext(options);

//At debug-time query.DebugView.Query will already show the problematic SQL.
var query = ctx.EdiSource.Take(10);

//Materializing throws an Error
var source = query.FirstOrDefault();

Expected SQL (like in EF Core 6):

DECLARE @__p_0 int = 10;

SELECT TOP(@__p_0) [e].[Id], [e].[EdiType], [e].[Name]
FROM [EdiSource] AS [e]

Current SQL (since EF Core 7):

EdiTypeNavigationEdiType is part of the selection, which is no column and causes an exception.

DECLARE @__p_0 int = 10;

SELECT TOP(@__p_0) [e].[Id], [e].[EdiType], [e].[EdiTypeNavigationEdiType], [e].[Name]
FROM [EdiSource] AS [e]

Suggestions

I did not find anything in the documented Breaking Changes about this.

I tried adding annotations like [ForeignKey] and [InverseProperty], which did not change the behavior.
The configuration section in the scaffolded DbContext seems to be fine.

Would be nice if this issue can be fixed.
But I also need a quick fix, so i would appreciate if you got any workaround, like renaming columns or anything alike.

I have been wondering if there is a problem with the refernced column not being a PK or if a missing Id-Suffix could cause this problem.

I am also not sure if this is also a problem on other type of operations like SaveChanges.

Include provider and version information

EF Core version: 7.0.2
Database provider: Microsoft.EntityFrameworkCore.SqlServer 7.0.2
Target framework: .NET 7.0
Operating system: win10-x64
IDE: Visual Studio 2022 17.4.3

[ajcvickers]

Duplicate of #29418.

Workaround is to add in the missing HasForeignKey call. For example:

entity.HasOne(d => d.EdiTypeNavigation).WithMany(p => p.EdiSource)
    .HasForeignKey(d => d.EdiType)
    .HasPrincipalKey(p => p.EdiType)
    .OnDelete(DeleteBehavior.ClientSetNull)
    .HasConstraintName("FK_EdiSource_EdiType");