将此SQL查询转换为LINQ

Question

SELECT 
CreationUtcTime, Speed, 
CONVERT(varchar, (CreationUtcTime - LAG(CreationUtcTime) OVER (ORDER BY CreationUtcTime)), 108) AS diff 
FROM 
assetstatusrecords 
WHERE 
Speed <> 0.00 
ORDER BY 
CreationUtcTime

我希望在不使用LINQTODB函数的情况下将此SQL查询转换为LINQ查询，并且我希望精确的差异，包括小时、天、秒、分钟，以便在稍后阶段对时间求和。

我尝试过的内容如下：

var records = _context.AssetStatusRecords
.OrderByDescending(s => s.CreationUtcTime)
.Where(s => s.AssetId.Equals(asset.Id)
&& s.CreationUtcTime >= from 
&& s.CreationUtcTime <= to 
&& s.Speed != 0)
.ToList();
var query = from rec1 in records
from rec2 in records.Where(r => rec1.SequentialId > r.SequentialId).DefaultIfEmpty()
group new { rec1, rec2 } by new { rec1.SequentialId, rec1.CreationUtcTime, rec1.Speed } into g
orderby g.Key.SequentialId
select new
{
g.Key.CreationUtcTime,
g.Key.Speed,
Diff = EntityFunctions.DiffDays(g.Max(p => p.rec2.CreationUtcTime), g.Key.CreationUtcTime)
};

LINQ 的模型类

class AssetStatusRecord : Entity
{
protected AssetStatusRecord()
{
}
public AssetStatusRecord(CoordinatesValue coordinates, double speed,
LengthValue distanceTravelled, Guid sensorId, Guid? assetId,
int? heading, Guid readingId, DateTime? sensorDateTime)
{
Coordinates = coordinates;
Speed = speed;
DistanceTravelled = distanceTravelled;
SensorId = sensorId;
AssetId = assetId;
Heading = heading;
ReadingId = readingId;
SensorDateTime = sensorDateTime;
}
public CoordinatesValue Coordinates { get; private set; }
public double Speed { get; private set; }
public LengthValue DistanceTravelled { get; private set; }
public Guid SensorId { get; private set; }
public Guid? AssetId { get; private set; }
public int? Heading { get; private set; }
public Guid ReadingId { get; private set; }
public DateTime? SensorDateTime { get; private set; }
}

Entity类如下：

public class Entity : IEntity
{
public Entity();
public Guid Id { get; protected set; }
public long SequentialId { get; protected set; }
public DateTime CreationUtcTime { get; protected set; }
public DateTime CreationLocalTime { get; protected set; }
}

这就是接口IEntity:

public interface IEntity
{
Guid Id { get; }
long SequentialId { get; }
DateTime CreationUtcTime { get; }
}

Answer 1

尝试以下查询：

var records = _context.AssetStatusRecords
.Where(s => s.AssetId == asset.Id
&& s.CreationUtcTime >= from 
&& s.CreationUtcTime <= to 
&& s.Speed != 0);
var query = 
from current in records
from prev in records
.Where(prev => current.CreationUtcTime <= prev.CreationUtcTime && prev.SequentialId < current.SequentialId)
.OrderByDescending(prev => prev.CreationUtcTime)
.Take(1)
.DefaultIfEmpty()
orderby current.CreationUtcTime
select new
{
current.CreationUtcTime,
current.Speed,
Diff = EntityFunctions.DiffDays(current.CreationUtcTime, prev.CreationUtcTime)
};