LINQ for returning first in repeating sequences

I have a Measurements table as follows:

SourceId : int

TimeStamp: date/time

Measurement: int

Sample data looks like this (more on the asterisks below):

SID| TimeStamp | Measurement

10 | 02-01-2011 12:00:00 | 30 *

10 | 02-01-2011 12:10:00 | 30

10 | 02-开发者_如何学Go01-2011 12:17:00 | 32 *

10 | 02-01-2011 12:29:00 | 30 *

10 | 02-01-2011 12:34:00 | 30

10 | 02-01-2011 12:39:00 | 35 *

10 | 02-01-2011 12:46:00 | 36 *

10 | 02-01-2011 12:39:00 | 36

10 | 02-01-2011 12:54:00 | 36

11 | 02-01-2011 12:00:00 | 36 *

11 | 02-01-2011 12:10:00 | 36

11 | 02-01-2011 12:17:00 | 37 *

11 | 02-01-2011 12:29:00 | 38 *

11 | 02-01-2011 12:34:00 | 38

11 | 02-01-2011 12:39:00 | 37 *

11 | 02-01-2011 12:46:00 | 36 *

11 | 02-01-2011 12:39:00 | 36

11 | 02-01-2011 12:54:00 | 36

I need a LINQ query that will return only the rows when the Measurement value is different from the prior row having the same SourceId (i.e. each row marked with an asterisk). The table should be sorted by SourceId, then TimeStamp.

The data from the query will be used to plot a graph where each SourceId is a series. The source table has several million rows and the repeating measurements are in the thousands. Since these repeating measurement values don't make any difference to the resulting graph I'd like to eliminate them before passing the data to my graph control for rendering.

I have tried using Distinct() in various ways, and reviewed the Aggregate queries here http://msdn.microsoft.com/en-us/vcsharp/aa336746 but don't see an obvious solution.

Sometimes a plain old foreach loop will suffice.

var finalList = new List<MyRowObject>();
MyRowObject prevRow = null;

foreach (var row in myCollection)
{
    if (prevRow == null || (row.SID != prevRow.SID || row.Measurement != prevRow.Measurement))
    {
        finalList.Add(row);
    }
    prevRow = row;
}

Personally, I like the DistinctUntilChanged extension method that is included in the Rx Extensions library. It's very handy. As is the rest of the library, by the way.

But I do understand, you might not want to add a whole new dependency just for this. In this case, I propose Zip:

        sequence.Take(1).Concat(
        sequence.Zip( sequence.Skip(1), (prev,next) => new { item = next, sameAsPrevious = prev == next } )
            .Where( (x,index) => !x.sameAsPrevious )
            .Select( x => x.item )
        )

There's no way to do this in a single query in sql. Ergo there's no way to do this in a single query in linq to sql.

The problem is you need to compare each row to the "next" row. That's just not something that sql does well at all.

Look at the first five rows:

10 | 02-01-2011 12:00:00 | 30 *
10 | 02-01-2011 12:10:00 | 30
10 | 02-01-2011 12:17:00 | 32 *
10 | 02-01-2011 12:29:00 | 30 *
10 | 02-01-2011 12:34:00 | 30

You want to keep 2 records with 30 and remove 2 records with 30. That rules out grouping.