LINQ select the next record with each corresponding result

Question

I have objects from which measurements are saved in one table. I want to know how long the object has been in a certain state for a period of time.

Thus, in addition to getting the record in the right state, I need to connect it with the next measurement made from the same object in order 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 that this is not very effective, especially when I use Compact Edition 3.5.

Is there a way to go to the next dimension through m, or can I somehow use orderby or a group to select the next one by Id? Or even make a join proposal easier?

Answer 1

, . , :

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: , . :

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');