CODEWITHSUNDEEP
vb.netlinq
user1515742
user1515742

Reputation: 61

Need to convert SQL Query to LINQ

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

Upvotes: 0

Views: 174

Answers (1)

Tim Schmelter
Tim Schmelter

Reputation: 460038

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.

Upvotes: 1

Related Questions