how to change sql query to linq?

Question

How to change this sql query to linq? I have three join that last join is select query.

select  mod.ModSerial,
        met.MetSerial,
        met.WaterNo,
        met.PowerNo,
        Gro.GroupDate
     from MetlDB.dbo.tblMet AS met
     join MetDB.dbo.tblMod AS mod on mod.ModID= met.ModID_FK  
     join MetDB.dbo.tblGroupData As Gro on Gro.MetID_FK= met.MetID
     join(select MetID_FK,
           max(GroupDataID) as maxgroupdata
           from MetDB.dbo.tblGroupData
           group by MetID_FK) g on met.MetID=g.MetID_FK and Gro.GroupDataID=g.maxgroupdata

Answer 1

Here you go pretty:

    using (MetDBEntities ctnx = new MetDBEntities())
    {
        var query1 = from grp in ctnx.tblGroupDatas
                     group grp by grp.MetID_FK into g
                     let maxId = g.Max(gId => gId.GroupDataID)
                     select new { metId = g.Key, maxId };

        var query2 = from met in ctnx.tblMets
                     from mod in ctnx.tblMods.Where(mo => mo.ModId == met.ModId_fk)
                     from grp in ctnx.tblGroupDatas.Where(gr => gr.MetID_FK == met.MetId)
                     from q1 in query1.Where(q => q.metId == met.MetId && grp.GroupDataID == q.maxId)
                     select new { mod.ModSerial, met.MetSerial, met.Waterno, met.powerno, grp.GroupDate };

        var result = query2.ToList();
    }

Edit: I Modified my answer based on this comment:

This methode is will cause problems with larger dataset, because you get every record from tblMets and tblMods and tblGroupDatas from the database into memory...