title: Linq group by and count
body: <p>I'm trying to understand how groupy by and count works with linq but i can't get how to do what i want.</p>
  
  <p>I have this table:</p>
  
  <pre><code>ASSET:
  Id, Code, Name, ParentId
  </code></pre>
  
  <p>the ParentId is null if its the root, and contains the parent id if the asset is linked to another asset</p>
  
  <p>I'd like to have for each root parents the Id and the number of children</p>
  
  <p>this is the query i used:</p>
  
  <pre><code>select father.Id, father.Code, COUNT(children.Id) As NumberOfChildren 
  from Asset father 
  left join Asset children on(father.Id = children.ParentId) 
  where father.ParentId IS NULL 
  group by father.Id, father.Code
  </code></pre>
  
  <p>this is the linq query i do</p>
  
  <pre><code>var query = from father in this.assetService.GetAll() 
                          join children in this.assetService.GetAll() 
                          on father.Id equals children.ParentId into Children 
                          from children in Children.DefaultIfEmpty() 
                          where father.ParentId.Value == null 
                          group father by new { id = father.Id, code = father.Code } into gf 
                          select new { id = gf.Key.id, count = gf.Count() };
  </code></pre>
  
  <p>but entity generates that query:</p>
  
  <pre><code>SELECT 
      1 AS [C1], 
      [GroupBy1].[K1] AS [Id], 
      [GroupBy1].[A1] AS [C2] 
      FROM ( SELECT 
          [Extent1].[Id] AS [K1], 
          [Extent1].[Code] AS [K2], 
          COUNT(1) AS [A1] 
          FROM  [dbo].[Asset] AS [Extent1] 
          LEFT OUTER JOIN [dbo].[Asset] AS [Extent2] ON [Extent1].[Id] = [Extent2].[ParentId] 
          WHERE [Extent1].[ParentId] IS NULL 
          GROUP BY [Extent1].[Id], [Extent1].[Code] 
      )  AS [GroupBy1]
  </code></pre>
  
  <p>the issue comes from the <code>COUNT(1)</code>, how can i tell thats its supposed to be <code>COUNT(children.Id)</code></p>