LINQ GROUP BY and MAX()

Question

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:

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

Can someone help me witht he linq syntax please?

**I'm trying this so far (thanks levanlevi)

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 => d.EffectiveDate_Date)
            }); 

But I'm getting the following error:

http://i.stack.imgur.com/AkJ5V.png

Solution 1

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



from e in WAPMaster.Factsheets.EffectiveDate
join c in WAPMaster.Configuration.Site
on c.Site_Id equals e.EffectiveDate_SiteId
group e by c.Site_Name into r
select new { SiteName = r.Key, EffectiveDate = r.Max(d=>d.EffectiveDate_Date)}

Solution 2

var test = (from effectiveDates in this._wapDatabase.EffectiveDates                         
            from sites in this._wapDatabase.Sites                         
            where sites.Site_Id = effectiveDates.EffectiveDate_SiteId
                     group effectiveDates by sites.Site_Id into g                         
             select new {  siteId = g.key , effectiveDate = g.max(ed => ed.EffectiveDate_Date)}); 

Comments

[Remo H. Jansen]

Thanks that looks really close, it is telling me that "can not resolve join method" do you know why could it be?

[levi]

Are you using entity framework? Are there connections between LinqToSql Classes?