title: Linq Left join, where, group by, count()
body: <p>I need help with doing a Left join in a linq statement. My T-sql query works as expected but I can't seem to get the wanted results from the Linq. I also realize that there are ton of questions like mine, but I can't seem to apply any of the solutions to my case.</p>
  
  <p>Products table</p>
  
  <pre><code>+---+------------+-----------+
  |   |transportID |  Type(int)|
  +---+------------+-----------+
  | 1 | 5          | 1         |
  | 2 | 5          | 3         |
  | 3 | 6          | 3         |
  +---+------------+-----------+
  </code></pre>
  
  <p>Stores</p>
  
  <pre><code>+---+------------+-------------+
  |   |Name        |Type1(string)|
  +---+------------+-------------+
  | 1 | Ho         | 1           |
  | 2 | He         | 2           |
  | 3 | Be         | 3           |
  | 4 | Ke         | 4           |
  | 5 | Fe         | 5           |
  +---+------------+-------------+
  </code></pre>
  
  <p>My wanted result is</p>
  
  <pre><code>+---+------------+-------------+
  |   |Type        |Count        |
  +---+------------+-------------+
  | 1 | 1          | 1           |
  | 2 | 2          | 0           |
  | 3 | 3          | 1           |
  | 4 | 4          | 0           |
  | 5 | 5          | 0           |
  +---+------------+-------------+
  </code></pre>
  
  <p>My tsql that works as intended</p>
  
  <pre><code>SELECT 
      Type1,
      Count(Pro.transportId) as Count
  
  FROM dbo.stores as sto
  
  left Join dbo.products as pro on (sto.Type1 = pro.Type AND pro.transportId=5)
  
  Where Type1 is not null
    group by Type1
    ORDER BY Type1 * 1 ASC
  </code></pre>
  
  <p>My Linq attempt returns this.</p>
  
  <pre><code>+---+------------+-------------+
  |   |Type        |Count        |
  +---+------------+-------------+
  | 1 | 1          | 1           |
  | 3 | 3          | 1           |
  +---+------------+-------------+
  </code></pre>
  
  <p>Linq Statement.</p>
  
  <pre><code>var res =   (from sto in _context.Stores
                               join pro in _context.Products on sto.Type1 equals System.Data.Objects.SqlClient.SqlFunctions.StringConvert((double)pro.Type).Trim()
                               where pro.transportId == transportId
                               group pro by pro.Type1 into pt1
                               select new TypeTransportation()
                               {
                                   Type = pt1.Key, // Needs to be int
                                   Count = pt1.Count()
                               }).ToList();
  </code></pre>
  
  <p>I've tried doing some defaultifempty but can't seem to make it work.</p>