title: LINQ join tables with value if no match
body: <p>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.</p>
  
  <p>First Table:</p>
  
  <pre><code>--------------
  | Id | Count |
  --------------
  | 1  |   10  |     
  --------------
  | 2  |   4   |               
  --------------
  </code></pre>
  
  <p>Second Table:</p>
  
  <pre><code>--------------
  | Id |  Name |
  --------------
  | 1  |  Tom  |     
  --------------
  | 2  |  John |               
  --------------
  | 3  |  Nick |               
  --------------
  | 4  |  Max  |               
  --------------
  </code></pre>
  
  <p>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.</p>
  
  <p>It should look like this:</p>
  
  <pre><code>----------------------
  | Id |  Name | Count |
  ----------------------
  | 1  |  Tom  |   10  |
  ----------------------
  | 2  |  John |   4   |      
  ----------------------
  | 3  |  Nick |   0   |      
  ----------------------
  | 4  |  Max  |   0   |      
  ----------------------
  </code></pre>
  
  <p>So far I have got this code:</p>
  
  <pre><code>// first table
  var listCount = entity.tblKundes.Where(x =&gt; x.Studio == 2)
                                  .Select(x =&gt; new { x.Id, x.Name})
                                  .GroupBy(x =&gt; x.Name).ToList(); 
  
  // second table
  var listBerater = entity.tblUsers.Where(x =&gt; x.Studio == 2)
                                   .Select(x =&gt; 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 =&gt; count.Key,
       berater =&gt; berater.Id,
       (count, berater) =&gt; new { listCount = count, listBerater = berater }
  ).ToList();
  </code></pre>
  
  <p><strong>Edit:</strong></p>
  
  <pre><code>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();
  </code></pre>