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

Answer 1

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.