C # query Select first since value has changed

Question

I have a database table that looks like this:

Now. This is a status indicator for DetectorID 1541.

I want to show only one line on my site. To do this, run 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 he does is he grabs the newest line ( Based on TimeStamp ) and shows it.

This will give me ScannerStatusHistoryID 61 results.

But, what I would like to have is the line the last time the value changed.

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

Since then, the values ​​have not changed.
Then I would like to receive a request to capture ScannerStatusHistoryID 57 . Until the value changes again, I want him to capture the first one.

How do I achieve this? I was thinking of calculating the results when it matched the last query. However, in this example, it will return 7 results (since the first 4 match the last 3). And so he will not give you the correct result.

Answer 1

You can write your request as follows:

 //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 is better not to just add a line if nothing has changed ?