1

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
mahdis dezfouli
  • 173
  • 3
  • 19

1 Answers1

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...

Bahman_Aries
  • 4,658
  • 6
  • 36
  • 60
  • 1
    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. You can query directly to the database, by using the DB entities: var query1 = from grp in ctnx.tblMets group grp by grp.MetID_FK into g let maxId = g.Max(gId => gId.GroupDataID) select new { metId = g.Key, maxId }; – Luc Jun 13 '15 at 13:19
  • @Luc, u r absolutely right, however it's not the matter of performance here, I wanted to show how sql to linq conversion is done on a step by step basis. – Bahman_Aries Jun 13 '15 at 13:28
  • @Luc Thank you very much . I use your solution. – mahdis dezfouli Jun 13 '15 at 13:28