只是对“跳过和获取”应该如何工作感到好奇。我正在获得要在客户端看到的结果,但是当我挂接AnjLab SQL Profiler并查看正在执行的SQL时,它看起来好像是在查询并将整个行集返回给客户。
它真的返回所有行,然后在客户端使用LINQ排序并缩小范围吗?
我曾经尝试使用Entity Framework和Linq to SQL来完成;两者似乎具有相同的行为。
不确定是否有任何区别,但是我在VWD 2010中使用C#。
有见识吗?
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查询):
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]
经过一些进一步的研究,我发现以下方法可以达到我期望的方式:
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有关。
===================================
必须将传入的lambda函数包装在Expression中:
Expression<Func<Store,string>> sort
以下工作可以实现我一直在寻找的简单性:
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排序参数更改为:
Expression<Func<Store, string>> sort