entity-framework - 加载时间过长

Question

我正在使用 MVC5 和 EF6 开发应用程序。我正在为数据库使用 SQL Server Express。我在数据库中有两个表/实体。

1. 车辆 - 包含有关车辆的信息
2. VehicleLog - 包含从车载 GPS 跟踪器接收的数据。

我的 VehicleLog 表目前有大约 20K 条记录，从表中获取特定车辆的数据大约需要 80 秒。例如:我尝试获取最后一条记录以显示车辆的当前状态(即移动或停止)，这需要超过 1 分半钟的时间。

Vehicle log表的记录数会随着时间的推移而增加。

当我尝试使用服务器资源管理器打开表时，它会在 5-10 秒内显示所有数据。任何人都可以帮助我在页面上快速加载详细信息吗？

感谢阅读并关注问题。

我的代码:

 public ActionResult Dashboard()
    {
        ApplicationUser au = db.Users.Find(currentUser);

        var myVehicles = from vehicle in au.Vehicles.ToList()
                         where vehicle.License.ExpiryDate >= DateTime.Now && !vehicle.IsDeleted
                         select new CurrentVehicleStatus
                         {
                             VehicleName = vehicle.Name,
                             DriverName = vehicle.Driver != null ? vehicle.Driver.Name : "No driver",
                             DriverId=vehicle.Driver != null ? vehicle.Driver.DriverId : 0,
                             VehicleId = vehicle.VehicleId,
                             VehicleStatus = GeoUtils.GetStatusOf(vehicle.GsmDeviceLogs.Last())
                          };
    return PartialView("Dashboard", myVehicles);
    }


public static VehicleStatus GetStatusOf(GSMDeviceLog deviceLog)
    {
        VehicleStatus currentStatus = VehicleStatus.Stop;

        if (deviceLog != null)
        {
            //Considering DigitalInputLevel1 as Ignition. Not a correct way to do it as DigitalInputLevel1
            //is device dependent. Must change in future.
            if (deviceLog.DigitalInputLevel1)
                currentStatus = VehicleStatus.Idle;

            if (deviceLog.DigitalInputLevel1 && deviceLog.Speed > ZERO_SPEED)
                currentStatus = VehicleStatus.Moving;
            else if (!deviceLog.DigitalInputLevel1 && deviceLog.Speed >= ZERO_SPEED)
                currentStatus = VehicleStatus.Towed;
            if ((DateTime.Now - deviceLog.DateTimeOfLog).TotalMinutes > 2)
                currentStatus = VehicleStatus.Unreachable;
        }
        else
            currentStatus = VehicleStatus.Unreachable;

        return currentStatus;
    }

如果我评论最后一行(VehicleStats=....)，页面加载时间将低于 1 秒。但如果它发表评论，则大约需要 2 分钟。

型号:

public class Vehicle
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int VehicleId
    { get; set; }

    [Display(Name = "Vehicle name")]
    [StringLength(100)]
    public String Name
    { get; set; }

    [Required]
    public String VehicleType
    { get; set; }

    [Required]
    [StringLength(20)]
    [Display(Name = "Registration no.")]
    public String RegNo
    { get; set; }

    [Required]
    [StringLength(100)]
    public String Manufacturer
    { get; set; }

    [StringLength(20)]
    [Display(Name = "Model or Year")]
    public String Year
    { get; set; }

    [StringLength(100)]
    [Display(Name = "Service provider")]
    public String ServiceProvider
    { get; set; }

    [DataType(DataType.Date)]
    [Display(Name = "Insurance date")]
    [DisplayFormat(DataFormatString = "{0:dd/MM/yyyy}", ApplyFormatInEditMode = true)]
    public DateTime InsuranceDate
    { get; set; }

    [DataType(DataType.Date)]
    [Display(Name = "Last serviced on")]
    public DateTime LastServicedOn
    { get; set; }

    [Display(Name = "Last serviced at (km)")]
    public int LastServicedAt
    { get; set; }

    [Display(Name = "Next service at (km)")]
    public int NextServiceAt
    { get; set; }

    [DataType(DataType.Date)]
    [Display(Name = "PUC expiry date")]
    public DateTime PUCExpiryDate
    { get; set; }


    [Display(Name = "Vehicle Ownership document")]
    [DataType(DataType.ImageUrl)]
    public virtual List<OwnershipPaper> OwnershipPapers
    { get; set; }

    //[Display(Name = "Vehicle status")]
    //public VehicleStatusType VehicleStatus
    //{ get; set; }

    [Display(Name = "Target Utilization (km) per day")]
    public int TargetUtilizationPerDay
    { get; set; }

    public virtual Driver Driver
    { get; set; }
    [Display(Name = "Vehicle Group")]

    [Required]
    public virtual VehicleGroup VehicleGroup
    { get; set; }

    public string IMEI 
    { get; set; }

    [Display(Name="Fuel tank capacity")]
    [Required]
    public double FuelTankCapacityLitres
    { get; set; }

    public virtual License License { get; set; }

    public virtual ICollection<GSMDeviceLog> GsmDeviceLogs { get; set; }

    public virtual Policy Policy { get; set; }

    public virtual ApplicationUser User { get; set; }

    public bool IsDeleted { get; set; }
}



