Edit Question - KnowledgeHub

Title Body

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

Tags (comma-separated) Cancel