Query: Wrong Sql emitted when doing a .Distinct() query on a one-to-one join

[shaulbehr]

I have a Thing table, which has a one to zero/one relationship with ThingExtended:

public class Thing
{
  [Key]
  public Guid ThingId {get; set;}
  public ThingExtended ThingExtended {get; set;}
}
public class ThingExtended
{
  [Key]
  public Guid ThingId {get; set;}
  public Thing Thing {get; set;}
  public int SomeValue {get; set;}
}
...
modelBuilder.Entity<ThingExtended>( e=>
{
  e.HasOne(t => t.Thing)
    .WithOne(t => t.ThingExtended)
    .HasForeignKey<ThingExtended>(t=>t.ThingId);
});

Now I want to write a query that finds the number of distinct values for ThingExtended.SomeValue for some subset of Things:

IQueryable<Thing> thingQuery;
...
var distinctValues = thingQuery
    .Select(t => t.ThingExtended.SomeValue)
    .Distinct()
    .Count();

Ba-da-BOOM! SQL error:

System.Data.SqlClient.SqlException : The ORDER BY clause is invalid in views, inline functions, derived tables, subqueries, and common table expressions, unless TOP, OFFSET or FOR XML is also specified.

I put a sniffer on the Sql server, and found this script being run:

SELECT COUNT(*)
FROM (
    SELECT DISTINCT [t].[ThingId], [t.ThingExtended].[ThingId], [t.ThingExtended].[SomeValue]
    FROM [dbo].[Thing] AS [t]
    LEFT JOIN [dbo].[ThingExtended] AS [t.ThingExtended] ON [t].[ThingId] = [t.ThingExtended].[ThingId]
    ORDER BY [t].[ThingId]
) AS [t]

... which, aside from being invalid Sql, is incorrect logically, since I only want the "distinct" modifier to apply to SomeValue.

[maumar]

Looks like a duplicate of #7348 which is now fixed and will ship in 1.1.1

[maumar]

Unfortunately the fix is not ideal - distinct and count will now be applied on the client. This will again be fixed once we stop materializing unnecessary columns in the LEFT JOIN scenarios

[maumar]

This is now fixed, closing