How can I fetch data from a time series with LINQ to SQL?
I have a database table full of time points and experimental values at these points in time. I need to get the values for an experiment and create a thumbnail showing the XY plot of its data. Since the actual dataset for each experiment is potentially 100,000 data points and my image is only 100 pixels wide, I want to sample the data, taking every tenth point in time for my image and ignoring the rest.
My current query (which fetches all data without fetching) looks something 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?
source to share
Every nth element will do this:
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 } );
Works with the Queryable.Where overload, which provides an index in sequence, so you can filter based on the index.
source to share
.Skip(n).Take(1)
Will return one example. Re-name it to get more points.
http://msdn.microsoft.com/en-us/library/bb386988.aspx
If performance becomes an issue and you have an int ID primary key containing sequential values, you can try returning all records that would evenly split the primary key with your n.
.Where(x => x.PK % n == 0)
source to share