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

Thread is closed for posting
5 posts, 0 answers
  1. Brandon
    Brandon avatar
    3 posts
    Member since:
    Sep 2015

    Posted 22 Feb 2017 Link to this post

    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();
                }

     

  2. Brandon
    Brandon avatar
    3 posts
    Member since:
    Sep 2015

    Posted 22 Feb 2017 Link to this post

    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();
  3. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    323 posts

    Posted 24 Feb 2017 Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
  4. Brandon
    Brandon avatar
    3 posts
    Member since:
    Sep 2015

    Posted 24 Feb 2017 in reply to Viktor Zhivkov Link to this post

    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?

  5. Viktor Zhivkov
    Admin
    Viktor Zhivkov avatar
    323 posts

    Posted 01 Mar 2017 Link to this post

    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
     
    Check out the latest announcement about Telerik Data Access vNext as a powerful framework able to solve core development problems.
Back to Top