通过单独的' List '变量排序列

本文关键字：变量 排序 string 单独 List | 更新日期: 2023-09-27 18:05:06

我需要根据另一个列表中元素的索引对实体框架查询的结果进行排序。

我尝试了其他地方找到的建议，如

.ThenBy(m=>list.IndexOf(m.Term))

，但我得到一个HTTP 500错误，所以我想知道如果我错过了什么。在调试时，我得到这个内部异常。

LINQ到实体不能识别Int32方法IndexOf(System.String)'方法，该方法无法翻译

特别是，我在考虑这个场景。

private IEnumerable<MiaLog1A> PopulateQuery(string selectedCampus)
{
    var list = new List<string> {"No Longer Alphabetical","Fall","Mid","Spring"};
    return _db.MiaLog1A.Where(m => m.Campus == selectedCampus)
            .OrderBy(m => m.StudentName)
            .ThenBy(m=>m.Term)  
                    /* I would like to sort "Term" by the 
                     * order of "list".
                     */
        .AsEnumerable();
}

是否有一种方法可以以这种方式排序，或者我是否被限制为通过字典或创建连接排序?

通过单独的' List<string> '变量排序列

这应该能奏效:

private IEnumerable<MiaLog1A> PopulateQuery(string selectedCampus)
{
    var list = new List<string> {"Fall","Mid","Spring"};
    return _db.MiaLog1A.Where(m => m.Campus == selectedCampus)
        .AsEnumerable()
        .OrderBy(m => m.StudentName)
        .ThenBy(m=> list.IndexOf(m.Term));
}

这是一种疯狂的方式，但它不会回落到IEnumerable 

var db = new BloggingContext();
var list = (new List<string> { "FR", "EN" }).
    Select((s,i)=> $"select '{s}' as {nameof(OrderIndex.Name)},{i} as {nameof(OrderIndex.Id)}");
var order = db.Set<OrderIndex>().FromSql(String.Join(" union ",list));
var orderedItems = from post in db.Posts
                   join ln in order on post.Lang equals ln.Name into lnPost
                   from od in lnPost.DefaultIfEmpty()
                   orderby od.Id
                   select post;
var data = orderedItems.ToList();

您可以在这里找到BloggingContext的定义，我刚刚为语言代码添加了一个Lang字段。这将工作在SQLite, SqlServer和MySQL的oracle，你需要添加from dual由于EF core非常糟糕，您将需要对OrderIndex执行与SQL视图相同的过程。在EF 6中，使用SqlQuery可以更好地完成此操作，您不需要像EF core那样进行注册。创建的查询是

SELECT "post"."PostId", "post"."BlogId", "post"."Content", "post"."Date", "post"."Lang", "post"."Title", "ln"."Id", "ln"."Name"
    FROM "Posts" AS "post"
    LEFT JOIN (
        select 'FR' as Name,0 as Id union select 'EN' as Name,1 as Id
    ) AS "ln" ON "post"."Lang" = "ln"."Name"
    ORDER BY "ln"."Id", "post"."Lang"

编辑:只要记住另一种方法就行了，虽然没那么疯狂，但可能会更好。

var lang = new List<string> { "FR", "EN" };    
var orderedItems = from post in db.Posts
                   orderby (lang[0] == post.Lang) ? 
                   0 :((lang[1] == post.Lang) ? 1 : 2)
                   select post;
var param = Expression.Parameter(typeof(Post));
var order = lang.Select((s, i) => new { s, i })
    .Aggregate((Expression)Expression.Constant(lang.Count), (agg, i) =>
        Expression.Condition(
            Expression.Equal(Expression.Property(param,nameof(Post.Lang)),
            Expression.Constant(i.s)),
            Expression.Constant(i.i),
            agg));
var exp = Expression.Lambda<Func<Post, int>>(order, param);
var data = db.Posts.OrderBy(exp).ToList();

和SQL 

SELECT "p"."PostId", "p"."BlogId", "p"."Content", "p"."Date", "p"."Lang", "p"."Title"
    FROM "Posts" AS "p"
    ORDER BY CASE
        WHEN "p"."Lang" = 'EN'
        THEN 1 ELSE CASE
            WHEN "p"."Lang" = 'FR'
            THEN 0 ELSE 2
        END
    END
我仍然认为疯狂的方式是有用的，我只是不知道是什么。
相关文章：