Left outer join in LINQ

join.cs

void Main()
{
    Dictionary<int, string> mere = new Dictionary<int, string>();
    mere.Add(1, "rosii");
    mere.Add(2, "verzi");
    mere.Add(3, "albastre");
    mere.Add(4, "galbene");

    Dictionary<int, string> pere = new Dictionary<int, string>();
    pere.Add(3, "albastre");
    pere.Add(4, "galbene");

    //use only in memory
    var joined = mere.GroupJoin(pere, x => x.Key, x => x.Key, (x, y) => new { Mere = x, Pere= y })
    	.Select(x => new { Mar=x.Mere, Par = x.Pere.DefaultIfEmpty().FirstOrDefault()});
    //good sql
    var joined2 = mere.GroupJoin(pere, x => x.Key, x => x.Key, (x, y) => new { Mere = x, Pere = y })
     	.SelectMany(x => x.Pere.DefaultIfEmpty(), (x, y) => new { Mar = x.Mere, Par = y });
    //good sql
    var joined3 =
        from c in mere
        join p in pere on c.Key equals p.Key into ps
        from p in ps.DefaultIfEmpty()
        select new { x = c, y=p };
	
    joined.Dump();
    joined2.Dump();
    joined3.Dump();
}