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

Lloyd Sheen 1,391 Reputation points
2024-04-24T19:10:49.03+00:00

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.

C#
C#
An object-oriented and type-safe programming language that has its roots in the C family of languages and includes support for component-oriented programming.
10,356 questions
0 comments No comments
{count} votes

2 answers

Sort by: Most helpful
  1. Viorel 112.9K Reputation points
    2024-04-24T19:51:58.7966667+00:00

    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 . . .
    
    0 comments No comments

  2. Jiale Xue - MSFT 34,671 Reputation points Microsoft Vendor
    2024-04-25T03:21:57.11+00:00

    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.

    0 comments No comments