实体框架/ LINQ到SQL：跳过和放大器;采取 [英] Entity Framework/Linq to SQL: Skip & Take
问题描述
只是好奇，如何跳过和放大器;就拿都应该工作。我得到我想要在客户端看到的结果，但是当我勾了AnjLab SQL事件探查器，看看正在执行它看起来好像是在查询和整个行集返回的SQL客户端。
难道真的返回所有的行，然后进行排序，并在客户端缩小东西与LINQ？
我试着做它用实体框架和LINQ to SQL;两者似乎有相同的行为。
不知道这有什么差别，但我使用VWD 2010 C＃。
任何见解？
公开的IEnumerable＆LT;店＆GT; ListStores（Func键＆LT;店，串＆GT;排序，布尔递减，诠释页，诠释的pageSize，OUT INT总记录）
{
VAR上下文=新TectonicEntities（）;
总记录= context.Stores.Count（）;
INT skipRows =（页 - 1）* pageSize的;
如果（降序）
返回context.Stores.OrderByDescending（排序）.Skip（skipRows）。取（的pageSize）.ToList（）;
返回context.Stores.OrderBy（排序）.Skip（skipRows）。取（的pageSize）.ToList（）;
}
产生的SQL（注：我不包括统计查询）：
SELECT
[Extent1]。[ID] AS [ID]
[Extent1]。[名称] [名称]
[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]
在一些进一步的研究，我发现下面的工作我希望它的方式：
公开的IEnumerable＆LT;店＆GT; ListStores（Func键＆LT;店，串＆GT;排序，布尔递减，诠释页，诠释的pageSize，OUT INT总记录）
{
VAR上下文=新TectonicEntities（）;
总记录= context.Stores.Count（）;
INT skipRows =（页 - 1）* pageSize的;
VAR QRY =从S在context.Stores排序依据s.Name升选择s;
返回qry.Skip（skipRows）。取（pageSize的）;
}
产生的SQL：
SELECT TOP（3）
[Extent1]。[ID] AS [ID]
[Extent1]。[名称] [名称]
[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]。[名称] [名称]，[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]。[名] ASC）AS [ROW_NUMBER]
FROM [DBO]。[tec_Stores] AS [Extent1]
）AS [Extent1]
WHERE [Extent1] [ROW_NUMBER] GT。 3
ORDER BY [Extent1]。[名] ASC
我真的很喜欢第一种方案的工作方式;在传递一个lambda EX pression进行排序。有没有办法做到在LINQ同样的事情到SQL排序依据语法？我试着用qry.OrderBy（排序）.Skip（skipRows）。取（pageSize的），但最终给我相同的结果为code我的第一个块。使我相信我的问题在某种程度上依赖于排序依据。
====================================
问题解决了
只好在包装前pression传入的lambda函数：
防爆pression＆LT; Func键＆LT;店，串＆GT;＆GT;分类
下面的工作，并完成我一直在寻找简单：
公开的IEnumerable＆LT;店＆GT; ListStores（前pression＆LT; Func键＆LT;店，串＆GT;＆GT;排序，布尔递减，诠释页，诠释的pageSize，OUT INT总记录）
{
名单＆LT;店＆GT;商店=新的名单，其中，商店和GT;（）;
使用（VAR上下文=新TectonicEntities（））
{
总记录= context.Stores.Count（）;
INT skipRows =（页 - 1）* pageSize的;
如果（降序）
商店= context.Stores.OrderByDescending（排序）.Skip（skipRows）。取（的pageSize）.ToList（）;
其他
商店= context.Stores.OrderBy（排序）.Skip（skipRows）。取（的pageSize）.ToList（）;
}
返回专卖店;
}
这是固定的，对我来说是改变Func键排序参数，主要的事情：
防爆pression＆LT; Func键＆LT;店，串＆GT;＆GT;分类
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.
Is it really returning all the rows then sorting and narrowing down stuff with LINQ on the client side?
I've tried doing it with both Entity Framework and Linq to SQL; both appear to have the same behavior.
Not sure it makes any difference, but I'm using C# in VWD 2010.
Any insight?
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();
}
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);
}
Resulting 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
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.
====================================
PROBLEM SOLVED
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;
}
The main thing that fixed it for me was changing the Func sort parameter to:
Expression<Func<Store, string>> sort
这篇关于实体框架/ LINQ到SQL：跳过和放大器;采取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
