LINQ query generates incorrect SQL (mismatched aliases)

[drysart]

Details

The following query generates invalid SQL:

var q =
	from bm in ctx.Context.BucketMessage
	where EF.Functions.Contains(bm.MessageText, "xreport")
	orderby bm.Timestamp descending
	select bm.Id;

var q2 =
	from messageId in q.Take(10)
	from bmr in ctx.Context.GetBucketMessageSurroundingContext(messageId, 5, 5)
	select bmr;

var xq =
	from bmr in q2
	join character in ctx.Context.Character on bmr.CharacterId equals character.Id 
	join bmk in ctx.Context.BucketMessageKind on bmr.BucketMessageKindId equals bmk.Id
	orderby bmr.Timestamp descending
	select new LogViewLineModel()
	{
		LineKind = bmk.Description,
		Character = new CharacterInfoModel()
		{
			Name = character.Name,
			Gender = (from cs in ctx.Context.CharacterStatus
						where cs.CharacterId == bmr.CharacterId && cs.Timestamp <= bmr.Timestamp
						orderby cs.Timestamp descending
						select cs.Gender.Description).Take(1).FirstOrDefault() ?? "none"
		},
		Timestamp = bmr.Timestamp,
		MessageText = bmr.MessageText
	};

System.Diagnostics.Debug.WriteLine(xq.ToQueryString());

In this query, GetBucketMessageSurroundingContext is a user-defined table-valued function.

The SQL generated by this query is as follows:

DECLARE @__p_1 int = 10;

SELECT [b0].[Description] AS [LineKind], [c0].[Name], COALESCE((
    SELECT TOP(1) [g].[Description]
    FROM (
        SELECT TOP(1) [c].[Id], [c].[CharacterId], [c].[GenderId], [c].[OnlineStatusId], [c].[StatusMessage], [c].[Timestamp]
        FROM [fllog].[characterstatus] AS [c]
        WHERE ([c].[CharacterId] = [g].[CharacterId]) AND ([c].[Timestamp] <= [g].[Timestamp])
        ORDER BY [c].[Timestamp] DESC
    ) AS [t]
    INNER JOIN [fllog].[gender] AS [g] ON [t].[GenderId] = [g].[Id]
    ORDER BY [t].[Timestamp] DESC), N'none') AS [Gender], CAST([g].[Timestamp] AS datetimeoffset) AS [Timestamp], [g].[MessageText]
FROM (
    SELECT TOP(@__p_1) [b].[Id]
    FROM [fllog].[bucketmessage] AS [b]
    WHERE CONTAINS([b].[MessageText], N'xreport')
    ORDER BY [b].[Timestamp] DESC
) AS [t0]
CROSS APPLY [fllog].[GetBucketMessageSurroundingContext]([t0].[Id], 5, 5) AS [g0]
INNER JOIN [fllog].[character] AS [c0] ON [g].[CharacterId] = [c0].[Id]
INNER JOIN [fllog].[bucketmessagekind] AS [b0] ON [g].[BucketMessageKindId] = [b0].[Id]
ORDER BY [g].[Timestamp] DESC

Specifically, the issue here is that the subquery in the result gets the alias [g] on its join to the Gender table; the cross apply to the user-defined function uses the alias [g0] at the point of the cross apply (presumably to avoid a conflict on the alias name), but all other references to columns returned by that function improperly refer to it with the alias [g].

Version information

EF Core version: 5.0.3
Database provider: Microsoft.EntityFrameworkCore.SqlServer 5.0.3
Target framework: .NET 5.0
Operating system: Windows 10 19042.804
IDE: Visual Studio 2019 Professional 16.8.5

[smitpatel]

We need a runnable repro code for us to test or add a regression test for the scenario. We have fixed quite a lot of similar issues so this should be working now.

[drysart]

This repo has a minimal repro. Verified incorrect SQL generation on both EF Core 5.0.4 and 6.0.0-preview.2.21154.2.

[smitpatel]

@drysart - Thanks for the repro code. I verified that this works in #24491 and added a regression test using same model and query.