CODEWITHSUNDEEP
c#entity-framework-core
Randy
Randy

Reputation: 1287

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

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?

Upvotes: 3

Views: 1620

Answers (2)

Svyatoslav Danyliv
Svyatoslav Danyliv

Reputation: 27282

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

Upvotes: 1

Randy
Randy

Reputation: 1287

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.

Upvotes: 0

Related Questions