用扩展方法进行实体框架分页很慢? [英] Entity framework paging with extension method is slow?

问题描述

我在C#中的Entity Framework中查询缓慢时遇到一些问题.我创建了一个名为Page的扩展方法来处理分页，但是当我使用它时，查询的速度变得非常慢.如果我只是做.Skip(page.Value * pageSize.Value).Take(pageSize.Value)而不是使用Page，则查询会快很多.我想用Page做到这一点会在分页之前获取所有联系人.有什么方法可以防止这种情况，还是我做错了其他事情?

I have some problems with a slow query in Entity Framework in C#. I have created an extension method called Page to handle paging, but when I use it the query gets really slow. If I just do .Skip(page.Value * pageSize.Value).Take(pageSize.Value) instead of using Page the query gets a lot faster. I guess that doing it with Page fetches all contacts before paging. Is there a way to prevent this or am I doing something else wrong?

查询:

var contacts = db.Contacts
                        .Where(x => x.AccountID == accountID && x.Deleted == false)
                        .OrderByDescending(x => x.FirstName)
                        .ThenBy(x => x.LastName)
                        .ThenBy(x => x.CreatedDate)
                        .Page(page, pageSize);

return contacts.ToList();

扩展方法:

    public static IEnumerable<T> Page<T>(this IEnumerable<T> elements, int? page, int? pageSize)
    {
        if (page.HasValue && pageSize.HasValue)
            return elements.Skip(page.Value * pageSize.Value).Take(pageSize.Value);
        else
            return elements;
    }

推荐答案

您的扩展方法应该在IQueryable之上，以便EF可以处理该表达式并分页生成SQL查询.

Your extension method should be over IQueryable so that EF can process the expression and generate the SQL query with pagination.

由于您使用的是IEnumerable，因此Page方法将调用IEnumerable的Skip and Take.这将导致枚举到该点为止的查询结果(在调用Page之前)，并在内存中对所有返回的项目进行分页，而不是在数据库查询中包括分页.

Since your using IEnumerable, the Page method will invoke Skip and Take of IEnumerable. This will cause enumeration of the result of the query that was built up to that point (before the call to Page) and make the pagination in memory, over all the returned items, instead of including pagination on the DB query.

这篇关于用扩展方法进行实体框架分页很慢?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！