带有分页的动态 Linq 到实体 Orderby [英] Dynamic Linq to Entities Orderby with Pagination

问题描述

我有一个包含超过 80 万条记录的 SQL 表，需要以 25 个结果的页面显示在 Web 上.我需要能够从表格中搜索和排序这些结果，但是由于表格太大，我无法在过滤/排序之前将所有结果拉到 IEnumerable(我之前这样做过并且有效，但现在令人难以置信最初的拉动速度很慢).

I have a SQL table with over 800k records that need to be displayed on the web in pages of 25 results. I need to be able to search and sort these results from the table, but because the table is so large I cannot pull all the results to an IEnumerable before filtering/sorting (I was doing this before and it worked, but it is now incredibly slow doing the initial pull).

我已经找到了搜索方式，但这种搜索方式真的让我很困惑.我花了几个小时研究它，但在 .Skip().Take() 之前找不到任何有效的解决方案.

I've figured out the search, but the sort is really messing me up. I've spent hours researching it, but can't find any solutions that work before the .Skip().Take().

我需要能够做这样的事情:

I need to be able to do something like this:

string sortField = "Name"; //just for example
string sortDirection = "descending"; //also for example
List<People> = (from s in db.People
                orderby sortField sortDirection
                select s).Skip((page - 1) * pageSize).Take(pageSize).ToList();

People 中的可排序列可以是 DateTime、int 或字符串，所以我尝试做类似的事情

The sortable columns in People can be DateTime, ints, or strings, so my attempts to do something like

orderby (
    currentSort == "Name" ? s.Name : 
    currentSort = "SignUpDate" ? s.SignupDate : s.Id
)

徒劳无功，因为程序抱怨混合类型.

were in vain, as the program complains about mixing types.

有什么可以做的事情吗?提前感谢您的任何帮助或线索！

Is there anything that can be done to make this work? Thanks in advance for any help or leads!

推荐答案

您可以使用以下自定义扩展方法，该方法使用 System.Linq 动态构建 OrderBy(Descending) 调用.Expressions.Expression 类(类似于 如何使用字符串通过表达式创建EF订单?):

You can use the following custom extension method, which builds OrderBy(Descending) call dynamically using the System.Linq.Expressions.Expression class (similar to How to use a string to create a EF order by expression?):

public static partial class QueryableExtensions
{
    public static IOrderedQueryable<T> OrderByMember<T>(this IQueryable<T> source, string memberPath, bool descending)
    {
        var parameter = Expression.Parameter(typeof(T), "item");
        var member = memberPath.Split('.')
            .Aggregate((Expression)parameter, Expression.PropertyOrField);
        var keySelector = Expression.Lambda(member, parameter);
        var methodCall = Expression.Call(
            typeof(Queryable), descending ? "OrderByDescending" : "OrderBy", 
            new[] { parameter.Type, member.Type },
            source.Expression, Expression.Quote(keySelector));
        return (IOrderedQueryable<T>)source.Provider.CreateQuery(methodCall);
    }
}

像这样:

var people = db.People
    .OrderByMember(sortField, sortDirection == "descending")
    .Skip((page - 1) * pageSize).Take(pageSize)
    .ToList();

这篇关于带有分页的动态 Linq 到实体 Orderby的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！