LINQ for RANK() and CASE WHEN in VB.NET

问题:

I apologize, if a similar question is already asked in VB.Net for the RANK() and CASE WHEN.

I have two tables. The script with the required columns are below:

Table 1:

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

Table 2:

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

I would like to write a LINQ statement in VB.Net 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:

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 >= LH.EffectiveOn

I started by doing it this way:

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) >= i.EffectiveOn)
Return query.ToList()

回答1:

Ha! I did it. I found some helpful info from here.

The VB.Net code is

Dim query = (From lh In e.LoanHeaderEntities _
         From lhhGrp In e.LoanHeaderHistoryEntities.Where(Function(i) i.ColumnName = colName And i.LoanHeaderId = lh.LoanHeaderId) _
        .OrderByDescending(Function(i) i.EnteredDate) _
        .Take(1) _
        .DefaultIfEmpty() _     
         Select New HeaderInfo With {_
             .LoanHeaderId = lh.LoanHeaderId, _             
             .EffectiveOn = lh.EffectiveOn, _
             .LoanStatusChangedDate = lhhGrp.EnteredDate, _
             .LoanReviewStatusId = lhhGrp.NewValue _
            })
'Additional condition for CASE WHEN
If (isActive) Then
    query = query.Where(Function(i) If(i.LoanReviewStatusId = LoanReviewStatus.Final Or i.LoanReviewStatusId = LoanReviewStatus.Completed, i.LoanStatusChangedDate, i.EffectiveOn) >= i.EffectiveOn)
End If
Return query.ToList()

I could not stop laughing when I saw the query it executes behind the scenes. Check the CASE WHEN statement it generates below. I ignored the ISNULL because my field is NOT NULL.

SELECT
    [Extent1].LoanHeaderId,
    [Limit1].NewValue,
    [Limit1].EnteredDate
FROM [dbo].[LoanHeader] AS [Extent1]
OUTER APPLY
(
    SELECT TOP (1)
        [Project1].[LoanHeaderId] AS [LoanHeaderId],
        [Project1].[NewValue] AS [NewValue],
        [Project1].[EnteredDate] AS [EnteredDate]
        FROM
        (
            SELECT
                [Extent2].[LoanHeaderId] AS [LoanHeaderId], 
                [Extent2].[NewValue] AS [NewValue], 
                [Extent2].[EnteredDate] AS [EnteredDate]
            FROM [dbo].[LoanHeaderHistory] AS [Extent2]
            WHERE ([Extent2].[ColumnName] = 'LoanReviewStatusId')
            AND ([Extent2].[LoanHeaderId] = [Extent1].[LoanHeaderId])
        )  AS [Project1]
        ORDER BY [Project1].[EnteredDate] DESC
) AS [Limit1]
WHERE ((CASE WHEN ((CASE WHEN (CASE WHEN ((3 =  CAST( [Limit1].[NewValue] AS int)) OR (2 =  CAST( [Limit1].[NewValue] AS int))) THEN cast(1 as bit) WHEN ( NOT ((3 =  CAST( [Limit1].[NewValue] AS int)) OR (2 =  CAST( [Limit1].[NewValue] AS int)))) THEN cast(0 as bit) END IS NULL) THEN cast(0 as bit) WHEN ((3 =  CAST( [Limit1].[NewValue] AS int)) OR (2 =  CAST( [Limit1].[NewValue] AS int))) THEN cast(1 as bit) WHEN ( NOT ((3 =  CAST( [Limit1].[NewValue] AS int)) OR (2 =  CAST( [Limit1].[NewValue] AS int)))) THEN cast(0 as bit) END) = 1) THEN  CAST( [Limit1].[EnteredDate] AS datetime) ELSE  CAST( [Extent1].[EffectiveOn] AS datetime) END) >=  CAST( [Extent1].[EffectiveOn] AS datetime))

Tell me who is better in writing queries, EF or me?