需要将SQL Query转换为LINQ

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

我有以下SQL查询,我需要使用VB.NET转换为LINQ

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.

我不能在这里使用DISTINCT,它仍会带回所有数据,因为CreatedBy / ModifiedBy是不同的。

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?

通过使用以下LINQ查询来选择所有数据,有没有办法可以从中获取LocationCode的DISTINCT记录?

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

1 个解决方案

#1

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

LINQ不支持ROW_NUMBER,也许您可以使用此GROUP BY方法:

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.

这将按id排序每个LocationCode组的第一行。

#1