linq 分组结果需要很长时间

Question

我有以下方法可以进行一些分组

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的记录。有没有我没有看到的性能提升器？

Answer 1

我正在尝试将您的查询转换为对数据库更友好的版本。var vehicles = vehiclesAndQuotes&nbsp; &nbsp; .GroupBy(vehicleAndQuote =>&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; new {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; vehicleAndQuote.Vehicle.VehicleMakeName,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; vehicleAndQuote.Vehicle.VehicleModelTypeName,&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; vehicleAndQuote.Vehicle.VehicleEdition&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(a => new {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; // DB friendly&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; vehicle = a.First(),&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; plans = a.GroupBy(quote => quote.Quote.YearlyMileage)&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; })&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .AsEnumerable() // May o may not be needed / passing to LINQ to Objects&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; .Select(a => {&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var vehicle = _mapper.Map<VehicleAndQuote, CatalogVehicle>(a.vehicle);&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; var plans = a.plans.Select(group => _mapper.Map<IEnumerable<VehicleAndQuote>, LeasingPlan>(group));&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; vehicle.LeasingPlans = plans;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; return vehicle;&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; });return vehicles.ToList(); // This should be avoided, specially if you are processing a large collection.这样您就可以在数据库端进行双重分组。另外，我直到最后才将整个系列具体化。

Answer 2

检查中生成的查询SQL Profiler。您的查询正在(N+1 problem)对vehicle和 中的每条记录进行选择plans。如果您正在使用Entity Framework 2.0，您应该知道它在GroupBy本地运行并将所有数据从数据库中提取到内存中SELECT *，然后将它们分组到内存中。将您的查询分解为每个实体的多个查询。不要LINQ像First()在SelectLINQ函数中那样使用函数，因为它会导致N+1 SQL Issue.&nbsp;将您的 EF 版本升级到 2.1 或更高版本以避免Local GroupBy出现问题。