Linq Query With Multiple Joins Not Giving Correct Results

c# entity-framework entity-framework-6 linq

Accepted Answer

ZZZ_tmp
4
8/2/2018 10:20:39 PM

Popular Answer

Your LINQ fails to correctly convert the SQL;Join s theStorageAreaTypes on theStorageAreaRack.StorageAreaId rather than theStorageAreas.StorageAreaTypeId because of which EF decreases theStorageAreasJoin - It has no bearing on the result.

Elevating the members of each join would, in my opinion, make things more obvious. This will flatten the anonymous objects and give them names depending on their members (that are the join tables). There is no need to divide theWhere LINQ may use clauses.&& furthermore utilising SQLAND Also, avoid comparing boolean values to other values if you have anytrue or false . Furthermore, there is no justification for passing range variables that won't be utilised right away.

Putting everything together

var ans = context.StorageAreaRacks
                 .Join(context.StorageAreas, sar => sar.StorageAreaId, sa => sa.Id, (sar, sa) => new { sar, sa })
                 .Join(context.StorageAreaTypes, sarsa => sarsa.sa.StorageAreaTypeId, sat => sat.Id, (sarsa, sat) => new { sarsa.sar, sat })
                 .Join(context.Racks, sarsat => sarsat.sar.RackId, r => r.Id, (sarsat, r) => new { sarsat.sat, r })
                 .Where(satr => !satr.sat.IsManual && satr.r.IsEnabled && !satr.r.IsVirtual)
                 .Select(satr => new { satr.sat.Id, satr.sat.Name })
                 .Distinct()
                 .ToList();

However, I believe that LINQ comprehension syntax may be simpler to comprehend when several joins are involved and when converting SQL:

var ans = (from sar in context.StorageAreaRacks
           join sa in context.StorageAreas on sar.StorageAreaId equals sa.Id
           join sat in context.StorageAreaTypes on sa.StorageAreaTypeId equals sat.Id
           join r in context.Racks on sar.RackId equals r.Id
           where !sat.IsManual && r.IsEnabled && !r.IsVirtual
           select new {
               sat.Name,
               sat.Id
           }).Distinct().ToList();


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow