Conversion of this SQL Query to LINQ-CodePudding

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

I want this SQL query to be converted to LINQ query without using LINQTODB functions and I want exact difference including hours, days, seconds, minutes such that I want to sum the time at later stage.

What I have tried is below:

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)
                   };

Model class for 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; }
}

And the Entity class is as follows:

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; }
}

And this is the interface IEntity:

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

CodePudding user response：

Try the following query:

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)
    };