Fetch every nth row with LINQ

c#, entity-framework, linq

Solution

You can use the Select overload that includes the item index of enumerations. Something like this should do the trick --

var data = myDataLogEnumeration.
        Select((dt,i) => new { DataLog = dt, Index = i }).
        Where(x => x.Index % nth == 0).
        Select(x => x.DataLog);

If you need to limit the query with a Where or sort with OrderBy, you must do it before the first Select, otherwise the indexes will be all wrong --

var data = myDataLogEnumeration.
        Where(dt => dt.DateTime > dateLimit).
        OrderBy(dt => dt.SomeField).
        Select((dt,i) => new { DataLog = dt, Index = i }).
        Where(x => x.Index % nth == 0).
        Select(x => x.DataLog);

Unfortunately, as juharr commented, this overload is not supported in Entity Framework. One way to deal with this is to do something like this --

var data = entity.DataLogSet.
        Where(dt => dt.DateTime > dateLimit).
        OrderBy(dt => dt.SomeField).
        ToArray().
        Select((dt,i) => new { DataLog = dt, Index = i }).
        Where(x => x.Index % nth == 0).
        Select(x => x.DataLog);

Note the addition of a `ToArray()`. This isn't ideal though as it will force loading all the data that matches the initial query before selecting only every nth row.

Problem

We have a table in our SQL database with historical raw data I need to create charts from. We access the DB via Entity Framework and LINQ. For smaller datetime intervals, I can simply read the data and generate the charts: ``` var mydata = entity.DataLogSet.Where(dt => dt.DateTime > dateLimit); ``` But we want to implement a feature where you can quickly "zoom out" from the charts to include larger date intervals (last 5 days, last month, last 6 months, last 10 years and so on and so forth.) We don't want to chart every single data point for this. We want to use a sample of the data, by which I mean something like this -- - Last 5 days: chart every data point in the table - Last month: chart every 10th data point in the table - Last 6 months: chart every 100th data point The number of data points and chart names are only examples. What I need is a way to pick only the "nth" row from the database.

Original source