Linq Query With Multiple Joins Not Giving Correct Results

Question

I have a Linq query which is being used to replace a database function. This is the first one with multiple joins and I can't seem to figure out why it returns 0 results.

If you can see any difference which could result in the incorrect return it would be greatly appreciated......I've been trying to solve it longer than I should have.

Linq Query

context.StorageAreaRacks
    .Join(context.StorageAreas, sar => sar.StorageAreaId, sa => sa.Id, (sar, sa) => new { sar, sa })
    .Join(context.StorageAreaTypes, xsar => xsar.sar.StorageAreaId, sat => sat.Id, (xsar, sat) => new { xsar, sat })
    .Join(context.Racks, xxsar => xxsar.xsar.sar.RackId, r => r.Id, (xxsar, r) => new { xxsar, r })
    .Where(x => x.xxsar.sat.IsManual == false)
    .Where(x => x.r.IsEnabled == true)
    .Where(x => x.r.IsVirtual == false)
    .Select(x => new { x.xxsar.sat.Id, x.xxsar.sat.Name })
    .Distinct()
    .ToList();

This is the query which is generated by the LINQ query

SELECT 
[Distinct1].[C1] AS [C1], 
[Distinct1].[Id] AS [Id], 
[Distinct1].[Name] AS [Name]
FROM ( SELECT DISTINCT 
    [Extent2].[Id] AS [Id], 
    [Extent2].[Name] AS [Name], 
    1 AS [C1]
    FROM   [dbo].[StorageAreaRacks] AS [Extent1]
    INNER JOIN [dbo].[StorageAreaTypes] AS [Extent2] ON [Extent1].[StorageAreaId] = [Extent2].[Id]
    INNER JOIN [dbo].[Racks] AS [Extent3] ON [Extent1].[RackId] = [Extent3].[Id]
    WHERE (0 = [Extent2].[IsManual]) AND (1 = [Extent3].[IsEnabled]) AND (0 = [Extent3].[IsVirtual])
)  AS [Distinct1]

Sql Query which produces required results

SELECT DISTINCT sat.Name, sat.Id
FROM StorageAreaRacks sar
    JOIN StorageAreas sa on sa.id = sar.StorageAreaId
    JOIN StorageAreaTypes sat on sat.id = sa.StorageAreaTypeId
    JOIN Racks r on r.id = sar.RackId
WHERE sat.IsManual = 0
    AND r.IsEnabled = 1
    AND r.IsVirtual = 0

What ORM is used to run this query (EF6, EF Core, other)? And what's the generated SQL? — Ivan Stoev, Aug 02 '18 at 19:25
@IvanStoev The ORM is EF6 and I had to look up how to see the generated SQL query. This is something new to me so going through could help. Since it's too long I'll add the generated query to the question. — Myles, Aug 02 '18 at 19:57
Well, in EF6 you could simply replace `.ToList()` with `.ToString()` and you'll get the SQL query :) — Ivan Stoev, Aug 02 '18 at 19:59
@IvanStoev EF and LINQ are quiet new to me so I still have a lot to learn :P. — Myles, Aug 02 '18 at 20:08
Perhaps my [SQL to LINQ Recipe](https://stackoverflow.com/questions/49245160/sql-to-linq-with-multiple-join-count-and-left-join/49245786#49245786) would help you? — NetMage, Aug 02 '18 at 21:00
Please rewrite this LINQ query using navigation properties. Joins are verbose (esp. in method syntax) and highly error-prone. For example, `StorageAreaTypes` shouldn't be joined by `StorageAreaId`. When using navigation properties it's impossible to mismatch join fields. — Gert Arnold, Aug 02 '18 at 21:06

score 6 · Accepted Answer · answered Aug 02 '18 at 22:20

Using joins with LINQ method syntax is hard to read and error prone.

Using joins with LINQ query syntax is better, but still error prone (you can join by the wrong key as you did) and does not give you information about join cardinality.

The best for LINQ to Entities queries is to use navigation properties (as Gert Arnold suggested in the comments and not only - see Don’t use Linq’s Join. Navigate!) because they have none of the aforementioned drawbacks.

The whole query should be something like this:

var query = context.StorageAreaRacks
    .Where(sar => !sar.StorageArea.StorageAreaType.IsManual
        && sar.Rack.IsEnabled && !sar.Rack.IsVirtual)
    .Select(sar => new
    {
        sar.StorageArea.StorageAreaType.Id,
        sar.StorageArea.StorageAreaType.Name,
    })
    .Distinct();

or

var query = (
    from sar in context.StorageAreaRacks
    let sat = sar.StorageArea.StorageAreaType
    let r = sar.Rack
    where !sat.IsManual && r.IsEnabled && !r.IsVirtual
    select new { sat.Id, sat.Name })
    .Distinct();

Simple, readable and almost no place for mistakes. Navigation properties are one of the most beautiful features of EF, don't miss them.

NetMage · Answer 2 · 2019-04-24T19:09:27.617

Your LINQ doesn't translate the SQL properly; it Joins the StorageAreaTypes on the StorageAreaRack.StorageAreaId instead of on the StorageAreas.StorageAreaTypeId, which is why EF drops the StorageAreas Join - it has no effect on the outcome.

I think it is clearer if you elevate the members of each join to flatten the anonymous objects and name them based on their members (that are the join tables). Also, no reason to separate the Where clauses, LINQ can use && as well as SQL using AND. Also, if you have boolean values, don't compare them to true or false. Also there is no reason to pass range variables through that aren't used later.

Putting it all 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 think when multiple joins are involved and when translating SQL, LINQ comprehension syntax can be easier to understand:

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();

score 0 · Answer 3 · answered Aug 02 '18 at 16:10

0

You are missing a Where for your rack ID != null in your LINQ statement, and a Distinct().

answered Aug 02 '18 at 16:10

Kit

20,354
4
60
103

The where rackId != null is apparently pointless as it isn't a null-able type. I don't think the lack of a distinct would make it go from 10 results to 0. I'm pretty sure that I tried it but I will again anyways. – Myles Aug 02 '18 at 17:09
Hm, yeah I see your point on Distinct(). Maybe it's a type-related issue with bool/int. It's hard to know without knowing your full table and object structure. – Kit Aug 02 '18 at 17:18

Linq Query With Multiple Joins Not Giving Correct Results

3 Answers3