title: LINQ for RANK() and CASE WHEN in VB.NET
body: <p>I apologize, if a similar question is already asked in <strong>VB.Net</strong> for the <strong>RANK()</strong> and <strong>CASE WHEN</strong>. </p>
  
  <p>I have two tables. The script with the required columns are below:</p>
  
  <p>Table 1:</p>
  
  <pre><code>SELECT S.* INTO dbo.LoanHeader
  FROM
  (
      SELECT 1 LoanHeaderId, 1234 LoanNumber, GETDATE() EffectiveOn
      UNION ALL
      SELECT 2 LoanHeaderId, 2234 LoanNumber, GETDATE() EffectiveOn
      UNION ALL
      SELECT 3 LoanHeaderId, 3234 LoanNumber, GETDATE() EffectiveOn
  )S
  </code></pre>
  
  <p>Table 2:</p>
  
  <pre><code>SELECT S.* INTO dbo.LoanHeaderHistory
  FROM
  (
      SELECT 1 LoanHeaderId, 1 NewValue, 'LoanReviewStatusId' ColumnName, DATEADD(DD, -3, GETDATE()) EnteredDate
      UNION ALL
      SELECT 1 LoanHeaderId, 2 NewValue, 'LoanReviewStatusId' ColumnName, DATEADD(DD, -2, GETDATE()) EnteredDate
      UNION ALL
      SELECT 1 LoanHeaderId, 3 NewValue, 'LoanReviewStatusId' ColumnName, DATEADD(DD, -1, GETDATE()) EnteredDate
      UNION ALL
      SELECT 1 LoanHeaderId, 1 NewValue, 'LoanReviewStatusId' ColumnName, GETDATE() EnteredDate
  )S
  </code></pre>
  
  <p>I would like to write a <strong>LINQ statement in VB.Net</strong> of the SQL below. There are other tables involved and if I start to mention why I need it in this way will take too long. But the intention is to use one of the dates based on the NewValue in the criteria:</p>
  
  <pre><code>SELECT
      LH.LoanHeaderId,
      LHH.NewValue,
      LHH.EnteredDate
  FROM dbo.LoanHeader LH
  LEFT JOIN 
  (
      SELECT
          LoanHeaderId,
          NewValue,
          EnteredDate,
          RANK() OVER (PARTITION BY LoanHeaderId ORDER BY EnteredDate DESC) HRank
      FROM dbo.LoanHeaderHistory
      WHERE ColumnName = 'LoanReviewStatusId'
  )LHH ON LHH.LoanHeaderId = LH.LoanHeaderId AND LHH.HRank = 1
  WHERE CASE WHEN ISNULL(LHH.NewValue, 1) IN (2, 3) THEN LHH.EnteredDate ELSE LH.EffectiveOn END &gt;= LH.EffectiveOn
  </code></pre>
  
  <p>I started by doing it this way:</p>
  
  <pre><code>Dim columnName = "LoanReviewStatusId"
  Dim query = (From lh In e.LoanHeaderEntities_
               From lhh In e.LoanHeaderHistoryEntities.OrderByDescending(Function(i) i.EnteredDate).Where(Function(i) lh.LoanHeaderId = i.LoanHeaderId AndAlso i.ColumnName = columnName).DefaultIfEmpty() _
               Select New With{ _
                       lh.LoanHeaderId, _
                       lhh.NewValue, _
                       lhh.EnteredDate _
                       })
  query = query.Where(Function(i) If(Not IsNothing(i.LoanReviewStatusId) AndAlso (i.LoanReviewStatusId = 2 Or i.LoanReviewStatusId = 3), i.LoanStatusChangedDate, i.EffectiveOn) &gt;= i.EffectiveOn)
  Return query.ToList()
  </code></pre>