title: Converting SQL Rank() to LINQ, or alternative
body: <p>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.</p>
  
  <p>The reason I posted it here, which is maybe in error, is to see if anyone has an alternative to the <code>Rank()</code> statement so that I can get this switched over.  Alternatively, if there is a way to represent <code>Rank()</code> in LINQ that would be appreciated also.</p>
  
  <pre><code>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] &lt;= GETDATE())
              OR wh.WorkerCategory IS NULL
        ) 
  AND (
              (ce.[DisenrollmentDate] IS NULL) 
              OR (ce.[DisenrollmentDate] &gt;= GetDate())
        )
  </code></pre>