Don't add grouping key to projection when Distinct is applied

[apolloLegends]

Background

I am upgrading my solution from .NET 5.0.13 to .NET 6.0.5. However, I'm blocked by incorrectly generated SQL that i have been unable to find a workaround for.

I have checked numerous open and closed cases and have not found something that matches my problem.

Problem

-- NET 5.0.13 generated code

exec sp_executesql N'SELECT TOP(@__p_0) [t].[JSON]
FROM [TableData] AS [t]
INNER JOIN (
    SELECT DISTINCT [i].[Parcel]
    FROM [IndexData] AS [i]
    WHERE [i].[Parcel] = N''some condition''
    GROUP BY [i].[Parcel], [i].[RowId]
    HAVING COUNT(*) = 1
) AS [t0] ON [t].[ParcelNumber] = [t0].[Parcel]
WHERE [t].[TableId] = 123
ORDER BY [t].[ParcelNumber]',N'@__p_0 int',@__p_0=123456

-- NET 6.0.1 and 6.0.5 generated code

exec sp_executesql N'SELECT TOP(@__p_0) [t].[JSON]
FROM [TableData] AS [t]
INNER JOIN (
    SELECT DISTINCT [i].[Parcel], [i].[RowId]
    FROM [IndexData] AS [i]
    WHERE [i].[Parcel] = N''some condition''
    GROUP BY [i].[Parcel], [i].[RowId]
    HAVING COUNT(*) = 1
) AS [t0] ON [t].[ParcelNumber] = [t0].[Parcel]
WHERE [t].[TableId] = 123
ORDER BY [t].[ParcelNumber]',N'@__p_0 int',@__p_0=
123456

For clarity, [i].[RowId] should not be in the generated SQL.

Cause

The above SQL is being generated by this code:

var queryResults = (from i in indexResults
                    group i by new { i.ParcelNumber, i.RowId } into grp
                    where grp.Count() == 1
                    select grp.Key.ParcelNumber).Distinct();

var jsonLookup = (from dcv in db.TableData.Where(a => a.TableId == 123)
                  join wos in queryResults
                  on dcv.ParcelNumber equals wos
                  orderby dcv.ParcelNumber
                  select dcv.JSON).Take(123456);

var jsonData = jsonLookup.ToList();

If queryResults is converted to a list such as:

var queryResults = (from i in indexResults
                    group i by new { i.ParcelNumber, i.RowId } into grp
                    where grp.Count() == 1
                    select grp.Key.ParcelNumber).Distinct().ToList();

**It doesnt include the RowId in the query so the problem is somehow related to the join and/or the distinct**

SELECT DISTINCT [i].[Parcel]
FROM [IndexData] AS [i]
WHERE [i].[Parcel] = N'some condition'
GROUP BY [i].[Parcel], [i].[RowId]
HAVING COUNT(*) = 1

Sample

I have attached a simple repro project

• Create a local SQL database called LinqToSQLBug1 using integrated security
• Apply migrations
• Run, review the single generated SQL command in SQL Server Profiler

PP.Core6.SqlBug1.zip

Include provider and version information

EF Core version: 6.0.1 and 6.0.5
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: .NET 6.0
Operating system: Windows 11
IDE: Visual Studio 2022 17.1.6

Thank you!

[AndriySvyryd]

@smitpatel

[apolloLegends]

Thanks for the quick resolution.

Will this be part of 6.0.6?

[ajcvickers]

Re-open to discuss patching.

[ajcvickers]

Note from triage: we will prepare a 6.0 patch for this.