LINQ SQL – FULL OUTER JOIN

Possibly save 1 hour of your time: There is no equivalent of full outer join in LINQ that is same as SQL.

The solution is to do a left outer join and then a right outer join using DefaultIfEmpty and then use UNION to join these two results.

var leftouterjoin = (from items1 in table1
                    join items2 in table2
                    on new { items1.column1, items1.column2 } equals new { items2.column1, items2.column2 } into tt
                    from ttnew in tt.DefaultIfEmpty()
                    select new { column1= items1.column1, column2 = items1.column2, Value = items1.Value });

var rightouterjoin = (from items2 in table2
                    join items1 in table1
                    on new { items2.column1, items2.column2 } equals new { items1.column1, items1.column2 } into tt
                    from ttnew in tt.DefaultIfEmpty()
                    select new { column1= items2.column1, column2 = items2.column2, Value = items2.Value });

                var fullouterjoin = leftouterjoin.Union(rightouterjoin).ToList();

                var returnList = fullouterjoin.Select(x => new YourClass
                {
                    column1= x.column1,
                    column2= x.column2,
                    Value = x.Value,
                }).ToList();

If you have null values, your UNION may return duplicates. The key is to add Distinct() after your UNION().

There is a difference between SQL UNION (which performs an implicit DISTINCT) and LINQ Union (which required an explicit Distinct()).

Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google+ photo

You are commenting using your Google+ account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

w

Connecting to %s