LINQ select next record with each matching result

Question

I have objects from which measurements are saved to a single table. I want to find out how long an object has been in a certain state within a time period.

So in addition to getting the record with the wanted state I need to pair it up with the next measurement made from the same object to calculate the time between them.

I came up with this monster:

// Get the the object entry from Database
MeasuredObject object1;
try
{
   object1 = (MeasuredObject)(from getObject in db.MeasuredObject where wantedObject.Id.Equals(getObject.Id) select getObject).Single();
}
catch (System.ArgumentNullException e)
{
   throw new System.ArgumentException("Object does not exist", "wantedObject", e);
}

// Get every measurement which matches the state in the time period and the next measurement from it
var pairs = (from m in object1.Measurements
             join nextM in object1.Measurements
             on (from next in object1.Measurements where (m.Id < next.Id) select next.Id).Min() equals nextM.Id
             where 'm is in time period and has required state'
             select new { meas = m, next = nextM });

I would say this doesn't seem very efficient especially when I'm using Compact Edition 3.5.

Is there any way to navigate to the next measurement through m or could I somehow use orderby or group to select next by Id? Or even make the join clause simpler?

Answer 1

From the posted code looks like you are working with in memory collection. If that's true, then the following should be sufficient:

var items = (from m in object1.Measurements
             where 'm is in time period and has required state'
             orderby m.Id
             select m)
            .ToList();

var pairs = items.Select((item, index) => new
            {
                meas = item,
                next = index + 1 < items.Count ? items[index + 1] : null
            }); 

EDIT: The above is not the exact equivalent of your code because it applies the filter before pairing the items. The exact optimized equivalent would be like this:

var items = object1.Measurements.OrderBy(m => m.Id).ToList();

var pairs = items.Select((item, index) => new
            {
                meas = item,
                next = index + 1 < items.Count ? items[index + 1] : null
            })
            .Where(pair => 'pair.meas is in time period and has required state');