LINQ的RANK（）和CASE当VB.NET(LINQ for RANK() and CASE W

我道歉，如果类似的问题在VB.Net已经问了RANK（）和CASE WHEN。

我有两个表。 与所需的列脚本低于：

表格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

表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

我想在下面的SQL的VB.Net写一个LINQ声明 。 这里涉及其他表，如果我开始说为什么我需要它这样会花费太长的时间。 但我们的目的是使用标准的基础上的NewValue日期之一：

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

我开始做这种方式：

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

Answer 1:

哈！ 我做的。 我发现一些有用的信息从这里 。

该VB.Net代码

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

我不能停止，当我看到它执行的幕后查询笑。 检查的情况下声明它下面产生。 我忽略了ISNULL，因为我的字段是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))

告诉我谁是更好的书面查询，EF还是我？

文章来源: LINQ for RANK() and CASE WHEN in VB.NET