我有以下方法可以進行一些分組private List<CatalogVehicle> GroupResult(IEnumerable<VehicleAndQuote> vehiclesAndQuotes) { var vehicles = vehiclesAndQuotes .GroupBy(vehicleAndQuote => new { vehicleAndQuote.Vehicle.VehicleMakeName, vehicleAndQuote.Vehicle.VehicleModelTypeName, vehicleAndQuote.Vehicle.VehicleEdition }) .Select(a => new { vehicle = _mapper.Map<VehicleAndQuote, CatalogVehicle>(a.First()), plans = GetLeasingpPlansGroupedByYearlyMileages(a.ToList()) //<== this one is taking ages }) .Select(a => { a.vehicle.LeasingPlans = a.plans; return a.vehicle; }); return vehicles.ToList(); }以及在其中調用的方法； private List<LeasingPlan> GetLeasingpPlansGroupedByYearlyMileages(IEnumerable<VehicleAndQuote> vehicleAndQuotes) { return vehicleAndQuotes.GroupBy(quote => quote.Quote.YearlyMileage) .Select(group => _mapper.Map<List<VehicleAndQuote>, LeasingPlan>(group.ToList())) .ToList(); }最后一種方法需要很長時間。在IEnumerable<VehicleAndQuote> vehiclesAndQuotes約30.000的記錄。有沒有我沒有看到的性能提升器？
2 回答
慕村225694
TA貢獻1880條經驗 獲得超4個贊
我正在嘗試將您的查詢轉換為對數據庫更友好的版本。
var vehicles = vehiclesAndQuotes
.GroupBy(vehicleAndQuote =>
new {
vehicleAndQuote.Vehicle.VehicleMakeName,
vehicleAndQuote.Vehicle.VehicleModelTypeName,
vehicleAndQuote.Vehicle.VehicleEdition
})
.Select(a => new {
// DB friendly
vehicle = a.First(),
plans = a.GroupBy(quote => quote.Quote.YearlyMileage)
})
.AsEnumerable() // May o may not be needed / passing to LINQ to Objects
.Select(a => {
var vehicle = _mapper.Map<VehicleAndQuote, CatalogVehicle>(a.vehicle);
var plans = a.plans.Select(group => _mapper.Map<IEnumerable<VehicleAndQuote>, LeasingPlan>(group));
vehicle.LeasingPlans = plans;
return vehicle;
});
return vehicles.ToList(); // This should be avoided, specially if you are processing a large collection.
這樣您就可以在數據庫端進行雙重分組。另外，我直到最后才將整個系列具體化。
四季花海
TA貢獻1811條經驗 獲得超5個贊
檢查中生成的查詢SQL Profiler。您的查詢正在(N+1 problem)對vehicle和 中的每條記錄進行選擇plans。如果您正在使用Entity Framework 2.0，您應該知道它在GroupBy本地運行并將所有數據從數據庫中提取到內存中SELECT *，然后將它們分組到內存中。將您的查詢分解為每個實體的多個查詢。不要LINQ像First()在SelectLINQ函數中那樣使用函數，因為它會導致N+1 SQL Issue. 將您的 EF 版本升級到 2.1 或更高版本以避免Local GroupBy出現問題。
- 2 回答
- 0 關注
- 247 瀏覽
添加回答
舉報
0/150
提交
取消