title: LINQ GROUP BY and MAX()
body: <p>I'm trying to find out how to write an SQL sentence in LINQ but I can't find a way to do it for the moment, this is the SQL command:</p>
  
  <pre><code>SELECT cs.Site_Name, MAX(ed.EffectiveDate_Date)
  FROM [WAPMaster].[Factsheets].[EffectiveDate] ed,
  [WAPMaster].[Configuration].[Site] cs
  WHERE cs.Site_Id = ed.EffectiveDate_SiteId
  GROUP BY cs.Site_Name
  </code></pre>
  
  <p>Can someone help me witht he linq syntax please?</p>
  
  <p>**I'm trying this so far (thanks levanlevi)</p>
  
  <pre><code>var test = (from e in this._wapDatabase.EffectiveDates
              join c in this._wapDatabase.Sites 
              on c.Site_Id equals e.EffectiveDate_SiteId
              group e by c.Site_Name into r
              select new
              {
                  r.Key.SiteName,
                  EffectiveDate = r.Max(d =&gt; d.EffectiveDate_Date)
              }); 
  </code></pre>
  
  <p>But I'm getting the following error:</p>
  
  <p><a href="https://i.stack.imgur.com/AkJ5V.png" rel="nofollow noreferrer">http://i.stack.imgur.com/AkJ5V.png</a></p>