LINQ Query Design

Question

How to write a LINQ query to fetch only the boundary records for a condition. For example, Consider the below database table which represents tracking data received from a vehicle:

enter image description here

I wish to fetch only record 47890 and 47880. Which will give the start time and end time when vehicle was stopped.

Right now, in my query i fetch all the records and then take the first and the last. Also, the query need to be generic, I may have multiple stops for a vehicle. For Example:

Stop1 : 11:00 AM to 1:00 PM

Stop2 : 3:00 PM to 3:30 PM

and so on.

Here is the code I have written so far:

var sData = db.Vehicles
            .Where(v => v.VehicleId == vehicleId)
            .SelectMany(v => v.GsmDeviceLogs)
            .Where(gs => gs.DateTimeOfLog > startDate && gs.DateTimeOfLog < endDate && gs.Speed < zeroSpeed && !gs.IgnitionOn)
            .Select(v => new
            {
                DateTimeOfLog = v.DateTimeOfLog,
                Location = v.Location
            }).OrderBy(gs => gs.DateTimeOfLog).ToList();

Is your current query returning wrong results? I would extract the conditions for the the start and date time and the zero speed after the `!gs.IgnitionOn` (in a second Where) - this way you will have only the zeroes and you could always take First and Last entry from the resulting query. As you describe it, the query should be very simple and you are almost there. — keenthinker, Mar 28 '14 at 06:41
But the first and last may be many. If vehicle stops twice in a day then there should be 2 pair of First and Last. — Ashish Charan, Mar 28 '14 at 06:44
The query works. But will Only Give me 1 first and last pair. If in case there are two stops. It will combine them into one. I want two first and last pairs for two stops. — Ashish Charan, Mar 28 '14 at 06:46

score 2 · Answer 1 · edited May 23 '17 at 10:26

The next result is tested with LinqPad. It can be optmized with T-SQL and used via stored procedure.

var indexedRowsAsc = arr.OrderBy(r => r.DateTimeOfLog)
                        .Select((r, index) => new { Row = r, Index = index});

// find intersection of current row and next row with  condition (IgnitionOn)
// intersection can ignore first and last row 
var foundRows = (from a in indexedRowsAsc
                 from b in indexedRowsAsc
                 where a.Index == (b.Index -1) && 
                       a.Row.IgnitionOn != b.Row.IgnitionOn
                 select new {a, b}
                 ).ToArray();

var firstRow = arr.OrderBy(r => r.DateTimeOfLog).FirstOrDefault();
var lastRow = arr.OrderByDescending(r => r.DateTimeOfLog).FirstOrDefault();

// union found rows with first and last row
var distinctFoundRows = foundRows.Select(fr => fr.a.Row)
                        // comparer can be added for union for proper distinct gathering
                        .Union(foundRows.Select(fr => fr.b.Row))
                        // add first and last row
                        .Union(new Vehicle[]{firstRow})
                        .Union(new Vehicle[]{lastRow})
                        .Where(r => r!= null)
                        .OrderBy(r => r.DateTimeOfLog)
                        .ToArray();

// find result by grouping rows where IgnitionOn == 0 
int groupId = 1;
var result = distinctFoundRows
             .Select(row => new {Row =row, GroupId = (row.IgnitionOn == 0? groupId: ++groupId)})
             .Where(res => res.Row.IgnitionOn == 0)
             .GroupBy(res => res.GroupId)
             .Select(gr => new {First = gr.First().Row, Last = gr.Last().Row})
             .ToArray();

The secret of finding changed values in column is self joining.

enter image description here

LINQ Query Design

1 Answers1