CODEWITHSUNDEEP
c#sql-serverlinqjoin
TAD II
TAD II

Reputation: 23

Having Trouble Converting SQL to LINQ when using a join on a select statement

Folks,

Trying to convert the following SQL-Server Command to LINQ. I have verified the SQL runs correctly via SSMS. 

select top 100 tts.* from tblLCState tts
INNER JOIN
    (SELECT fldLCID, MAX(fldStateDate) AS Statedate
    FROM tblLCState
    GROUP BY fldLCID) grptts 
ON tts.fldLCID = grptts.fldLCID 
AND tts.fldStateDate = grptts.Statedate
where fldLCStateCode = 1
order by fldStateDate desc

I am confused how to join the table tblLCState to the select statement. My attempt at the LNIQfollows:

from tRow in tblLCState
join irow2 in (from iRow in tblLCState
    group iRow by iRow.fldLCID into g
    select new {fldLCID = g.Key, MaxStateDate =  (from t2 in g select t2.fldStateDate).Max()} ) 
on ((tRow.fldStateDate = irow2.MaxStateDate) and (tRow.fldLCID = irow2.g.fldLCID))

The error is on the and operator in the on clause saying that a ) was expected. I have not attempted the where/order/top 100 at this point. Just have spent much time looking for the join with no luck on this form or any other. I have seen many posts to join on another table but unfortunately I don't have this luxury.

Any help would be appreciated.

Thanks

Tom D.

Upvotes: 1

Views: 91

Answers (1)

selami
selami

Reputation: 2498

LINQ

  var result = (from tRow in tblLCState
                join irow2 in (from iRow in tblLCState
                                group iRow by iRow.fldLCID into g
                                select new { fldLCID = g.Key, MaxStateDate = g.Max(k => k.fldStateDate) })
                on new { StateDate = tRow.fldStateDate, tRow.fldLCID } equals new { StateDate = irow2.MaxStateDate, irow2.fldLCID }
                select tRow);

Upvotes: 1

Related Questions