Linq .Distinct() add extraneous ordering

Question

My entity has a computable geography::point column (represented by DbGeography type in code) When I call a .Distinct() method on query for this entity it adds ORDER BY clause that's ordering by every single column, including the geography::point one, which is not comparable. That leads to an exception. What can be a possible reason for that, and how to fix it?

Query generated by EF before the call to Distinct:

SELECT 
[Project1].[account_key1] AS [account_key], 
...
FROM ( SELECT 
    [Extent1].[account_key] AS [account_key], 
    ...
    CASE WHEN ([Extent9].[object_type] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM         [dbo].[property_info] AS [Extent1]
    LEFT OUTER JOIN [dbo].[listing_info] AS [Extent2] ON ([Extent1].[property_key] = [Extent2].[property_key]) AND ([Extent1].[account_key] = [Extent2].[account_key])
    LEFT OUTER JOIN [dbo].[v_property_owner_primary_contact] AS [Extent3] ON ([Extent1].[property_key] = [Extent3].[property_key]) AND ([Extent1].[account_key] = [Extent3].[account_key])
    LEFT OUTER JOIN [dbo].[group_info] AS [Extent4] ON ([Extent1].[group_key] = [Extent4].[group_key]) AND ([Extent1].[account_key] = [Extent4].[account_key])
    INNER JOIN [dbo].[user_info] AS [Extent5] ON ([Extent1].[user_key] = [Extent5].[user_key]) AND ([Extent1].[account_key] = [Extent5].[account_key])
    LEFT OUTER JOIN [dbo].[property_types] AS [Extent6] ON ([Extent1].[property_type_key] = [Extent6].[property_type_key]) AND ([Extent1].[account_key] = [Extent6].[account_key])
    LEFT OUTER JOIN [dbo].[listing_info] AS [Extent7] ON ([Extent1].[property_key] = [Extent7].[property_key]) AND ([Extent1].[account_key] = [Extent7].[account_key])
    LEFT OUTER JOIN [dbo].[country_info] AS [Extent8] ON [Extent1].[country_key] = [Extent8].[country_key]
    LEFT OUTER JOIN [dbo].[object_info] AS [Extent9] ON ([Extent1].[property_key] = [Extent9].[property_key]) AND ([Extent1].[account_key] = [Extent9].[account_key])
    WHERE ([Extent1].[account_key] = @p__linq__0) AND ([Extent1].[account_key] = @p__linq__1)
)  AS [Project1]
ORDER BY [Project1].[account_key1] ASC, [Project1].[property_key1] ASC, [Project1].[account_key2] ASC, [Project1].[property_key2] ASC, [Project1].[account_key] ASC, [Project1].[account_key6] ASC, [Project1].[property_key3] ASC, [Project1].[property_key] ASC, [Project1].[account_key3] ASC, [Project1].[group_key1] ASC, [Project1].[account_key4] ASC, [Project1].[user_key2] ASC, [Project1].[account_key5] ASC, [Project1].[property_type_key1] ASC, [Project1].[country_key1] ASC, [Project1].[C2] ASC

Query generated after the Distinct call:

SELECT 
[Project1].[country_key] AS [country_key], 
...
FROM ( SELECT 
    [Extent1].[account_key] AS [account_key], 
    ...
    CASE WHEN ([Extent9].[object_type] IS NULL) THEN CAST(NULL AS int) ELSE 1 END AS [C2]
    FROM         [dbo].[property_info] AS [Extent1]
    LEFT OUTER JOIN [dbo].[listing_info] AS [Extent2] ON ([Extent1].[property_key] = [Extent2].[property_key]) AND ([Extent1].[account_key] = [Extent2].[account_key])
    LEFT OUTER JOIN [dbo].[v_property_owner_primary_contact] AS [Extent3] ON ([Extent1].[property_key] = [Extent3].[property_key]) AND ([Extent1].[account_key] = [Extent3].[account_key])
    LEFT OUTER JOIN [dbo].[group_info] AS [Extent4] ON ([Extent1].[group_key] = [Extent4].[group_key]) AND ([Extent1].[account_key] = [Extent4].[account_key])
    INNER JOIN [dbo].[user_info] AS [Extent5] ON ([Extent1].[user_key] = [Extent5].[user_key]) AND ([Extent1].[account_key] = [Extent5].[account_key])
    LEFT OUTER JOIN [dbo].[property_types] AS [Extent6] ON ([Extent1].[property_type_key] = [Extent6].[property_type_key]) AND ([Extent1].[account_key] = [Extent6].[account_key])
    LEFT OUTER JOIN [dbo].[listing_info] AS [Extent7] ON ([Extent1].[property_key] = [Extent7].[property_key]) AND ([Extent1].[account_key] = [Extent7].[account_key])
    LEFT OUTER JOIN [dbo].[country_info] AS [Extent8] ON [Extent1].[country_key] = [Extent8].[country_key]
    LEFT OUTER JOIN [dbo].[object_info] AS [Extent9] ON ([Extent1].[property_key] = [Extent9].[property_key]) AND ([Extent1].[account_key] = [Extent9].[account_key])
    WHERE ([Extent1].[account_key] = @p__linq__0) AND ([Extent1].[account_key] = @p__linq__1)
)  AS [Project1]
ORDER BY [Project1].[country_key] ASC, [Project1].[account_key] ASC, [Project1].[group_key] ASC, [Project1].[account_key1] ASC, [Project1].[property_key1] ASC, [Project1].[account_key2] ASC, [Project1].[property_key2] ASC, [Project1].[user_key] ASC, [Project1].[property_type_key] ASC, [Project1].[account_key6] ASC, [Project1].[property_key3] ASC, [Project1].[property_key] ASC, [Project1].[rowversion] ASC, [Project1].[rea8_uniqueid] ASC, [Project1].[created_by] ASC, [Project1].[created_date] ASC, [Project1].[modified_by] ASC, [Project1].[modified_date] ASC, [Project1].[verified_by] ASC, [Project1].[verified_date] ASC, [Project1].[site_principal_key] ASC, [Project1].[owner_principal_key] ASC, [Project1].[agent_principal_key] ASC, [Project1].[property_name] ASC, [Project1].[not_mappable] ASC, [Project1].[address_number_1] ASC, [Project1].[address_number_2] ASC, [Project1].[address_direction] ASC, [Project1].[address_street] ASC, [Project1].[address_suite] ASC, [Project1].[padded_number_1] ASC, [Project1].[padded_number_2] ASC, [Project1].[cross_streets] ASC, [Project1].[map_coord] ASC, [Project1].[parcel] ASC, [Project1].[building_id] ASC, [Project1].[description] ASC, [Project1].[web_site] ASC, [Project1].[for_sale] ASC, [Project1].[for_lease] ASC, [Project1].[owner_occupied] ASC, [Project1].[building_class] ASC, [Project1].[construction_type] ASC, [Project1].[location] ASC, [Project1].[zoning] ASC, [Project1].[market] ASC, [Project1].[submarket] ASC, [Project1].[acres] ASC, [Project1].[sqft] ASC, [Project1].[units] ASC, [Project1].[stories] ASC, [Project1].[assessed_value] ASC, [Project1].[last_trans_price] ASC, [Project1].[last_trans_date] ASC, [Project1].[C1] ASC, [Project1].[address_1] ASC, [Project1].[address_2] ASC, [Project1].[city] ASC, [Project1].[state] ASC, [Project1].[zip_code] ASC, [Project1].[latitude] ASC, [Project1].[longitude] ASC, [Project1].[user_1] ASC, [Project1].[user_2] ASC, [Project1].[user_3] ASC, [Project1].[user_4] ASC, [Project1].[user_5] ASC, [Project1].[user_6] ASC, [Project1].[user_7] ASC, [Project1].[user_8] ASC, [Project1].[user_9] ASC, [Project1].[user_10] ASC, [Project1].[user_11] ASC, [Project1].[user_12] ASC, [Project1].[logical_1] ASC, [Project1].[logical_2] ASC, [Project1].[logical_3] ASC, [Project1].[logical_4] ASC, [Project1].[logical_5] ASC, [Project1].[logical_6] ASC, [Project1].[logical_7] ASC, [Project1].[logical_8] ASC, [Project1].[logical_9] ASC, [Project1].[logical_10] ASC, [Project1].[logical_11] ASC, [Project1].[logical_12] ASC, [Project1].[logical_13] ASC, [Project1].[logical_14] ASC, [Project1].[logical_15] ASC, [Project1].[logical_16] ASC, [Project1].[logical_17] ASC, [Project1].[logical_18] ASC, [Project1].[logical_19] ASC, [Project1].[logical_20] ASC, [Project1].[logical_21] ASC, [Project1].[logical_22] ASC, [Project1].[logical_23] ASC, [Project1].[logical_24] ASC, [Project1].[usernumber_1] ASC, [Project1].[usernumber_2] ASC, [Project1].[usernumber_3] ASC, [Project1].[userdate_1] ASC, [Project1].[userdate_2] ASC, [Project1].[userdate_3] ASC, [Project1].[usermulti] ASC, [Project1].[import_source] ASC, [Project1].[import_record] ASC, [Project1].[notes] ASC, [Project1].[geo_point] ASC, [Project1].[account_key3] ASC, [Project1].[group_key1] ASC, [Project1].[account_key4] ASC, [Project1].[user_key2] ASC, [Project1].[account_key5] ASC, [Project1].[property_type_key1] ASC, [Project1].[country_key1] ASC, [Project1].[C2] ASC

You can see that the only thing distinct does is add extraneous orderings

Code that generates a query:

var query = _unitOfWork.Get<T>().AsExpandable();
if (includes != null)
{
    foreach (var include in includes)
    {
        query = query.Include(include);
    }
}
foreach (var queryPredicate in queryPredicateList)
{
    query = query.Where(queryPredicate);
}

query = query.Distinct();

Question is: Why do you call `Distinct` on whole entity? Show query. — Svyatoslav Danyliv, May 13 '22 at 11:06
This part of code was on the project before me, people who wrote it claim that it's for optimization reasons — JohnDiGriz, May 13 '22 at 11:11
Usually `Distinct` on whole entity is used when query is wrong and records become duplicated. — Svyatoslav Danyliv, May 13 '22 at 11:12
The solution is to not call `.Distinct()`. Regardless of how the query ends up, there is no way of efficiently executing that operation on the geography column, and presumably you don't even want to do that in the first place (if duplicates had to be removed, then only by key, not by the whole entity). Adding `.Distinct()` "for optimization reasons" just smacks of something that was added to paper over a problem somewhere else that should have gotten a specific solution there, since in general de-duplicating, even when it can be done, is hardly free. — Jeroen Mostert, May 13 '22 at 12:32

Linq .Distinct() add extraneous ordering

0 Answers0