title: C# Linq Join 2 tables on multiple columns and GROUP BY for count
body: <p>I need to JOIN two entities on five columns and I need to GROUP BY every column in the SELECT plus get a COUNT on each GROUP BY. While this is cake to me in SQL, I am hopelessly confused with every example I can find for LINQ.</p>
  
  <p>I've found the following two questions (<a href="https://stackoverflow.com/q/5231845/1735836">C# Linq Group By on multiple columns [duplicate]</a> and <a href="https://stackoverflow.com/q/847066/1735836">Group By Multiple Columns</a>) that are similar to mine but I am still confused about how to do this. Here is my SQL statement:</p>
  
  <pre><code>SELECT  o.org_hq_name,
          o.org_command_name,
          o.org_region_name,
          o.org_installation_name,
          o.org_site_name,
          o.org_subsite_name,
          o.org_hq_id,
          o.org_command_id,
          o.org_region_id,
          o.org_installation_id,
          o.org_site_id,
          count(org_site_id) AS count
  
  FROM    organization o, asset a
  
  WHERE      o.org_hq_id = hq_org_id
  AND        o.org_command_id = a.command_org_id
  AND        o.org_region_id = a.region_org_id
  AND        o.org_installation_id = a.installation_org_id
  AND        o.org_site_id = a.site_org_id
  
  GROUP BY o.org_hq_name,
          o.org_command_name,
          o.org_region_name,
          o.org_installation_name,
          o.org_site_name,
          o.org_subsite_name,
          o.org_hq_id,
          o.org_command_id,
          o.org_region_id,
          o.org_installation_id,
          o.org_site_id
  </code></pre>
  
  <p>I have a join below:</p>
  
  <pre><code>var join1 =  from m in context.asset
               join o in context.organization
               on new {hqID = a.hq_org_id, commandID = a.command_org_id, regionID = a.region_org_id, installationID = a.installation_org_id, siteID = a.site_org_id}
               equals new {hqID = o.hq_id, commandID = o.command_id, regionID = o.region_id, installationID = o.installation_id, siteID = o.site_id}
               select new
               {
                   hqID = o.hq_id,
                   commandID = o.command_id,
                   regionID = o.region_id,
                   installationID = o.installation_id,
                   siteID = o.site_id
                   //hqId = o.count(org_site_id) AS count
               };
  </code></pre>
  
  <p>I have a Group By below:</p>
  
  <pre><code>var group1 = from a in context.asset
              group a by new
              {
                  a.hq_org_id,
                  a.command_org_id,
                  a.region_org_id,
                  a.installation_org_id,
                  a.site_org_id
  
                  // I am not sure how to get the count
  
              } into asset
              select new
              {
                  // I cannot figure out how to join
              }
  </code></pre>
  
  <p>Please help me with the syntax to GROUP BY with a JOIN on two tables, but on MULTIPLE columns of the two tables AND get the count for each group by.</p>