How to compare previous record column value with current record column value of a list using LINQ query in C#

Question

Compare previous record column value with current record column value in c# linq

My linq query is as follows,

var list = (from v in db.VehicleLocation
            where v.VehicleId == vehicleId
            select new VehicleLocationModel {
                        Id = v.Id,
                        Location = v.Location,
                        DateTimestamp = v.DateTimestamp,
                        DiffTimestamp = v.DateTimestamp - previoustimestamp
            }).OrderBy(x => x.DateTimestamp).ToList();

please help me...

Duplicate of [this](https://stackoverflow.com/questions/4460106/how-do-access-previous-item-in-list-using-linq) SO question. — Jeroen Heier, Feb 19 '18 at 05:00

score 0 · Answer 1 · answered Feb 19 '18 at 05:50

You can try something like that:

var list = (from v in db.VehicleLocation.Where(x.VehicleId == vehicleId)
            from v2 in db.VehicleLocation.Where(x.VehicleId == vehicleId)
            where v.DateTimestamp > v2.previoustimestamp
            group v2 by new { v.Id, v.Location, v.DateTimestamp } into sub
            select new VehicleLocationModel
            {
                Id = sub.Key.Id,
                Location = sub.Key.Location,
                DateTimestamp = sub.Key.DateTimestamp,
                DiffTimestamp = sub.Key.DateTimestamp - sub.Max(x => x.DateTimestamp)
            }).OrderBy(x => x.DateTimestamp).ToList();

score 0 · Answer 2 · answered Feb 20 '18 at 10:29

So you have a sequence of VehicleLocations, some of them are from the vehicle with vehicleId. Every VehicleLocation has a TimeStamp.

You want, among with some other properties all VehicleLocations of the vehicle with vehicleId, together with their TimeStamps and a value for DiffTimeStamp, which is the difference between the TimeStamp and something what you call "previous timestamp"

First you'll have to define the previous time stamp. I guess, that you mean that if you'd order all VehicleLocations of one particular Vehicle by ascending timestamp, that the "previous time stamp" of any but the first VehicleLocation is the timestamp of the VehicleLocation prior to the current one.

To make the definition complete: the previous time stamp of the first element is the timestamp of the element itself. This makes the DiffTimeStamp the difference between the current time stamp and the previous time stamp. DiffTimeStamp of the first item in the sequence is TimeSpan.Zero

I think the fastest method would be to transfer the ordered sequence of (the requested properties) of all VehicleLocations for the vehicle with vehicleId to local memory and then yield return the requested data:

IEnumerable<VehicleLocationModel> FetchModelsById(int vehicleId)
{
    var vehicleLocations = db.VehicleLocations
        .Where(vehicleLocation => vehicleLocation.VehicleId == vehicleId)
        .Select(vehicleLocation => new VehicleLocationModel()
        {
            Id = vehicleLocation.Id,
            Location = vehicleLocation.Location,
            DateTimeStamp = vehicleLocation.DateTimestamp,
        })
        .OrderBy(vehicleLocation => vehicleLocation.TimeStamp);

Note: all values but DiffTimeStamp are filled. We'll only yield return VehicleLocations if the collection contains elements. The DiffTimeStamp of the first element will equal TimeSpan.Zero:

Continuing:

        // only yield return something if there are elements:
        if (vehicleLocations.Any())
        {
            // the first one will be different:
            var firstElement = vehicleLocations.First();
            firstElement.DiffTimeStamp = TimeSpan.Zero;
            yield return firstElement;

            // the rest of the elements:
            DateTime previousTimeStamp = firstElement.DateTimeStamp;
            foreach (VehicleLocation location in vehicleLocations.Skip(1))
            {
                 location.DiffTimeStamp = location.DateTimeStamp - previousTimeStamp;
                 yield return location;
                 previousTimeStamp = location.DateTimeStamp;
            }
        }
    }
}

The nice thing about this solution (apart from that its easy to understand) is that the database has to do less work, it has to transfer less bytes to your local process (the slowest part), and both the original sequence on the database side and the resulting sequence on local side are iterated only once. This is at the cost that your local process has to do the subtractions of the DatetimeStamp and the PreviousDateTimeStamp. But this is done at utmost once per iterated element

How to compare previous record column value with current record column value of a list using LINQ query in C#

2 Answers2