將此sql查詢轉換為LINQ查詢

我想將此 sql 查詢轉換為 linq 查詢。

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

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

物體類如下： -

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

和介面 IEntity ：-

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

這個模型類可用于執行我在下面的評論中使用的 linq 查詢

uj5u.com熱心網友回復：

如果您使用的是 EF Core，則可以通過linq2db.EntityFrameworkCore擴展執行此類查詢。請注意，我是創作者之一。

使用此擴展，您可以LAG在 LINQ 查詢中使用：

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

uj5u.com熱心網友回復：

如果對于任何兩條記錄A并且B滿足A.SequentialId < B.SequentialId條件A.CreationUtcTime <= B.CreationUtcTime，那么在沒有LAG功能的情況下，您可以執行以下操作：

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

注意：上面的代碼適用于Pomelo.EntityFrameworkCore.MySql 提供程式。

標籤：sql 林克 ef-core-3.1 sql 到 linq 的转换

上一篇：如何將SQL傳輸到LINQ或其他方法

下一篇：使用LINQtoGroupBy兩列并用它們構建字典？