EF中IOrderedQueryable与IOrderedEnumerable类型不匹配及排序问题解决

问题概述 #

执行EF查询代码的最后一行var total = containers.Count();时，抛出类型转换错误：

"Expression of type 'System.Linq.IOrderedQueryable1[AC.DBModels.Entities.ContactEmail]' cannot be used for return type 'System.Linq.IOrderedEnumerable1[AC.DBModels.Entities.ContactEmail]"

移除containers3Sorting中的.OrderBy(y => y.ContactEmails.OrderBy(z => z.Email).ThenBy(z => z.DisplayName))语句后，代码可正常运行。

原查询代码 #

IQueryable<Container> containers = this.Repository.Containers.AsNoTracking();
var expressions = new List<IQueryable<Container>>();
var sortingExpressions = new List<Func<IQueryable<Container>, IOrderedQueryable<Container>>>();

var containers2 = containers.Where(x => EF.Functions.Like(x.Description, $"%{message.SearchValue}%"));
if (containers2.Any())
{
    var containers2Sorting = new Func<IQueryable<Container>, IOrderedQueryable<Container>>(x => x
                                .OrderBy(y => y.Description)
                                .ThenByDescending(y => y.DateUpdated));

    expressions.Add(containers2);
    sortingExpressions.Add(containers2Sorting);
}

var containers3 = containers.Where(x => x.ContactEmails
                                    .Any(y => EF.Functions.Like(y.Email, $"%{message.SearchValue}%")
                                            || EF.Functions.Like(y.DisplayName, $"%{message.SearchValue}%")));
if (containers3.Any())
{
    var containers3Sorting = new Func<IQueryable<Container>, IOrderedQueryable<Container>>(x => x
                                .OrderBy(y => y.ContactEmails.OrderBy(z => z.Email).ThenBy(z => z.DisplayName))
                                .ThenByDescending(y => y.DateUpdated));

    expressions.Add(containers3);
    sortingExpressions.Add(containers3Sorting);
}

if (expressions.Any())
{
    var mergedContainers = expressions.Aggregate((acc, i) => acc.Union(i));

    if (sortingExpressions.Any())
    {
        var mergedSorting = sortingExpressions
                                    .Aggregate((acc, next) => q => next(acc(q)));

        containers = mergedSorting(mergedContainers);
    }
    else
    {
        containers = mergedContainers.OrderByDescending(x => x.DateUpdated);
    }
}
else
{
    containers = Enumerable.Empty<Container>().AsQueryable();
}

var total = containers.Count();

尝试过的无效改写及错误 #

1. 尝试通过赋值导航属性排序后返回ID：

.OrderBy(y =>
{
    y.ContactEmails = y.ContactEmails.OrderBy(z => z.Email).ThenBy(z => z.DisplayName);
    return y.Id;
})

错误：Can't implicitly convert IOrderedEnumerable to List

2. 添加.ToList()后：

.OrderBy(y =>
{
    y.ContactEmails = y.ContactEmails.OrderBy(z => z.Email).ThenBy(z => z.DisplayName).ToList();
    return y.Id;
})

错误：lambda expression with a statement body can't be converted to expression tree 和 expression tree may not contain assignment operator

示例数据 #

dbo.Containers #

dbo.ContactEmails #

预期结果（搜索值为"test"时） #

解决方案 #

错误根源是不能直接将集合排序的结果作为OrderBy的键，EF无法将这种表达式转换为SQL。我们需要针对匹配搜索条件的ContactEmail排序后，取单个字段（如Email）作为容器的排序依据。

修改containers3Sorting的排序逻辑：

var containers3Sorting = new Func<IQueryable<Container>, IOrderedQueryable<Container>>(x => x
    .OrderBy(y => y.ContactEmails
        // 先筛选当前容器中匹配搜索条件的ContactEmail
        .Where(z => EF.Functions.Like(z.Email, $"%{message.SearchValue}%") || EF.Functions.Like(z.DisplayName, $"%{message.SearchValue}%"))
        // 对匹配的ContactEmail排序
        .OrderBy(z => z.Email)
        .ThenBy(z => z.DisplayName)
        // 取排序后的第一个Email作为排序键
        .Select(z => z.Email)
        .FirstOrDefault())
    .ThenByDescending(y => y.DateUpdated));

逻辑说明 #

1. 先筛选当前Container中符合搜索条件的ContactEmail，避免对所有关联数据排序；
2. 对筛选后的ContactEmail按Email、DisplayName排序；
3. 取排序后的第一个Email作为Container的排序键，EF可以将这个逻辑转换为合法的SQL；
4. 后续再按DateUpdated降序排序，符合需求。

修改后执行Count()即可正常运行，同时能满足预期的排序结果。

内容的提问来源于stack exchange，提问作者A. Gladkiy