Full Outer Join in LINQ

While I haven’t found any direct support for a right outer join or full outer join in LINQ, I did work out a solution to mimic a fuller outer join. I started by building a left outer join like I did in my post, Left Outer Join in LINQ, and then union those results with another left outer join LINQ query which swaps the sides of the join from the first query. So in effect what you are doing, using the C# classes from my previous post, is performing a left outer join from Person to PersonAddress and union the results with a second query performing a left outer join from PersonAddress to Person. The key is to make sure to use the same columns in each select like would be required performing a union in SQL. It isn’t pretty, but it seems to work.

LINQ Query

 var query = (from p in dc.GetTable<Person>()
 join pa in dc.GetTable<PersonAddress>() on p.Id equals pa.PersonId into tempAddresses
 from addresses in tempAddresses.DefaultIfEmpty()
 select new { p.FirstName, p.LastName, addresses.State }).Union(
 from pa2 in dc.GetTable<PersonAddress>()
 join p2 in dc.GetTable<Person>() on pa2.PersonId equals p2.Id into tempPersons
 from persons in tempPersons.DefaultIfEmpty()
 select new { persons.FirstName, persons.LastName, pa2.State });

SQL Translation

SELECT [t4].[FirstName], [t4].[LastName], [t4].[value] AS [State]
FROM (
 SELECT [t0].[FirstName], [t0].[LastName], [t1].[State] AS [value]
 FROM [Person] AS [t0]
 LEFT OUTER JOIN [PersonAddress] AS [t1] ON [t0].[Id] = [t1].[PersonId]
 UNION
 SELECT [t3].[FirstName] AS [value], [t3].[LastName] AS [value2], [t2].[State]
 FROM [PersonAddress] AS [t2]
 LEFT OUTER JOIN [Person] AS [t3] ON [t2].[PersonId] = [t3].[Id]
 ) AS [t4]
-- Context: SqlProvider(Sql2005) Model: AttributedMetaModel Build: 3.5.21022.8