.Distinct() on a string property is not occurring in the database

[Brandon]

In the following example, the executed query is not doing a distinct at the database level event though the grade name is a string property. It appears to bring back all records and then do the distinct in memory. Possibly related to the multiple column sort?

It results in the following query:
SELECT
c.[Name] AS COL1, c.[SortOrder] AS xj1
FROM
[Sf_SchoolProduct] a JOIN [Sf_Gender] AS b ON (a.[GenderCode] = b.[Code]) LEFT JOIN [Sf_Grade] AS c ON (a.[GradeCode] = c.[Code])
WHERE
a.[SchoolCode] = @p0 AND b.[Name] = @p1 ORDER BY xj1, COL1

//School and genderName are method arguments.

using (MyContext context = new MyContext ())
            {
                return context.SchoolProducts
                    .Where(sp => sp.SchoolCode == School.Code && sp.Gender.Name == genderName)
                    .OrderBy(sp => sp.Grade.SortOrder)
                    .ThenBy(sp => sp.Grade.Name)
                    .Select(sp => sp.Grade.Name)
                    .Distinct()
                    .ToList();
            }

[Brandon]

Follow up, I changed the query to try group by. Now the records are distinct by using grouping but they're not sorted.

Now the query looks like this:
SELECT b.[Name] AS COL1                  
FROM [Sf_SchoolProduct] a LEFT JOIN [Sf_Gender] AS b ON (a.[GenderCode] = b.[Code])
WHERE a.[SchoolCode] = @p0                                      
GROUP BY b.[Name]

return context.SchoolProducts
                    .Where(sp => sp.SchoolCode == School.Code)
                    .OrderBy(sp => sp.Gender.SortOrder)
                    .ThenBy(sp => sp.Gender.Name)
                    .GroupBy(sp => sp.Gender.Name)
                    .Select(g => g.Key)
                    //.Distinct()
                    .ToList();

[Viktor Zhivkov]

Hi Brandon,

I am glad that you were able to find a working query version for your situation.
In general you should know that anything after a LINQ projection (.Select(...)) will always be executed in-memory by Telerik Data Access, therefore the observed behavior is expected.

Regards,
Viktor Zhivkov
Telerik by Progress

 

[Brandon]

Viktor, 

I'm not sure that I would consider it a working solution. The OrderBy clauses are completely ignored and since the rest of the operation is performed in memory... it doesn't have the data in the projection to perform the sort. I think it would be helpful in these situations for the Data Access libraries to throw an exception when it simple cannot execute the entire query as written in Linq... instead of having to poke under the hood to realize its not working correctly.

What do you think?

[Viktor Zhivkov]

Hello Brandon,

If I understand correctly in your initial query you want to get all Grade names that are related to SchoolProducts matching certain criteria. The names should be sorted.
If this is the case you should be able to re-write your query like this:

1.var q2 = context.Grades
2.           .Where(g => context.SchoolProducts
3.               .Any(sp => sp.SchoolCode == School.Code && sp.Gender.Name == genderName &&
4.                   sp.GradeId = g.Id) // join to Grades
5.           .OrderBy(g => g.SortOrder).ThenBy(g => g.Name)
6.           .Select(c => g.Name);

If Grade names are not unique you will need again the .Distinct() call to remove the duplicates, but the way the query is expressed you will not get repetitions due to joining with the SchoolProducts.

The behavior that you have described is the design decision taken when a .Where() clause will be executed in-memory. For projections and post-projection operations we have decided to silently fall back to split execution. 

Regards,
Viktor Zhivkov
Telerik by Progress