LINQ join tables with value if no match

Question

I know that there are some examples but I could not apply them on my code. I am pretty new to Linq and SQL. I have two tables I want to join.

First Table:

--------------
| Id | Count |
--------------
| 1  |   10  |     
--------------
| 2  |   4   |               
--------------

Second Table:

--------------
| Id |  Name |
--------------
| 1  |  Tom  |     
--------------
| 2  |  John |               
--------------
| 3  |  Nick |               
--------------
| 4  |  Max  |               
--------------

As you can see, the second table has more records than the first. My goal is to join them based on the Id. The problem is that after I have joined the tables it only displays the matching records, which is Id 1 and 2. Though I want to display every Id (from 1 to 4) and if there is no match in both tables, there should be a default value 0.

It should look like this:

----------------------
| Id |  Name | Count |
----------------------
| 1  |  Tom  |   10  |
----------------------
| 2  |  John |   4   |      
----------------------
| 3  |  Nick |   0   |      
----------------------
| 4  |  Max  |   0   |      
----------------------

So far I have got this code:

// first table
var listCount = entity.tblKundes.Where(x => x.Studio == 2)
                                .Select(x => new { x.Id, x.Name})
                                .GroupBy(x => x.Name).ToList(); 

// second table
var listBerater = entity.tblUsers.Where(x => x.Studio == 2)
                                 .Select(x => new { x.Id, x.Name})
                                 .ToList();

// This join should be edited so that it displays non matching records as well
var test = listCount.Join(
     listBerater,
     count => count.Key,
     berater => berater.Id,
     (count, berater) => new { listCount = count, listBerater = berater }
).ToList();

Edit:

var test2 = (from list in listCount
             join berater in listBerater on list.Berater equals berater.Id into gj
             from sublist in gj.DefaultIfEmpty()
             select new { sublist.Id, sublist.Nachname, sublist.Vorname }).ToList();

Take a look at https://msdn.microsoft.com/en-us/library/bb397895.aspx — DWright, Sep 02 '16 at 21:59
Thanks for your answer. Can you look at my edit? I had to remove the grouping in rder to join them like in the example of the msdn. Can you help me to group it by name? — Tom el Safadi, Sep 02 '16 at 22:21
You shouldn't change the essential question after you've received answers. You asked how to do outer join and it was answered. Now you ask how to do grouping. You should open a new question for that, if you can't figure it out after finding (numerous) examples on the internet. And maybe accept the answer that helped you most. — Gert Arnold, Sep 03 '16 at 19:39

score 0 · Answer 1 · answered Sep 03 '16 at 05:46

There is a typical concept in every Structured Query Languages which is called "Left join". Left-Join means that you will have all rows of data from first table even there is no equivalent in the second one. "Inner-Join" is a little different and only looks for matched rows of data.

Here you can find enough and complete information about your issue. Left Join

LINQ join tables with value if no match

1 Answers1