linq 按特定列表值顺序对IQueryable排序

x7rlezfr 于 2023-06-19 发布在其他

关注(0)|答案(3)|浏览(156)

是否可以通过其他列表的索引对IQueryable进行排序，而不强制转换为IEnumerable。Enitity Framework和Linq无法将其转换为SQL Query。我还尝试了Dictionary<int，Enum>和返回int顺序的函数。我不想要枚举值。示例：

public enum Letter
{
    A = 0,
    B = 1,
    C = 2
}

public class MyClass
{
    public Letter LetterEnum { get; set; }
    public DateTime Date { get; set; }
}

public IQueryable<MyClass> Sort(IQueryable<MyClass> data)
{
    List<Letter> prefferedOrder = new List<Letter> { Letter.B, Letter.C, Letter.A };
    return data.OrderBy(x=> prefferedOrder.IndexOf(x.LetterEnum)).ThenBy(x=> x.Date);
}

linq

来源：https://stackoverflow.com/questions/76424326/sort-iqueryable-by-specific-list-value-order

3条答案

按热度按时间

goqiplq21#

如果是SQL问题，我建议使用case表达式。在linq中，你可以这样写：

return data
           .OrderBy(x => x.LetterEnum == Letter.B ? 1 :
                         x.LetterEnum == Letter.C ? 2 :
                         x.LetterEnum == Letter.A ? 3 : (int?) null)
           .ThenBy(x => x.Date);

生成的SQL：

ORDER BY CASE
    WHEN CAST([t].[LetterEnum] AS int) = 1 THEN 1
    WHEN CAST([t].[LetterEnum] AS int) = 2 THEN 2
    WHEN CAST([t].[LetterEnum] AS int) = 0 THEN 3
    ELSE NULL
END, [t].[Date]

赞(0）回复(0）举报 2023-06-19

ggazkfy82#

可以动态构建排序查询：

public IQueryable<MyClass> Sort(IQueryable<MyClass> data)
{
    var prefferedOrder = new List<Letter> { Letter.B, Letter.C, Letter.A };

    IOrderedQueryable<MyClass>? sorted = null;

    foreach (var orderItem in prefferedOrder)
    {
        if (sorted == null)
        {
            sorted = data.OrderBy(x => x.LetterEnum == orderItem ? 0 : 1);
        }
        else
        {
            sorted = sorted.ThenBy(x => x.LetterEnum == orderItem ? 0 : 1);
        }
    }

    if (sorted == null)
    {
        sorted = data.OrderBy(x => x.Date);
    }
    else
    {
        sorted = sorted.ThenBy(x => x.Date);
    }

    return sorted;
}

从原理上讲，它将生成以下内容：

return data
        .OrderBy(x => x.LetterEnum == Letter.B ? 0 : 1)
        .ThenBy(x => x.LetterEnum == Letter.C ? 0 : 1)
        .ThenBy(x => x.LetterEnum == Letter.A ? 0 : 1)
        .ThenBy(x => x.Date);

赞(0）回复(0）举报 2023-06-19

t9aqgxwy3#

动态顺序
根据您指定的列表，它们将创建一个排序

public IQueryable<MyClass> Sort(IQueryable<MyClass> data)
        {
            List<Letter> prefferedOrder = new List<Letter> { Letter.B, Letter.C, Letter.A };

            for (int i = 0; i <= prefferedOrder.Count; ++i)
            {

                var value = i == prefferedOrder.Count ? 0 : prefferedOrder[i];
                if (data is IOrderedQueryable<MyClass> orderedQuery)
                {
                    if (i == prefferedOrder.Count)
                        data = orderedQuery.ThenBy(d => d.Date);
                    else
                        data = orderedQuery.ThenBy(d => d.LetterEnum == value);
                }
                else
                {
                    data = data.OrderBy(d => d.LetterEnum == value);
                }
            }

            return data;
        }

赞(0）回复(0）举报 2023-06-19

我来回答

linq 按特定列表值顺序对IQueryable排序

3条答案

相关问题

热门标签

最新问答