left outer join in lambda/method syntax in Linq

Question

Possible Duplicate:
How do you perform a left outer join using linq extension methods

I can't find a left outer join example of Linq lambda (with extension methods), at least, not a clear one.

Let's say I have the following table:

Parent
{
    PID     // PK
}

Child
{
    CID     // PK
    PID     // FK
    Text
}

I want to join Parent with Child, and for every child missing, I want the default value for Text to be "[[Empty]]". How can I do this with linq lambda syntax?

I currently have the following:

var source = lParent.GroupJoin(
    lChild,
    p => p.PID,
    c => c.PID,
    (p, g) =>
        new // ParentChildJoined
        {
            PID = p.PID;
            // How do I add child values here?
        });

What have you already tried? And what do you mean by "linq lambda"? — Sergei Rogovtcev, Aug 22 '12 at 14:56
@mellamokm oh very helpful, all those examples are in lambda syntax! oh wait, no they're not. -_- — NomenNescio, Aug 22 '12 at 15:03
@SergRogovtsev I mean how to do it with extension methods. :P — NomenNescio, Aug 22 '12 at 15:08

score 78 · Accepted Answer · answered Aug 22 '12 at 15:44

You're close. The following will select PID, CID and Text for each child, and PID, CID = -1 and Text = "[[Empty]]" for each parent with no children:

var source = lParent.GroupJoin(
    lChild,
    p => p.PID,
    c => c.PID,
    (p, g) => g
        .Select(c => new { PID = p.PID, CID = c.CID, Text = c.Text })
        .DefaultIfEmpty(new { PID = p.PID, CID = -1, Text = "[[Empty]]" }))
    .SelectMany(g => g);

score 7 · Answer 2 · edited Dec 23 '19 at 10:58

from p in Parent
join c in Child on p.PID equals c.PID into g
from c in g.DefaultIfEmpty()
select new 
{
   p.PID,
   CID = c != null ? (int?)c.CID : null, // Could be null
   Text = c != null ? c.Text : "[[Empty]]"
}

With lambda:

class ChildResult
{
   public int PID { get; set; }
   public int? CID { get; set; }
   public string Text { get; set; }
}

lParent.SelectMany(p => p.Childs.Any() ?
  p.Childs.Select(c => new ChildResult() { PID = c.PID, CID = c.CID, Text = c.Text }) :
  new [] { new ChildResult() { PID = p.PID, CID = null, Text = "[[Empty]]" } } );

left outer join in lambda/method syntax in Linq

2 Answers2

Linked