LINQ to SQL从表的第N行开始 [英] LINQ to SQL Every Nth Row From Table

问题描述

有人知道如何编写LINQ to SQL语句以从表中返回第n行吗?我需要在分页数据网格中的每页顶部获取该项目的标题，以便快速进行用户扫描.因此，如果我想要第一个记录，那么之后的每个第三个记录都来自以下名称:

Anybody know how to write a LINQ to SQL statement to return every nth row from a table? I'm needing to get the title of the item at the top of each page in a paged data grid back for fast user scanning. So if i wanted the first record, then every 3rd one after that, from the following names:

Amy ，Eric，Jason， Jo ，John，Josh， Maribel ，Paul，Steve， Tom

Amy, Eric, Jason, Joe, John, Josh, Maribel, Paul, Steve, Tom

我会得到艾米，乔，马里贝尔和汤姆.

I'd get Amy, Joe, Maribel, and Tom.

我怀疑可以做到这一点... LINQ to SQL语句已经结合排序和分页调用了ROW_NUMBER()SQL函数.我只是不知道如何取回第n个项目.该SQL语句类似于WHERE ROW_NUMBER MOD 3 = 0，但我不知道该LINQ语句用于获取正确的SQL.

I suspect this can be done... LINQ to SQL statements already invoke the ROW_NUMBER() SQL function in conjunction with sorting and paging. I just don't know how to get back every nth item. The SQL Statement would be something like WHERE ROW_NUMBER MOD 3 = 0, but I don't know the LINQ statement to use to get the right SQL.

推荐答案

有时，TSQL是必经之路.我会在这里使用ExecuteQuery<T>

Sometimes, TSQL is the way to go. I would use ExecuteQuery<T> here:

    var data = db.ExecuteQuery<SomeObjectType>(@"
SELECT * FROM 
(SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS [__row]
FROM [YourTable]) x WHERE (x.__row % 25) = 1");

您还可以换出n:

    var data = db.ExecuteQuery<SomeObjectType>(@"
DECLARE @n int = 2
SELECT * FROM 
(SELECT *, ROW_NUMBER() OVER (ORDER BY id) AS [__row]
FROM [YourTable]) x WHERE (x.__row % @n) = 1", n);

这篇关于LINQ to SQL从表的第N行开始的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！