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

What have you tried? can you get a LINQ query without the `LAG` piece. Here's a link showing some examples of using `LAG` in LINQ https://stackoverflow.com/questions/55924525/is-there-a-linq-to-entities-equivalent-for-the-lag-function-in-sql — Nick.Mc, Mar 30 '22 at 08:41
I am trying the first time, couldn't go beyond this:- ``var obds = _context.AssetStatusRecords.OrderByDescending(s => s.CreationUtcTime) .Where(s => s.AssetId.Equals(asset.Id) && s.CreationUtcTime >= from && s.CreationUtcTime <= to).ToList();`` — Shalabh Mishra, Mar 30 '22 at 09:01
Do you have any sequential IDs in the AssetStatusRecords table? — Ruslan Gilmutdinov, Mar 30 '22 at 10:36
This query is not convertible to EF. Window functions are not supported by EF Core. It is supported only by `linq2db` or it's EF Core extension. — Svyatoslav Danyliv, Mar 30 '22 at 10:49
Please if you can convert this using linq2db that will be helpful enough for me — Shalabh Mishra, Mar 30 '22 at 10:53
Please add Model classes, LINQ query is built form classes and properties. — Svyatoslav Danyliv, Mar 30 '22 at 10:56

score 2 · Accepted Answer · answered Mar 30 '22 at 11:37

2

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

answered Mar 30 '22 at 11:37

Svyatoslav Danyliv

21,911
3
16
32

What do you mean? Who should embed something instead of you? – Svyatoslav Danyliv Mar 30 '22 at 11:51
Select appropriate package version. It depends on EF Core version. – Svyatoslav Danyliv Mar 30 '22 at 11:55
Which version of EF Core do you use? You do not need to include something in startup, just add package and `.ToLinqToDB()` call. – Svyatoslav Danyliv Mar 30 '22 at 12:00
Check that package is installed. For [EF Core 3.1](https://www.nuget.org/packages/linq2db.EntityFrameworkCore/3.17.0) – Svyatoslav Danyliv Mar 30 '22 at 12:05
My version for EntityFrameworkCore.Relational is 3.1.6 – Shalabh Mishra Mar 30 '22 at 12:34

Ruslan Gilmutdinov · Answer 2 · 2022-03-31T07:49:02.633

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.

For EF Core 3.1 you should use `EF.Functions.DateDiffDay` instead of `DbFunctions.DiffDays` (which is for EF 6.x). — Ruslan Gilmutdinov, Mar 31 '22 at 05:46

Conversion of this sql query to LINQ query

2 Answers2