SQL to Linq query with multiple left outer joins

Question

I'm trying to convert an SQL query to Linq containing several left outer joins, but I'm encountering an odd situation.

The relevant part of my SQL is:

SELECT * FROM dbo.SessionDetails as sd
    left outer join dbo.VoipDetails as vd on vd.SessionIdTime = sd.SessionIdTime and vd.SessionIdSeq = sd.SessionIdSeq
    left outer join dbo.Gateways as fgw on vd.FromGatewayId = fgw.GatewayId

My Linq query so far is:

var query = from sd in dbo.SessionDetails
    join vd in dbo.VoipDetails on new { sd.SessionIdTime, sd.SessionIdSeq } equals new { vd.SessionIdTime, vd.SessionIdSeq } into sdvd
    from v in sdvd.DefaultIfEmpty()
         join fgw  in dbo.Gateways on vd.FromGatewayId equals fgw.GatewayId   into sdgw
         from g in sdvd.DefaultIfEmpty()
             select sd;

I'm getting an error mark on vd.FromGatewayId telling me that The name 'vd' is not in scope on the left side of 'equals'. Consider swapping the expressions on either side of 'equals'.
However, if I do swap sides with gw.GatewayId then I get the same error message for both vd and gw. Can someone suggest the correct syntax here? Please bear in mind I have several more left-joins to add after I get the basic syntax down.

Answer 1

I believe the issue is that you are trying to access a value that has no scope in the query. I believe the reason for this is that you specify the relation ship and then assign these values to a collection called sdvd at this point you can't get to vd. That said, you then do from v in sdvd.DefaultIfEmpty() by doing so you have access to the rows in sdvd which are the same values that are in what you think vd holds. You should be able to use v instead of vd. I had to mock something up to test so I couldn't test out exactly this query, but the following should run.

var query = from sd in dbo.SessionDetails
    join vd in dbo.VoipDetails on new { sd.SessionIdTime, sd.SessionIdSeq } equals new { vd.SessionIdTime, vd.SessionIdSeq } into sdvd
    from v in sdvd.DefaultIfEmpty()
        join fgw  in dbo.Gateways on v.FromGatewayId equals fgw.GatewayId   into sdgw
        from g in sdvd.DefaultIfEmpty()
            select sd;

EDIT 2014/12/08

In order to see how the linq statement translates to sql, I would suggest you install https://www.linqpad.net/. You can set up a connection and test out your queries there and see the sql in the results view.

As i don't have the data structure for the question this will be hard. That said I mocked up something:

from sd in  Employees
    join vd in TimeEntries on new { sd.EmployeeID } equals new { vd.EmployeeID } into sdvd
    from v in sdvd.DefaultIfEmpty()
        join fgw  in EmployeeGroupDetails on v.EmployeeID equals fgw.EmployeeID   into sdgw
        from g in sdgw.DefaultIfEmpty()
            select  sd

This YIELDS:

SELECT [t0].*
FROM [Employee] AS [t0]
LEFT OUTER JOIN [TimeEntry] AS [t1] ON [t0].[EmployeeID] = [t1].[EmployeeID]
LEFT OUTER JOIN [EmployeeGroupDetail] AS [t2] ON [t1].[EmployeeID] = [t2].[EmployeeID]

It does come back with the right joins.