public class GSMDeviceLog
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int GSMDeviceLogId { get; set; }
    public string IMEI { get; set; }
    public string Message { get; set; }
    public string ProfileName { get; set; }
    public bool GPSStatus { get; set; }
    public int SignalStrength { get; set; }
    public DateTime DateTimeOfLog { get; set; }
    //public string TimeOfLog { get; set; }
    public double Latitude { get; set; }
    public double Longitude { get; set; }
    public float Altitude { get; set; }
    public float Speed { get; set; }
    public float Direction { get; set; }
    public int NoOfSatelite { get; set; }
    public float GPSPositionAccuracyIndication { get; set; }
    public float MilageReading { get; set; }
    public string Cell { get; set; }
    public float AnalogInputVoltage1 { get; set; }
    public float AnalogInputVoltage2 { get; set; }
    public float AnalogInputVoltage3 { get; set; }
    public float AnalogInputVoltage4 { get; set; }
    public bool DigitalInputLevel1 { get; set; }
    public bool DigitalInputLevel2 { get; set; }
    public bool DigitalInputLevel3 { get; set; }
    public bool DigitalInputLevel4 { get; set; }
    public bool DigitalOutputLevel1 { get; set; }
    public bool DigitalOutputLevel2 { get; set; }
    public bool DigitalOutputLevel3 { get; set; }
    public bool DigitalOutputLevel4 { get; set; }

    [Display(Name="Address")]
    public string Location { get; set; }

    public int InfoNumber { get; set; }

    //Reperesent harsh accelration and deaccelration
    public bool HarshDetecation { get; set; }

    //RFID Tag Number
    [StringLength(15)]
    public string RFID { get; set; }

    //public virtual Policy Policy { get; set; }

    public virtual ICollection<Violation> Violations { get; set; }

    public virtual Vehicle Vehicle { get; set; }


}

Answer 1

问题是您对 ToList() 的调用。这会评估查询并从数据库中检索每一行。

然后您将在循环中调用导航属性。这将导致对数据库中的每个项目执行新查询。这是运行时间慢的原因。

如果您只是将“ToList()”调用移到“select”语句之后，您所有的问题都将得到解决。

myVehicles = from vehicle in au.Vehicles
    where vehicle.License.ExpiryDate >= DateTime.Now && !vehicle.IsDeleted
    select new
    {
        VehicleName = vehicle.Name,
        DriverName = vehicle.Driver.Name ?? "No driver",
        DriverId= vehicle.Driver == null ? 0 : vehicle.Driver.DriverId,
        VehicleId = vehicle.VehicleId,
        GsmDeviceLogs = vehicle.GsmDeviceLogs.LastOrDefault()
    }.ToList()
    .Select({vehicle => new CurrentVehicleStatus
        VehicleName = VehicleName,
        DriverName = DriverName,
        DriverId= DriverId,
        VehicleId = VehicleId,
        VehicleStatus = GeoUtils.GetStatusOf(GsmDeviceLogs)
    });

编辑:将三元运算符更改为空合并，使它们对 EntityFramework 友好(驱动程序是否为空并不重要，因为这是一个在幕后转换为 SQL 的表达式)。

编辑:看过 GeoUtils 的代码，更新了答案。它肯定不会在表达式树中被解析。此外，更改了对 LastOrDefault() 的调用。