Why is EF Core adding an extra ORDER BY on the end

Question

I have a query:

    public async Task<IEnumerable<CategoryDto>> GetCategoriesAsync()
    {
        var dto = await _context.Categories.FromSqlRaw(
                @"SELECT * FROM [cp].[GetCategoryTree]")
            .ProjectTo<CategoryDto>(_mapper.ConfigurationProvider)
            .AsNoTrackingWithIdentityResolution()
            .ToListAsync();

        return dto;
    }

It produces this sql:

SELECT [c].[banner_group_id], [c].[category_id], [c].[description], [c].[inactive], [c].[issitecategory], [c].[keywords], [c].[category_name], [c].[page_content], [c].[parent_category_id], [c].[sort_order], [c].[title], [b].[banner_group_id], [b].[category_id], [b].[description], [b].[inactive], [b].[issitecategory], [b].[keywords], [b].[category_name], [b].[page_content], [b].[parent_category_id], [b].[sort_order], [b].[title]

FROM (

    SELECT * FROM [cp].[GetCategoryTree]

) AS [c]

LEFT JOIN [bma_ec_categories] AS [b] ON [c].[category_id] = [b].[parent_category_id]

ORDER BY [c].[category_id], [b].[category_id]

The output from [cp].[GetCategoryTree] is already sorted in that view. Why is EF Core adding an extra ORDER BY on the end? Can I tell EF Core to not sort?

Odd...is it Automapper's ProjectTo? Can you remove that and log your query? — GH DevOps, Aug 04 '21 at 19:06
Mapping to `CategoryDto` causes the join and I see that when EF-core joins it always adds ordering by PK values, probably to make post processing easier (building the objects in memory). — Gert Arnold, Aug 04 '21 at 20:17
Views have no ordering, what do you mean it's already sorted? — Charlieface, Aug 05 '21 at 12:00
No they cannot, that is simply a hack to be able to put the words `ORDER BY` in the view, it only means that the `TOP` is evaluated after sorting, it *does not* and I repeat **DOES NOT** enforce any sorting on the outer query, it's a complete myth. See https://stackoverflow.com/a/15188437/14868997 and https://www.mssqltips.com/sqlservertip/4488/avoid-order-by-in-sql-server-views/ and https://bengribaudo.com/blog/2015/05/01/3430/t-sql-ordering-myth-2-order-by-in-views and https://dba.stackexchange.com/a/21437/220697 — Charlieface, Aug 05 '21 at 13:22

Svyatoslav Danyliv · Accepted Answer · 2021-11-22T08:53:54.107

Recursive CTE returns result is plain recordset, it means that if you need tree on the client side, you have to reconstruct it by yourself.

Let's reuse original query:

public async Task<IEnumerable<CategoryDto>> GetCategoriesAsync()
{
    var plainResult = await _context.Categories.FromSqlRaw(@"SELECT * FROM [cp].[GetCategoryTree]")
        .Select(c => new CategoryDto
        {
            CategoryId = c.CategoryId,
            ParentId = c.ParentId,
            Name = c.Name,
            SortOrder = c.SortOrder
        })
        .ToListAsync();

       var lookup = plainResult.Where(x => x.ParentId != 0).ToLookup(x => x.ParentId);

       foreach (c in plainResult)
       {
           if (lookup.ContainsKey(c.CategoryId))
           {
               c.Children = lookup[c.CategoryId]
                   .OrderBy(x => x.SortOrder)
                   .ThenBy(x => x.Name)
                   .ToList();
           }            
       }

    var dto = plainResult.Where(x => x.ParentId == 0).ToList();

    return dto;
}

Actually I do not see here why do you need Recursive CTE. You can make original query directly from `_context.Categories`. CTE is needed if you want to select leaf of the Tree. — Svyatoslav Danyliv, Aug 04 '21 at 22:33

Randy · Answer 2 · 2021-08-05T00:53:06.200

I started out with a flat Category query. I needed it to be in a Tree Structure. So I added an Include for the Children. But it only included children on the root elements. I asked on SO here and was pointed to a Recursive CTE. That's what [cp].[GetCategoryTree] is. I now have scrapped all that and made my query:

    public async Task<IEnumerable<CategoryDto>> GetCategoriesAsync()
    {
       var dto = await _context.Categories
            .Where(x => x.ParentId == 0)
            .OrderBy(x=> x.SortOrder)
            .ThenBy(x => x.Name)
            .ProjectTo<CategoryDto>(_mapper.ConfigurationProvider)
            .AsNoTracking()
            .ToListAsync();

       foreach (var categoryDto in dto)
       {
           foreach (var categoryDtoChild in categoryDto.Children)
           {
               categoryDtoChild.Children = await _context.Categories
                   .Where(x => x.ParentId == categoryDtoChild.CategoryId)
                   .OrderBy(x => x.SortOrder)
                   .ThenBy(x => x.Name)
                   .ProjectTo<CategoryDto>(_mapper.ConfigurationProvider)
                   .AsNoTracking()
                   .ToListAsync();
            }
       }

        return dto;
    }

Which works, slowly sure, But populates all the way down to Great-Grand-Child. If anyone has a better solution, I am all ears.

Benchmark Results(Mine and @SvyatoslavDanyliv):

Method	IterationCount	Mean	Error	StdDev	Median	Min	Max	Ratio	RatioSD	Gen 0	Gen 1	Gen 2	Allocated
GetCategories	1	142.82 ms	5.260 ms	14.921 ms	142.65 ms	85.572 ms	170.29 ms	1.00	0.00	1000.0000	-	-	5,845 KB
GetCategoriesSingleQuery	1	11.17 ms	0.501 ms	1.396 ms	10.87 ms	8.057 ms	14.76 ms	0.08	0.01	-	-	-	914 KB

GetCategories	2	290.03 ms	7.703 ms	21.473 ms	288.38 ms	239.879 ms	350.48 ms	1.00	0.00	2000.0000	-	-	11,637 KB
GetCategoriesSingleQuery	2	23.21 ms	1.815 ms	5.207 ms	21.58 ms	17.005 ms	38.73 ms	0.08	0.02	-	-	-	1,745 KB

I think I will accept @SvyatoslavDanyliv's answer.

@SvyatoslavDanyliv Please, give me an Example for a single query. — Randy, Aug 04 '21 at 21:57

Why is EF Core adding an extra ORDER BY on the end

2 Answers2