实体框架/Linq to SQL:跳过&拿 [英] Entity Framework/Linq to SQL: Skip & Take

问题描述

只是好奇如何跳过 &采取应该工作.我得到了我想在客户端看到的结果，但是当我连接 AnjLab SQL Profiler 并查看正在执行的 SQL 时，它看起来好像在查询整个行集并将其返回给客户.

Just curious as to how Skip & Take are supposed to work. I'm getting the results I want to see on the client side, but when I hook up the AnjLab SQL Profiler and look at the SQL that is being executed it looks as though it is querying for and returning the entire set of rows to the client.

它真的返回所有行，然后在客户端使用 LINQ 对内容进行排序和缩小范围吗?

Is it really returning all the rows then sorting and narrowing down stuff with LINQ on the client side?

我试过用实体框架和 Linq to SQL 来做这件事；两者似乎具有相同的行为.

I've tried doing it with both Entity Framework and Linq to SQL; both appear to have the same behavior.

不确定它有什么不同，但我在 VWD 2010 中使用 C#.

Not sure it makes any difference, but I'm using C# in VWD 2010.

有什么见解吗?

public IEnumerable<Store> ListStores(Func<Store, string> sort, bool desc, int page, int pageSize, out int totalRecords)
{
    var context = new TectonicEntities();
    totalRecords = context.Stores.Count();
    int skipRows = (page - 1) * pageSize;
    if (desc)
        return context.Stores.OrderByDescending(sort).Skip(skipRows).Take(pageSize).ToList();
    return context.Stores.OrderBy(sort).Skip(skipRows).Take(pageSize).ToList();
}

结果 SQL(注意:我不包括 Count 查询):

Resulting SQL (Note: I'm excluding the Count query):

SELECT 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[LegalName] AS [LegalName], 
[Extent1].[YearEstablished] AS [YearEstablished], 
[Extent1].[DiskPath] AS [DiskPath], 
[Extent1].[URL] AS [URL], 
[Extent1].[SecureURL] AS [SecureURL], 
[Extent1].[UseSSL] AS [UseSSL]
FROM [dbo].[tec_Stores] AS [Extent1]

经过一些进一步的研究，我发现以下内容符合我的预期:

After some further research, I found that the following works the way I would expect it to:

public IEnumerable<Store> ListStores(Func<Store, string> sort, bool desc, int page, int pageSize, out int totalRecords)
{
    var context = new TectonicEntities();
    totalRecords = context.Stores.Count();
    int skipRows = (page - 1) * pageSize;           
    var qry = from s in context.Stores orderby s.Name ascending select s;
    return qry.Skip(skipRows).Take(pageSize);           
}

结果 SQL:

SELECT TOP (3) 
[Extent1].[ID] AS [ID], 
[Extent1].[Name] AS [Name], 
[Extent1].[LegalName] AS [LegalName], 
[Extent1].[YearEstablished] AS [YearEstablished], 
[Extent1].[DiskPath] AS [DiskPath], 
[Extent1].[URL] AS [URL], 
[Extent1].[SecureURL] AS [SecureURL], 
[Extent1].[UseSSL] AS [UseSSL]
FROM ( SELECT [Extent1].[ID] AS [ID], [Extent1].[Name] AS [Name], [Extent1].[LegalName] AS [LegalName], [Extent1].[YearEstablished] AS [YearEstablished], [Extent1].[DiskPath] AS [DiskPath], [Extent1].[URL] AS [URL], [Extent1].[SecureURL] AS [SecureURL], [Extent1].[UseSSL] AS [UseSSL], row_number() OVER (ORDER BY [Extent1].[Name] ASC) AS [row_number]
    FROM [dbo].[tec_Stores] AS [Extent1]
)  AS [Extent1]
WHERE [Extent1].[row_number] > 3
ORDER BY [Extent1].[Name] ASC

我真的很喜欢第一个选项的工作方式；传递 lambda 表达式以进行排序.有什么办法可以在 LINQ to SQL orderby 语法中完成同样的事情吗?我尝试使用 qry.OrderBy(sort).Skip(skipRows).Take(pageSize)，但这最终给了我与我的第一个代码块相同的结果.让我相信我的问题与 OrderBy 有某种联系.

I really like the way the first option works; Passing in a lambda expression for sort. Is there any way to accomplish the same thing in the LINQ to SQL orderby syntax? I tried using qry.OrderBy(sort).Skip(skipRows).Take(pageSize), but that ended up giving me the same results as my first block of code. Leads me to believe my issues are somehow tied to OrderBy.

====================================

====================================

必须在表达式中包装传入的 lambda 函数:

Had to wrap the incoming lambda function in Expression:

Expression<Func<Store,string>> sort

推荐答案

以下工作并实现了我一直在寻找的简单性:

The following works and accomplishes the simplicity I was looking for:

public IEnumerable<Store> ListStores(Expression<Func<Store, string>> sort, bool desc, int page, int pageSize, out int totalRecords)
{
    List<Store> stores = new List<Store>();
    using (var context = new TectonicEntities())
    {
        totalRecords = context.Stores.Count();
        int skipRows = (page - 1) * pageSize;
        if (desc)
            stores = context.Stores.OrderByDescending(sort).Skip(skipRows).Take(pageSize).ToList();
        else
            stores = context.Stores.OrderBy(sort).Skip(skipRows).Take(pageSize).ToList();
    }
    return stores;
}

为我修复它的主要事情是将 Func 排序参数更改为:

The main thing that fixed it for me was changing the Func sort parameter to:

Expression<Func<Store, string>> sort

这篇关于实体框架/Linq to SQL:跳过&拿的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！