如何将使用ROW_NUMBER()的查询转换为LINQ？

Question

我的表格有三列(sno, name, age)。我从数据库中检索出这个表格，还有一个额外的列(行号)，我使用了以下代码：

select * from (
    select ROW_NUMBER() over (order by SNo asc)as rowindex,SNo,Name,Age 
    from tblExample) 
    as example where rowindex between ((pageindex*10)+1) and ((pageindex+1)*10)

注意，pageindex是由用户传递的变量，它取一些整数值。

我的数据库是Sql Server 2008。我想使用Linq编写相同的查询。我该怎么做？

- Srikanth

---

LINQ to SQL？LINQ to EF？ - Simon Belanger

不使用拉取整个集合的方式，可能使用变量或重载的.Select操作符，我认为没有LINQ命令可以转换为此查询。 - Brad Christie

Answer 1

这不是直接的翻译，但是因为你的行号似乎只用于分页，所以尝试使用以下方法：

db.tblExample
  .OrderBy(t => t.SNo)
  .Select((t,i) => new {rowindex = i+1, t.SNo, t.Name, t.Age })
  .Skip(pageIndex * 10)
  .Take(10);

编辑

如果 Select((t,i) => ...) 无法工作，您可以尝试在填充查询后添加索引号来合成它：

db.tblExample
  .OrderBy(t => t.SNo)
  .Skip(pageIndex * 10)
  .Take(10)
  .AsEnumerable();
  .Select((t,i) => new {rowindex = (pageIndex*10)+i+1, t.SNo, t.Name, t.Age })

你最终应该得到与 SQL 相等的结果，除了 rowindex 字段将不会在 SQL 查询结果中出现，而是会在成员选择表达式中添加。

- D Stanley

---

无法使用.Select(Func<TSource, Int32, TResult>)重载来构建查询(不支持用于查询运算符'Select'的重载。)，结果必须先在本地存储。因此，如果这是一个DB上下文，您必须首先调用.ToList()（或等效方法）。 - Brad Christie

我建议不要在这里调用.ToList()之前调用.Select()；那会将所有内容加载到内存中，这会使分页失去意义。 相反，先执行.Skip()和.Take()，然后对其进行.ToList()处理，然后再将结果.Select()为输出格式。 - anaximander

@anaximander：必须将其加载到内存中才能使用重载；这就是我在“不支持的重载…”中所指的。 - Brad Christie

我知道这一点 - 我的观点是，如果你要用 .ToList() 或类似的方法将其加载到内存中，你应该在将其缩减到一页之后再这样做。我看过很多关于使用 .Skip() 和 .Take() 进行分页时为什么速度没有提升的 SO 问题，答案是提问者在分页之前调用了 .ToList()，从而使其有些多余。 - anaximander

Answer 2

您可以按照以下方式编写查询：

var index=1;
var pageIndex=1;
var pageSize = 10;
data.Select(x => new
{
    RowIndex = index++,
    Sno = x.Sno,
    Name = x.Name,
    Age = x.Age
}).OrderBy(x => x.Name)
.Skip(pageSize * (pageIndex - 1)).Take(pageSize).ToList();

- sree

---

表达式树中不应包含赋值运算符。如果没有本地缓存该列表，那么你是如何实现的？ - Brad Christie

@Simon Belanger：他的要求是使用 Linq 动态地实现 GridView 分页。在这种情况下，上述方案完美地发挥作用。你也可以尝试一下，对我来说它完美地工作了。 - sree

Answer 3

最佳情况（如果您需要元数据）：

var rows = db.tblExample           // database context
           .OrderBy(x => x.SNo)  // set an orderby
           .AsEnumerable()       // force query execution (now we have local dataset)
           .Select(x => new
           {
               rowindex = i,
               SNo = x.Sno,
               Name = x.Name,
               Age = x.Age
           });                   // now you have your original query

这里唯一的缺点是必须拉取整个数据集以获取此元数据。但是，如果您使用.Skip 和.Take，LINQ将自动将其转换为行号（只是您以后无法使用该元数据）。例如：

var pageIndex = /* ??? */;
var rows = db.tblExample
           .OrderBy(x => x.SNo)
           .Skip(pageIndex * 10).Take(10);

这应该给你类似于：

SELECT [t1].[SNo] AS [SNo],
       [t1].[Name] AS [Name],
       [t1].[Age] AS [Age]
FROM (
  SELECT ROW_NUMBER() OVER (ORDER BY [t0].[tblExample], [t0].[SNo]) AS [ROW_NUMBER],
         [t0].[SNo],
         [t0].[Name],
         [t0].Age]
  FROM   [tblExample] AS [t0]
) AS [t1]
WHERE [t1].[ROW_NUMBER] BETWEEN @p0 + 1 AND @p0 + @p1
ORDER BY [t1].[ROW_NUMBER]

现在row_number在SQL内部使用，但您无法在代码中访问它。

如果您真的想要这种访问权限，最好手动将查询发送到服务器。一个适用于此类事情的库是dapper-dot-net，它看起来像：

class MyObject {
    public Int32 rowindex;
    public Int32 SNo;
    public String Name;
    publig Int32 Age;
}

/* build "connection" */

connection.Query<MyObject>("SELECT * FROM (SELECT ROW_NUMBER() ... ) ...");

- Brad Christie

---

实际上，您可以使用.Select()的一个鲜为人知的重载来获取行号，它看起来像这样：.Select((x, i) => new {Index = i, Item = x})，它会返回一个对象，其中 obj.Index 就是行号。 - anaximander

@anaximander：不过该重载没有SQL翻译，所以只有在数据是本地的情况下才有效。这意味着如果您有10k条记录，则必须在可枚举序列中拥有全部10k条记录，然后才能使用该重载。（导致错误的内容已在D Stanley的回答评论中列出） - Brad Christie

确实如此。但是，如果您正在使用.Skip()和.Take()，那么很有可能您正在使用它们进行分页，而分页的整个目的就是将结果集分成可管理的块。如果您的页面足够大，以至于将集合加载到内存中成为问题，则您正在错误地进行分页。 - anaximander