LINQ to SQL Every Nth Row From Table

Question

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.

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.

Answer 1

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

You could also swap out the 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);

Answer 2

Once upon a time, there was no such thing as Row_Number, and yet such queries were possible. Behold!

var query =
  from c in db.Customers
  let i = (
    from c2 in db.Customers
    where c2.ID < c.ID
    select c2).Count()
  where i%3 == 0
  select c;

This generates the following Sql

SELECT [t2].[ID], [t2]. --(more fields)
FROM (
    SELECT [t0].[ID], [t0]. --(more fields)
(
        SELECT COUNT(*)
        FROM [dbo].[Customer] AS [t1]
        WHERE [t1].[ID] < [t0].[ID]
        ) AS [value]
    FROM [dbo].[Customer] AS [t0]
    ) AS [t2]
WHERE ([t2].[value] % @p0) = @p1

Answer 3

Here's an option that works, but it might be worth checking that it doesn't have any performance issues in practice:

var nth = 3;
var ids = Table
            .Select(x => x.Id)
            .ToArray()
            .Where((x, n) => n % nth == 0)
            .ToArray();

var nthRecords = Table
                   .Where(x => ids.Contains(x.Id));

Answer 4

There really doesn't seem to be an easy way to do this:

How do I add ROW_NUMBER to a LINQ query or Entity?

How to find the ROW_NUMBER() of a row with Linq to SQL

But there's always:

peopleToFilter.AsEnumerable().Where((x,i) => i % AmountToSkipBy == 0)

NOTE: This still doesn't execute on the database side of things!

Answer 5

Just googling around a bit I haven't found (or experienced) an option for Linq to SQL to directly support this.

The only option I can offer is that you write a stored procedure with the appropriate SQL query written out and then calling the sproc via Linq to SQL. Not the best solution, especially if you have any kind of complex filtering going on.

Answer 6

This will do the trick, but it isn't the most efficient query in the world:

var count = query.Count();
var pageSize = 10;
var pageTops = query.Take(1);
for(int i = pageSize; i < count; i += pageSize)
{
    pageTops = pageTops.Concat(query.Skip(i - (i % pageSize)).Take(1));
}
return pageTops;

It dynamically constructs a query to pull the (nth, 2*nth, 3*nth, etc) value from the given query. If you use this technique, you'll probably want to create a limit of maybe ten or twenty names, similar to how Google results page (1-10, and Next), in order to avoid getting an expression so large the database refuses to attempt to parse it.

If you need better performance, you'll probably have to use a stored procedure or a view to represent your query, and include the row number as part of the stored proc results or the view's fields.