将上一个记录的列值与当前记录列值在c#linq
中我的linq查询如下,
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();
请帮助我...
您可以尝试这样的东西:
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();
因此,您有一系列车辆,其中一些是带有载体的车辆。每个车辆插入都有一个时间戳。
您想要的是,在其他某些属性中,车辆的所有车辆与车辆的车辆以及它们的时间戳以及difftimestamp的价值,这是时间戳和您所说的"以前的时间戳"
之间的区别首先,您必须定义以前的时间戳。我想,您的意思是,如果您通过上升时间戳订购一辆特定车辆的所有车辆,则除了第一个车辆以外的任何车辆的"以前的时间戳记"是当前车辆之前的车辆时间戳。
使定义完成:第一个元素的上一个时间戳是元素本身的时间戳。这使difftimestamp在当前时间戳记和上一个时戳之间差异。序列中第一项的difftimestamp是timespan.zero
我认为最快的方法是转移所有车辆的车辆车辆的(请求属性(的有序序列,并带有载体的当地内存,然后产生返回请求的数据:
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);
注意:除了difftimestamp以外,所有值均已填充。如果收集包含元素,我们只会产生返回车辆的收益。第一个元素的difftimestamp将等于Zero。零:
继续:
// 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;
}
}
}
}
关于此解决方案的好处(除此之外,它易于理解(是数据库必须进行更少的工作,它必须将较少的字节传输到您的本地过程(最慢的部分(,并且两者都必须在数据库侧和本地侧的结果序列仅迭代一次。这是您本地流程必须进行的,需要进行DateTimestAmp和上一个DateTimestamp的减法。但这是每个迭代元素