Need to convert SQL Query to LINQ

Question

I have the following SQL query that I need to convert into LINQ with VB.NET

SELECT *
FROM    (SELECT Id
         ,LocationCode 
         ,LocationName
         ,ContactName
         ,ContactEmail
         ,Comments
         ,SBUName
         ,CreatedBy
         ,CreatedDtm
         ,ModifiedBy
         ,ModifiedDtm
         ,ROW_NUMBER() OVER (PARTITION BY LocationCode ORDER BY ID) AS RowNumber
FROM testDB ) as rows
  WHERE ROWNUMBER = 1

There are many duplicates of location code so I only want to display one record of each and the user will be able to edit the information. Once they edit I will save the info for all records that are for that specific location code.

I couldn't use DISTINCT here, it would still bring back all of the data since the CreatedBy/ModifiedBy are different.

By using the following LINQ query to select all of the data, is there a way I can get the DISTINCT records for LocationCode out of it?

queryLocMaint = From MR In objcontextGSC.TestDB
                                Select MR.Id,
                                    MR.LocationCode,
                                    MR.LocationName,
                                    MR.SBUName,
                                    MR.ContactName,
                                    MR.ContactEmail,
                                    MR.Comments,
                                    MR.CreatedBy,
                                    MR.CreatedDtm,
                                    MR.ModifiedBy,
                                    MR.ModifiedDtm()

possible duplicate of [Distinct in Entity framework](http://stackoverflow.com/questions/8536129/distinct-in-entity-framework). In LINQ-to-SQL you can do the same thing. — Gert Arnold, May 04 '15 at 14:48

score 1 · Accepted Answer · answered May 04 '15 at 14:57

1

ROW_NUMBER is not supported in LINQ, maybe you can use this GROUP BY approach:

Dim q = From mr In objcontextGSC.TestDB
        Group mr By mr.LocationCode Into LocationCodeGroup = Group
        Select LocationCodeGroup.OrderBy(Function(mr) mr.Id).First()

This takes the first row of each LocationCode-group ordered by id.

answered May 04 '15 at 14:57

Tim Schmelter

450,073
74
686
939

Thanks Tim this worked exactly how I needed it to. Just had to change the mr.Id to mr.LocationCode and it displayed all that I need. – user1515742 May 04 '15 at 15:36

Need to convert SQL Query to LINQ

1 Answers1