使用EFCore遇到的查询问题

前言

在排查错误日志的时候,发现有这样的错误日志:"The query uses a row limiting operator ('Skip' 'Take' ) without an 'OrderBy'’operator. This may lead to unpredictablresults. If the 'Distinct’operator is used after 'OrderBy',then make sure to use the 'OrderBy'operator after 'Diinct" as the ordering would otherwise get erased."
具体如图:
efcore分页没有进行排序

从提示中看到Skip和Take应该是某个分页查询没有排序.根据日志去代码中查看这个接口,果真是没有没有排序.其实这种在开发阶段都避免的,只要在调试的时候稍微看看日志.

错误的示例

其实错误的示例在 记一次遇到的乱码 ,查询的时候,是用过的,本来想先写这篇呢,只是觉得乱码,可以先说一下.
public async Task<PagedResultDto<CheckInfoDto>> GetAll(PagedResultRequestDto input)
{
    var peopleQuery = await _peopleRepository.GetQueryableAsync();
    var checkQuery = await _checkInfoRepository.GetQueryableAsync();

    var query = from p in peopleQuery
                join c in checkQuery on p.SN equals c.PeopleSN
                select new CheckInfoDto { SN = c.SN, PeopleSN = p.SN, Type = p.Type }; //Type就是乱码的枚举字段

    var totalCount = await query.CountAsync();

    List<CheckInfoDto> list;
    if (totalCount > 0)
    {
        list = await query.PageBy(input).ToListAsync(); //这里果然没有OrderBy
    }
    else
    {
        list = ([]);
    }

    return new PagedResultDto<CheckInfoDto>(totalCount, list);
}

正确的示例

第一种是在:
var query = from p in peopleQuery
            join c in checkQuery on p.SN equals c.PeopleSN
            orderby p.Id  //在这里进行排序
            select new CheckInfoDto { Id = p.Id, SN = c.SN, PeopleSN = p.SN, Type = p.Type }; //Type就是乱码的枚举字段

第二种是在:

List<CheckInfoDto> list;
if (totalCount > 0)
{
    list = await query.OrderBy(p => p.Id).PageBy(input).ToListAsync(); //在PageBy之前进行排序
}
else
{
    list = ([]);
}

两种方式生成的sql是一样的.

SELECT [p].[Id], [c].[SN], [p].[SN] AS [PeopleSN], [p].[Type]
FROM [Peoples] AS [p]
INNER JOIN [CheckInfos] AS [c] ON [p].[SN] = [c].[PeopleSN]
ORDER BY [p].[Id]
OFFSET @__p_0 ROWS FETCH NEXT @__p_1 ROWS ONLY


秋风 2024-06-23
文章分类
algorithm windows linux c cpp csharp javascript java dependon mono javascript browser asp.net core go asp.net database python asm rust english TypeScript EntityFramework Core
友情链接