The meaning of life is to explore the world

LINQ outer join

Posted on By Jason Liu

Some tips on how to do outer joins in LINQ

1.The introduction of joins in LINQ
https://stackoverflow.com/questions/36882478/how-to-do-sql-joins-in-lambda/36883214

2.Right outer join without Lambda

int[] arrayLeft = { 2, 3, 5, 6, 8 };
int[] arrayRight = { 9, 7, 6, 5, 3 };
// In theory LINQ only supports left outer join
// Swap the table sequence to do a right outer join with LINQ
var result = from r in arrayRight where r > 5
			join l in arrayLeft on r equals l into joined
			where r < 8
			from j in joined.DefaultIfEmpty()
			where j + r < 10
			select new { Left = j, Right = r };
Console.WriteLine("After right outer joined in LINQ: ====");
foreach (var r in result)
	Console.WriteLine(r);
//{ Left = 0, Right = 7 }

3.Right outer join with Lambda

int[] arrayLeft = { 2, 3, 5, 6, 8 };
int[] arrayRight = { 9, 7, 6, 5, 3 };
// Equivalent right outer join in LINQ with Lambda
var result = arrayRight.Where( r => r > 5 )
			.GroupJoin(arrayLeft, outer => outer, inner => inner, (r, l) => new {Right = r, Left = l })
			.Where( row => row.Right < 8 )
			.SelectMany(toFlatten => toFlatten.Left.DefaultIfEmpty(), (row, flatterned) => new { Left = flatterned, Right = row.Right })
			.Where(x => x.Left + x.Right < 10);
Console.WriteLine("After right outer joined with Lambda in LINQ: ====");
foreach (var r in result)
	Console.WriteLine(r);
//{ Left = 0, Right = 7 }