Conversion of this sql query to LINQ query-CodePudding

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

CodePudding user response：

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

CodePudding user response：

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

var results = query.ToList();