Linq takes too long get results

Question

I'm attempting to make the code below run as quickly as possible. It takes about couple mins to get the results added the finaloutput.

This code is part of API endpoint that get vehicle information, foreach loops over each vehicle to count the number of plates from PlateVehicle table and from another policies table to get insurance use.

foreach(var vehicle in result)
{
    //Get total Plates Count
    var plates = await _context.PlateVehicles
                 .Where(a => a.VehicleId.Equals(vehicle.VehicleId)
                        && a.Status=="Active").ToListAsync();
    vehicle.PlatesCount = plates.Count();

    //Get Insurance Use from Policy
    var policyInfo = await _context.Policies
                   .Where(a=>a.Vehicle_Id.Equals(vehicle.VehicleId)).FirstOrDefaultAsync();

    if (policyInfo != null)
    {
        vehicle.InsuranceUse = policyInfo.Class == null ? string.Empty : policyInfo.Class;
        finalOutput.Add(vehicle);
    }

}

there are around 6000 vehicle records in the DB and it is currently taking about 2 mins to get the results.

Answer 1

There's a lot that can be done to clean that up. With Entity Framework you want to leverage projection to get the relevant data in a single query rather than a more ADO-like approach fetching data in queries.

For a minimal impact solution to what you already have:

foreach(var vehicle in result)
{
    vehicle.PlateCount = await _context.PlateVehicles
        .Where(a => a.VehicleId.Equals(vehicle.VehicleId)
            && a.Status=="Active")
        .Count();

 // If there can be more than one policy, add an OrderBy then use FirstOrDefault()
    vehicle.InsuranceUse = await _context.Policies
        .Where(a => a.Vehicle_Id.Equals(vehicle.VehicleId))
        .Select(a => a.Class)
        .SingleOrDefault() ?? string.Empty; 

    finalOutput.Add(vehicle);
}

This doesn't address the fact that you are running these queries for each individual vehicle.

To further improve this, you would need to look at where the vehicle data is being loaded and how it is structured. For instance if we have a DTO or ViewModel for the Vehicle and it was loaded from the database, the best thing would be to adjust the loading of the vehicle data to project these fields directly into the view model using navigation properties. For instance to include the vehicle plate count:

var vehicles = await _context.Vehicles
    .Where(v => /* search criteria */)
    .Select(v => new VehicleViewModel
    {
        Id = v.Id,
        // Other properties...
        PlateCount = v.PlateVehicles.Count(pv => pv.Status == "Active");
        InsuranceUse = v.Policies.Select(p => p.Class) // Let the ViewModel getter resolve #null as string.Empty
     }).ToListAsync();

This would fetch the required details all within one query to the DB for maximum efficiency, but requires that all of the navigation properties are set up properly for the related entities.