Some result operators (e.g. Distinct) after selecting a property generate incorrect SQL if query also contains optional navigation or explicit GroupJoin call

[kierenj]

Same model as #6937, #6938, #7102, #7230.

This code:

            var identities = _context.Set<WidgetManufacturedIdentity>();
            var result = identities.Where(i => i.Manufacturer.Id == id && !i.Manufacturer.Retired.HasValue)
                .Select(i => i.RangeName)
                .Distinct()
                .ToArray()
                .Where(r => !string.IsNullOrEmpty(r))
                .Select(r => new { name = r });

Should generate a distinct set of RangeName property values. Instead, it retrieves all values.

Log output:

info: Microsoft.EntityFrameworkCore.Storage.IRelationalCommandBuilderFactory[1]
      Executed DbCommand (33ms) [Parameters=[@__id_0='?'], CommandType='Text', CommandTimeout='30']
      SELECT DISTINCT [i].[WidgetId], [i].[BrandName], [i].[ManufacturerId], [i].[ProductName], [i].[ProductRef], [i].[RangeName], [i.Manufacturer].[Id], [i.Manufacturer].[Added], [i.Manufacturer].[ListOrder], [i.Manufacturer].[Modified], [i.Manufacturer].[Name], [i.Manufacturer].[Retired]
      FROM [WidgetManufacturedIdentity] AS [i]
      LEFT JOIN [WidgetManufacturer] AS [i.Manufacturer] ON [i].[ManufacturerId] = [i.Manufacturer].[Id]
      WHERE ([i.Manufacturer].[Id] = @__id_0) AND [i.Manufacturer].[Retired] IS NULL

As you can see, the DISTINCT is there, but the argument list to the SELECT is not limited to the select list I specified.

Further technical details

EF Core version: 1.2.0-preview1-22878
Database Provider: Microsoft.EntityFrameworkCore.SqlServer
Operating system: Windows 10.0.14393
IDE: VS2015, commandline, Azure App Service

[maumar]

This happens because #6647 (left join on WidgetManufacturer)

However for this case we now produce incorrect results. Before the change, Distinct was done on the client, based on only one column and therefore produced the correct results. We should aim to fix this in 1.1.1 @divega

[divega]

@maumar sounds good. Feel free to assign it.

[maumar]

Fixed in 858a34f

[Eilon]

This patch bug is approved. Please use the normal code review process w/ a PR and make sure the fix is in the correct branch, then close the bug and mark it as done.