C# Query Select first since value changed

Question

I have a database table that looks like this:

Now. This is a Status History indicator for DetectorID 1541.

I only want to show 1 row at my website. To do this i excecute the following query.

[Query]
public IQueryable<ScannerStatusHistory> GetScannerStatusHistoryy(int detectorID)
{
    return ObjectContext.ScannerStatusHistories.Where(t => t.DetectorID == detectorID).OrderByDescending(d => d.TimeStamp).Take(1); ;
}

So what it does, it grabs the newest row (Based on TimeStamp), and shows it.

This will give me the results of ScannerStatusHistoryID 61.

But, what i would like to have, is the row of the last time a value got changed.

As you can see on ScannerStatusHistoryID 54, RC3 had a value of 4. Then on ScannerStatusHistoryID 57, it changed back to 3.

Since then, the values did not change.
What i would like to have then, is the query to grab the ScannerStatusHistoryID 57. Untill the value changes again, then i want it to grab the first of that one.

How do i achieve this? i was thinking about counting the results where it matched the last query. However, in this example, it will return 7 results (Since the first 4 are the same as the last 3). And so it wont give you the right result.

Answer 1

You can write your query like this to achive it:

//return ObjectContext.ScannerStatusHistories.Where(t => t.DetectorID == detectorID).OrderByDescending(d => d.TimeStamp).Take(1);
    var lastRow = ObjectContext.ScannerStatusHistories.Where(t => t.DetectorID == detectorID).OrderByDescending(d => d.TimeStamp).FirstOrDefault();
//Get last changed row
    if (lastRow != null)
    {
        var lastChangeRow = ObjectContext.ScannerStatusHistories
        .Where(t => t.DetectorID == detectorID
                && (t.HBD1 != lastRow.HBD1 || t.HBD2 != lastRow.HBD2 || t.HWD1 != lastRow.HWD1 || t.HWD2 != lastRow.HWD2 || t.RC1 != lastRow.RC1 || t.RC2 != lastRow.RC2 || t.RC3 != lastRow.RC3 || t.RC4 != lastRow.RC4))
            .OrderByDescending(d => d.TimeStamp)
            .FirstOrDefault();
    //Return next row
        if (lastChangeRow != null)
        {
            return ObjectContext.ScannerStatusHistories
            .Where(x => lastChangeRow.TimeStamp < x.TimeStamp
                && x.DetectorID == detectorID)
            .OrderBy(d => d.TimeStamp)
            .Take(1);
        }
    }
return ObjectContext.ScannerStatusHistories.Where(t => t.DetectorID == detectorID).OrderBy(d => d.TimeStamp).Take(1);

But maby it's better just not add row if nothing is changed?