c# - 为什么 EF Core 在末尾添加一个额外的 ORDER BY

Question

我有一个问题:

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

        return dto;
    }

它产生这个 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]

[cp].[GetCategoryTree] 的输出已在该 View 中排序。为什么 EF Core 在末尾添加额外的 ORDER BY？我可以告诉 EF Core 不排序吗？

Answer 1

递归CTE返回的结果是普通的记录集，这意味着如果你在客户端需要树，你必须自己重构它。

让我们重用原始查询:

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