title: Linq Left Outer Join with Count
body: <p>I want to create this SQL query:</p>
  
  <pre><code>SELECT 
      a.[Seat], 
      b.[PlayerId], 
      b.[UserName], 
      b.[NickName],
      COUNT(c.PlayerId) AS Trophy
      FROM   [dbo].[tbl_PlayerTableSeat] AS a
      INNER JOIN [dbo].[tbl_Player] AS b ON a.[PlayerId] = b.[PlayerId]               
      INNER JOIN [dbo].[tbl_GameVirtualTable] AS d ON d.GameVirtualTableId = a.GameVirtualTableId
      LEFT OUTER JOIN [dbo].[tbl_PlayerTableWinning] AS c ON a.[PlayerId] = c.[PlayerId] AND c.GameTableId = d.GameTableId                
      WHERE a.GameVirtualTableId = 36
      GROUP BY a.[Seat], b.[PlayerId], b.[UserName], b.[NickName]
  </code></pre>
  
  <p>I have this Linq</p>
  
  <pre><code>var virtualTableSeatList = (from s in db.PlayerTableSeat
                          join p in db.Player on s.PlayerId equals p.PlayerId
                          join v in db.GameVirtualTable on s.GameVirtualTableId equals v.GameVirtualTableId
                          join w in db.PlayerTableWinning on new { X1 = s.PlayerId, X2 = v.GameTableId } equals new { X1 = w.PlayerId, X2 = w.GameTableId } into gj
  
                          from g in gj.DefaultIfEmpty()
                          where s.GameVirtualTableId == virtualGameTableId
                          group new { p, s } by new { p.PlayerId, s.Seat, p.NickName, p.UserName } into grp
  
                          select new VirtualTableSeatDto
                          {
                              PlayerId = grp.Key.PlayerId,
                              Seat = grp.Key.Seat,
                              NickName = grp.Key.NickName,
                              UserName = grp.Key.UserName,                                            
                              Trophy = grp.Count()
                          }
                 ).ToList();
  </code></pre>
  
  <p>From SQL Profiler, the Linq generates this SQL query:</p>
  
  <pre><code>exec sp_executesql N'SELECT 
  [GroupBy1].[K2] AS [PlayerId], 
   CAST( [GroupBy1].[K1] AS int) AS [C1], 
  [GroupBy1].[K4] AS [NickName], 
  [GroupBy1].[K3] AS [UserName], 
  [GroupBy1].[A1] AS [C2]
  FROM ( SELECT 
      [Extent1].[Seat] AS [K1], 
      [Extent2].[PlayerId] AS [K2], 
      [Extent2].[UserName] AS [K3], 
      [Extent2].[NickName] AS [K4], 
      COUNT(1) AS [A1]
      FROM    [dbo].[tbl_PlayerTableSeat] AS [Extent1]
      INNER JOIN [dbo].[tbl_Player] AS [Extent2] ON [Extent1].[PlayerId] = [Extent2].[PlayerId]
      INNER JOIN [dbo].[tbl_GameVirtualTable] AS [Extent3] ON [Extent1].[GameVirtualTableId] = [Extent3].[GameVirtualTableId]
      LEFT OUTER JOIN [dbo].[tbl_PlayerTableWinning] AS [Extent4] ON ([Extent1].[PlayerId] = [Extent4].[PlayerId]) AND ([Extent3].[GameTableId] = [Extent4].[GameTableId])
      WHERE [Extent1].[GameVirtualTableId] = @p__linq__0
      GROUP BY [Extent1].[Seat], [Extent2].[PlayerId], [Extent2].[UserName], [Extent2].[NickName]
  )  AS [GroupBy1]',N'@p__linq__0 int',@p__linq__0=36
  </code></pre>
  
  <p>I want to change <code>COUNT(1) AS [A1]</code> to <code>COUNT([Extent4].[PlayerId]) AS [A1]</code></p>
  
  <p>so it can return correct data. 
  I have no idea how to change the LinQ</p>
  
  <p><code>Trophy = grp.Count()</code></p>
  
  <p>so that it can count <code>PlayerId</code> of <code>PlayerTableWinning</code> instead of COUNT(1)</p>
  
  <hr>
  
  <p>Updated: @Ivan Stoev</p>
  
  <p>By adding the g into the group.</p>
  
  <pre><code>group new { p, s, g }
  </code></pre>
  
  <p>And sum the group</p>
  
  <pre><code>Trophy = grp.Sum(item =&gt; item.w != null ? 1 : 0)
  </code></pre>
  
  <p>It return the correct answer. However, it is using SUM instead of count. The SQL query generated is as below:</p>
  
  <pre><code>exec sp_executesql N'SELECT 
  [GroupBy1].[K2] AS [PlayerId], 
   CAST( [GroupBy1].[K1] AS int) AS [C1], 
  [GroupBy1].[K4] AS [NickName], 
  [GroupBy1].[K3] AS [UserName], 
  [GroupBy1].[A1] AS [C2]
  FROM ( SELECT 
      [Filter1].[K1] AS [K1], 
      [Filter1].[K2] AS [K2], 
      [Filter1].[K3] AS [K3], 
      [Filter1].[K4] AS [K4], 
      SUM([Filter1].[A1]) AS [A1]
      FROM ( SELECT 
          [Extent1].[Seat] AS [K1], 
          [Extent2].[PlayerId] AS [K2], 
          [Extent2].[UserName] AS [K3], 
          [Extent2].[NickName] AS [K4], 
          CASE WHEN ( NOT (([Extent4].[GameTableId] IS NULL) AND ([Extent4].[PlayerId] IS NULL) AND ([Extent4].[GameRoundId] IS NULL))) THEN 1 ELSE 0 END AS [A1]
          FROM    [dbo].[tbl_PlayerTableSeat] AS [Extent1]
          INNER JOIN [dbo].[tbl_Player] AS [Extent2] ON [Extent1].[PlayerId] = [Extent2].[PlayerId]
          INNER JOIN [dbo].[tbl_GameVirtualTable] AS [Extent3] ON [Extent1].[GameVirtualTableId] = [Extent3].[GameVirtualTableId]
          LEFT OUTER JOIN [dbo].[tbl_PlayerTableWinning] AS [Extent4] ON ([Extent1].[PlayerId] = [Extent4].[PlayerId]) AND ([Extent3].[GameTableId] = [Extent4].[GameTableId])
          WHERE [Extent1].[GameVirtualTableId] = @p__linq__0
      )  AS [Filter1]
      GROUP BY [K1], [K2], [K3], [K4]
  )  AS [GroupBy1]',N'@p__linq__0 int',@p__linq__0=36
  </code></pre>