C# · 12月 27, 2021

c# – 将SQL Rank()转换为LINQ,或替代

我有以下sql语句可以按需要/期望工作.不过,我想把它翻译成一个LINQ语句(Lambda ??),以便它能与我的DAL的其余部分相匹配.但是我看不出如何在LINQ中模拟Rank().

我发布在这里的原因,这可能是错误的,是看看有没有人可以替代Rank()语句,以便我可以把它切换.或者,如果有一种方式来表示LINQ中的Rank(),那也将不胜感激.

USE CMOSELECT vp.[PersonID] AS [PersonId],ce.[EnrollmentID],vp.[FirstName],vp.[LastName],ce.[EnrollmentDate],ce.[DisenrollmentDate],wh.WorkerCategoryFROM [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(): var q = from s in class.student orderby s.Age descending select new { Name = s.name,Rank = (from o in class.student where o.mark > s.mark select o).Count() + 1 };