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