How can I subsample data from a time series with LINQ to SQL?

Question

I have a database table full of time points and experimental values at those time points. I need to retrieve the values for an experiment and create a thumbnail image showing an XY plot of its data. Because the actual data set for each experiment is potentially 100,000 data points and my image is only 100 pixels wide, I want to sample the data by taking every nth time point for my image and ignoring the rest.

My current query (which retrieves all the data without sampling) is something simple like this:

var points = from p in db.DataPoints
             where p.ExperimentId == myExperimentId
             orderby p.Time
             select new {
                            X = p.Time,
                            Y = p.Value
                        }

So, how can I best take every nth point from my result set in a LINQ to SQL query?

Answer 1

.Skip(n).Take(1)

Will return one sample point. Call it repeatedly to get more points.

http://msdn.microsoft.com/en-us/library/bb386988.aspx

If performance becomes an issue, and you have a primary identity key of type int containing consecutive values, you can try returning all records that will evenly divide the primary key by your n.

.Where(x => x.PK % n == 0)

Answer 2

You should you

.Skip(n).Take(100)

It skips how many every record you want it to skip and Takes 100 records.

HTH

Answer 3

This will do every nth element:

int nth = 100;
var points = db.DataPoints
           .Where(p => p.ExperimentId == myExperimentId)
           .OrderBy(p => p.Time)
           .Where( (p, index) => index % nth == 0 )
           .Select( p => new { X = p.Time, Y = p.Value } );

It works by using the Queryable.Where overload which provides an index in the sequence, so you can filter based off the index.