Return is not reached in debug-mode after Join to entities in DbSet-CodePudding

I don't have strong knowledge in TPL. Maybe I misunderstood some points. I have a linq-query to entities and JOIN-operator I use to connect diffrent DBSet with diffrent entities. The result list contains INNER JOIN selection. Here is :

public async Task<List<PlantsViewModel>> GetPlants()
{
    var plants = _context.Plants
        .Join(await _context.Saptransfer.ToListAsync(), plant => plant.PlantNumber, sap => sap.Plant,
        (plant, sap) => new
        {
            plant.PlantNumber,
            plant.Plant,
            plant.PlantManager,
            plant.District,
            plant.Area
        }).GroupBy(x => new
        {
            x.PlantNumber,
            x.Plant,
            x.PlantManager,
            x.District,
            x.Area
        }).ToList();

    return plants.
        Select(p => new PlantsViewModel
        {
            PlantID = p.Key.PlantNumber,
            PlantName = p.Key.Plant,
            PlantSupervisor = p.Key.PlantManager,
            DistrictName = p.Key.District,
            RegionName = p.Key.Area
        }).OrderBy(p => p.PlantName).ToList();
}

First of all, it consumes much time to execute - the non-clustered indexes're abscent. But main question - why break-point near return not switched during run-time? Maybe someone will advise me how to make linq-query in a right way. Thank you.

CodePudding user response：

You have did a big mistake when put everywhere ToList/Async. LINQ query is effective when you materialize objects at the end. I have no idea why you have put join without using it's values and then GroupBy for removing duplicates, but this query will be more effective that in original question:

public Task<List<PlantsViewModel>> GetPlants()
{
    var plants = 
        from plant in _context.Plants
        join sap in _context.Saptransfer on sap.Plant equals plant.PlantNumber
        group plant by new {         
            plant.PlantNumber,
            plant.Plant,
            plant.PlantManager,
            plant.District,
            plant.Area
        } into g
        orderby g.Key.Plant
        select new PlantsViewModel
        {
            PlantID = g.Key.PlantNumber,
            PlantName = g.Key.Plant,
            PlantSupervisor = g.Key.PlantManager,
            DistrictName = g.Key.District,
            RegionName = g.Key.Area
        };

    return plants.ToListAsync();
}

CodePudding user response：

There are several issues with your query. Firstly the grouping looks to be completely unnecessary, at worst case with the join you might need to use a Distinct to avoid duplicates, but this can possibly also be avoided. GroupBy would normally be used where you want to aggregate data, such as getting a count, a minimum or maximum. or simply group related records by a detail, which your query is not doing. The next issue is that you have declared the method as async yet the bulk of the query is synchronous (ToList, not ToListAsync) plus you are fetching the entire grouped aggregate set of the data before projecting just the values you care about for the view model. (Doing a ToList before a Select)

Assuming the Plant and SapTransfer tables don't share a FK relationship, joining this table is effectively only filtering Plants that have a matching record in the SapTransfer table. If that is the intended behavior then you can try the following:

public async Task<List<PlantsViewModel>> GetPlants()
{
    var plants = _context.Plants
        .Join(_context.Saptransfer, plant => plant.PlantNumber, 
            sap => sap.Plant,
            (plant, sap) => new
            {
                plant.PlantNumber,
                plant.Plant,
                plant.PlantManager,
                plant.District,
                plant.Area
            }).OrderBy(p => p.Plant)
        .Select(p => new PlantsViewModel
        {
            PlantID = p.PlantNumber,
            PlantName = p.Plant,
            PlantSupervisor = p.PlantManager,
            DistrictName = p.District,
            RegionName = p.Area
        }).Distinct();

    return await plants.ToListAsync();
}