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

Question

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.

In C# you need to use && and not and

stuartd
– stuartd

2016-12-02 13:26:31 +00:00
Commented Dec 2, 2016 at 13:26 — stuartd
– stuartd, Commented Dec 2, 2016 at 13:26

selami · Accepted Answer · 2016-12-05 07:13:07Z

1

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);

edited Dec 5, 2016 at 7:13

answered Dec 2, 2016 at 13:36

selami

2,4981 gold badge21 silver badges26 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

TAD II Over a year ago

Selami.....That worked fine in my linqpad (with the exception of needed to remove the .g. in last line) however I am getting an error when I put it in my C# code. It is getting a compile error: Error CS1936 Could not find an implementation of the query pattern for source type 'tblLCState'. 'GroupBy' not found. I have verified that I have the correct using directives in my code. Any ideas on what I am not seeing??

selami Over a year ago

@TomD Make sure System.Data.Linq reference is included in the project.

TAD II Over a year ago

@salami verified that the reference is included in the project. All other linq code is working.

TAD II Over a year ago

@salami found my bug and it works fine in code and on linqpad. Thanks for your help

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related