问题描述
我有以下 SQL 语句,可以按需要/预期工作.但是我想将它翻译成 LINQ 语句(Lambda??),以便它适合我的 DAL 的其余部分.但是我无法弄清楚如何在 LINQ 中模拟 Rank().
I have the below SQL statement that works as desired/expected. However I would like to translate it into a LINQ statement(Lambda??) so that it will fit with the rest of my DAL. However I cannot see to figure out how to simulate Rank() in LINQ.
我在这里发布它的原因(可能是错误的)是为了看看是否有人可以替代 Rank() 语句,以便我可以将其转换.或者,如果有一种在 LINQ 中表示 Rank() 的方法,那也将不胜感激.
The reason I posted it here, which is maybe in error, is to see if anyone has an alternative to the Rank() statement so that I can get this switched over. Alternatively, if there is a way to represent Rank() in LINQ that would be appreciated also.
USE CMO SELECT vp.[PersonID] AS [PersonId] ,ce.[EnrollmentID] ,vp.[FirstName] ,vp.[LastName] ,ce.[EnrollmentDate] ,ce.[DisenrollmentDate] ,wh.WorkerCategory FROM [dbo].[vwPersonInfo] AS vp INNER JOIN ( [dbo].[tblCMOEnrollment] AS ce LEFT OUTER JOIN ( SELECT * ,RANK()OVER(PARTITION BY EnrollmentID ORDER BY CASE WHEN EndDate IS NULL THEN 1 ELSE 2 END, EndDate DESC, StartDate DESC) AS whrank FROM [dbo].[tblWorkerHistory] WHERE WorkerCategory = 2 ) AS wh ON ce.[EnrollmentID] = wh.[EnrollmentID] AND wh.whrank = 1 ) ON vp.[PersonID] = ce.[ClientID] WHERE (vp.LastName NOT IN ('Client','Orientation','Real','Training','Matrix','Second','Not')) AND ( (wh.[EndDate] <= GETDATE()) OR wh.WorkerCategory IS NULL ) AND ( (ce.[DisenrollmentDate] IS NULL) OR (ce.[DisenrollmentDate] >= GetDate()) )
推荐答案
下面的示例展示了我如何在 Linq 中模拟 Rank():
Here's a sample that shows how I would simulate Rank() in Linq:
var items = new[] { new { Name = "1", Value = 2 }, new { Name = "2", Value = 2 }, new { Name = "3", Value = 1 }, new { Name = "4", Value = 1 }, new { Name = "5", Value = 3 }, new { Name = "6", Value = 3 }, new { Name = "7", Value = 4 }, }; var q = from s in items orderby s.Value descending select new { Name = s.Name, Value = s.Value, Rank = (from o in items where o.Value > s.Value select o).Count() + 1 }; foreach(var item in q) { Console.WriteLine($"Name: {item.Name} Value: {item.Value} Rank: {item.Rank}"); }
输出
Name: 7 Value: 4 Rank: 1 Name: 5 Value: 3 Rank: 2 Name: 6 Value: 3 Rank: 2 Name: 1 Value: 2 Rank: 4 Name: 2 Value: 2 Rank: 4 Name: 3 Value: 1 Rank: 6 Name: 4 Value: 1 Rank: 6