Linq how to joins with Left Outer and more than 2 tables

Question

I asked the question (https://learn.microsoft.com/en-us/answers/questions/1659579/linq-join-of-3-tables) and got an answer which works but is not entirely what I need. With each join the result set gets smaller as not all instance have an entry in each of the tables.

What I need is a Left Outer join but with more than two tables. I can do 2 tables with the following code -

    List<CommonPoints> lista = poso.assists.ToList();

    List<CommonPoints> listp = poso.points.ToList();

    List<CommonPoints> listg=poso.goals.ToList();

    List<CommonPoints> listpm=poso.penaltyMins.ToList();

    List<CommonPoints> listppg = poso.goalsPp.ToList();

    List<CommonPoints> listplmn = poso.plusMinus.ToList();

    //join plmn in listplmn on p.id equals plmn.id

    var resultx = from p in listp

                  join g in listg.DefaultIfEmpty() on p.id equals g.id into outJoin

                  from subgroup in outJoin.DefaultIfEmpty()

                    select new Combineded(p.id, p.value, 0, subgroup?.value ?? 0, 0, 0, p.firstName.TheDefault, p.lastName.TheDefault);

When I execute that the listp table (list) contains 154 items and the listg table listg table has 82 items. The result table (resultx) ends up with 154 items and the select new has the data needed to create the new item. As soon as I try with 3 tables I am lost.

Answer 1

Try something like this:

var result = from p in listp
                join g0 in listg on p.id equals g0.id into p_g
                join c0 in listC on p.id equals c0.id into p_c
                from g in p_g.DefaultIfEmpty( )
                from c in p_c.DefaultIfEmpty( )
                select new . . .

Answer 2

Hi @Lloyd Sheen , Welcome to Microsoft Q&A,

To perform a left outer join with more than two tables in LINQ, you can use multiple join clauses combined with into and DefaultIfEmpty() method. Here's how you can modify your code to join three tables:

var resultx = from p in listp
              join g in listg on p.id equals g.id into pgJoin
              from pg in pgJoin.DefaultIfEmpty()
              join pm in listpm on p.id equals pm.id into pgpmJoin
              from pgpm in pgpmJoin.DefaultIfEmpty()
              select new Combineded(p.id, p.value, 0, pg?.value ?? 0, pgpm?.value ?? 0, 0, p.firstName.TheDefault, p.lastName.TheDefault);

This query first joins listp and listg tables, then joins the result with listpm table.

You can keep extending this pattern to include more tables:

var resultx = from p in listp
              join g in listg on p.id equals g.id into pgJoin
              from pg in pgJoin.DefaultIfEmpty()
              join pm in listpm on p.id equals pm.id into pgpmJoin
              from pgpm in pgpmJoin.DefaultIfEmpty()
              join ppg in listppg on p.id equals ppg.id into pgpmppgJoin
              from pgpmppg in pgpmppgJoin.DefaultIfEmpty()
              join plmn in listplmn on p.id equals plmn.id into pgpmppgplmnJoin
              from pgpmppgplmn in pgpmppgplmnJoin.DefaultIfEmpty()
              select new Combineded(p.id, p.value, 0, pg?.value ?? 0, pgpm?.value ?? 0, pgpmppg?.value ?? 0, pgpmppgplmn?.value ?? 0, p.firstName.TheDefault, p.lastName.TheDefault);

This query adds a join with the listppg table, and then another join with the listplmn table.

Best Regards,

Jiale

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment". 

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.