转换SQL排名（）到LINQ，或替代 [英] Converting SQL Rank() to LINQ, or alternative

问题描述

我有工作按要求/预期下面的SQL语句。不过，我想将它翻译成一个LINQ声明（LAMBDA ??），以便它会与我DAL的其余部分适合。不过，我看不到弄清楚如何在LINQ模拟排名（）。

我张贴在这里的原因，这也许是错误的，是看是否有人有对排名语句中选择（），这样我可以得到这个切换过度。另外，如果有一种方法来重新present 排名（）在LINQ这将是AP $ P $也pciated。

 使用CMOSELECT副总裁。[是PersonID] AS [PERSONID]
            ，CE [EnrollmentID]
            ，副总裁。[名字]
            ，副总裁。[姓氏]
            ，CE [EnrollmentDate]
            ，CE [DisenrollmentDate]
            ，wh.WorkerCategoryFROM [DBO]。[vwPersonInfo] AS副总裁
            内部联接
            （
                  [DBO]。[tblCMOEnrollment]以Ce
                  LEFT OUTER JOIN
                        （
                              选择   *
                                          ，RANK（）OVER（PARTITION BY EnrollmentID ORDER BY CASE WHEN结束日期为null，则1 ELSE 2 END，结束日期DESC，起始日期DESC）AS whrank
                              FROM [DBO]。[tblWorkerHistory]
                              WHERE WorkerCategory = 2
                        ）AS WH
                              在CE [EnrollmentID] = WH [EnrollmentID]和wh.whrank = 1
            ）
                  虚拟样机。[是PersonID] = CE。[客户端ID]WHERE（vp.LastName NOT IN（'客户'，'方向'，'真正的'，'培训'，'矩阵'，'二'，'不'））
AND（
            （WH [结束日期] LT; = GETDATE（））
            OR wh.WorkerCategory IS NULL
      ）
AND（
            （CE [DisenrollmentDate] IS NULL）
            或（CE [DisenrollmentDate]方式＆gt; = GETDATE（））
      ）
 

解决方案

下面是显示了如何将模拟LINQ的排名（）的例子：

 变种Q =从s在class.student
                 排序依据s.Age降
                 选择新{
                     名称= s.name，
                     排名=（从o在class.student
                             其中，o.mark＆GT; s.mark
                             选择o）.Count之间的（）+ 1
                 };
 

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

解决方案

Here's a sample that shows how I would simulate Rank() in Linq:

   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 
                 };

这篇关于转换SQL排名（）到LINQ，或替代的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！