Conversion of this sql query to LINQ query

Question

I want to convert this sql query into a linq query.

SELECT
  CreationUtcTime,
  Speed,
  convert((CreationUtcTime - LAG(CreationUtcTime) OVER (ORDER BY CreationUtcTime)), char) AS diff
FROM assetstatusrecords
WHERE
  Speed <> 0.00 and
  CreationUtcTime <= '2022-03-28' and
  CreationUtcTime >= '2022-02-21' and
  AssetId = '7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1'
ORDER BY 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 are 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 the Interface IEntity :-

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

This model class can be used to execute linq query which I am using in below query in comments

Answer 1

If you are using EF Core, you can execute such query via linq2db.EntityFrameworkCore extension. Note that I'm one of the creators.

With this extension you can use LAG in LINQ query:

var query = 
    from s in context.AssetStatusRecord.ToLinqToDB()  // switch LINQ Provider
    where s.Speed != 0 
        && s.CreationUtcTime <= endTime
        && s.CreationUtcTime >= startTime
        && s.AssetId == assetId
    orderby s.CreationUtcTime
    select new 
    {
        s.CreationUtcTime,  
        s.Speed,
        diff = s.CreationUtcTime - 
            Sql.Ext.Lag(s.CreationUtcTime)
                .Over()
                .OrderBy(s => s.CreationUtcTime)
                .ToValue()
    };

 var result = query.ToList();

Answer 2

If for any two records A and B such that A.SequentialId < B.SequentialId the condition A.CreationUtcTime <= B.CreationUtcTime is met, then without LAG function you can do something like this:

DateTime dateFrom = DateTime.Parse("2022-02-21");
DateTime dateTo = DateTime.Parse("2022-03-28");
string assetId = "7556d50c-95e5-4cd5-a31d-b4d6c6ab1fb1";

var records = 
    from rec in context.AssetStatusRecords
    where 
        rec.CreationUtcTime >= dateFrom && 
        rec.CreationUtcTime <= dateTo && 
        rec.Speed != 0 &&
        rec.AssetId == assetId
    select rec;

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 = EF.Functions.DateDiffDay(g.Max(p => p.rec2.CreationUtcTime), g.Key.CreationUtcTime)
    };

var results = query.ToList();

Note: code above works with Pomelo.EntityFrameworkCore.MySql provider.