How to do LEFT JOIN in LINQ to Entities?

Question

I have spent the last 2 days trying to find out how to do a real LEFT JOIN in LINQ and I haven't been successful. I have a user table that has a "Primary2Address" column in it that could and is often NULL. so I have to do a LEFT JOIN here. In addition, in the Address table, I have more relationship that could be NULL, so I have to do multiple LEFT JOINS.
Every LINQ attempt i do outputs some SERIOUSLY CRAZY sql statements with UNIONS, nested SELECT statements and more wacky things.
All i need is:

SELECT u.UserName FROM Users u 
LEFT JOIN Addresses a ON a.AddressiD = u.Primary2Address
LEFT JOIN States s ON s.StateID = a.Address2State
LEFT JOIN Countries c ON c.CountryID = a.CountryID

Please help! So far my work-around was to create a stored procedure that uses my sql statement above, but I would really like to try to do this with LINQ (L2E). Thanks guys!

Answer 1

DefaultIfEmpty is used for left joins for EntityFramework 4+

var query = from u in context.Users
            from a in context.Addresses
                             .Where(x => u.Primary2Address == x.AddressiD)
                             .DefaultIfEmpty()
            from s in context.States
                             .Where(x => a.Address2State == x.StateID)
                             .DefaultIfEmpty()
            from c in context.Countries
                             .Where(x => a.CountryID == x.CountryID)
                             .DefaultIfEmpty()
            select u.UserName;