LINQ to SQL 从表中每第 N 行

2022-01-07 00:00:00 sql sql-server linq linq-to-sql

有人知道如何编写 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, 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，但我不知道用于获取正确 SQL 的 LINQ 语句.

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:

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);

相关文章