c# - 相关的 LINQ 查询优化

Question

我正在尝试获取已更改的记录，其中车辆的点火已更改。这是我的 SQL 表。

即我想获取记录 47890 和记录 47879。

我编写了以下相关的 LINQ 查询。

var sData = (from log in db.GSMDeviceLogs
             where log.Vehicle.VehicleId == vehicleId
             where log.IgnitionOn != (from prevLog in db.GSMDeviceLogs
                                     where prevLog.Vehicle.VehicleId == vehicleId
                                     where prevLog.DateTimeOfLog < log.DateTimeOfLog
                                     orderby prevLog.DateTimeOfLog descending
                                     select prevLog.IgnitionOn).FirstOrDefault()
             orderby log.DateTimeOfLog ascending
             select new { LogId = log.GSMDeviceLogId,
                          Ignition = log.IgnitionOn,
                          Date = log.DateTimeOfLog,
                          Location = log.Location }).ToList();

它给出了以下异常:

An exception of type 'System.Data.Entity.Core.EntityCommandExecutionException' occurred in EntityFramework.SqlServer.dll but was not handled in user code

内部异常说:

{"Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding."}

我希望这是因为查询效率很低，执行时间太长。我们如何优化 LINQ 查询？

Answer 1

做一个自连接:

var sData = (from log in db.GSMDeviceLogs
             join log2 in db.GSMDeviceLogs on log.GSMDeviceLogId equals log2.GSMDeviceLogId - 1
             where log.Vehicle.VehicleId == vehicleId
                 && log.IgnitionOn != log2.IgnitionOn
             orderby log.DateTimeOfLog ascending
             select new { LogId = log.,
                          Ignition = log.IgnitionOn,
                          Date = log.DateTimeOfLog,
                          Location = log.Location }).ToList();