带分页的动态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.Expressions.Expression类动态构建OrderBy(Descending)调用(类似于

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屋！