Linq to SQL:选择最新的DISTINCT条目 [英] Linq to SQL: SELECT latest DISTINCT entries

问题描述

给出一个包含以下列的表:[AuditEntityId] [UserName] [CaseID]

Given a table with the columns: [AuditEntityId] [UserName] [CaseID]

我想要具有[AuditEntityId]最高的特定[UserName]的[CaseID]的不同列表.

I want a distinct list of [CaseID]'s for a specific [UserName] that has the highest [AuditEntityId].

基本上，我希望用户处理的最后五个案例，按照从最新到最早的顺序排列.

Basically, I want the last five cases that a user worked on, in the order from latest to oldest.

我通过对[CaseID]进行分组来实现与众不同:

I am achieving the distinct by grouping on [CaseID]:

var lastItems = baseController.db.AuditEntities
                                 .OrderByDescending(a => a.AuditEntityId)
                                 .GroupBy(a => a.CaseID)
                                 .Select(a => a.FirstOrDefault())
                                 .Where(a => a.CaseID != null && a.CaseID != 0)
                                 .Where(a => a.UserName == filterContext.HttpContext.User.Identity.Name)
                                 .Take(5)
                                 .ToList();

这达到了为我提供用户处理过的不同案例列表的目的，但是 .OrderByDescending 完全被忽略了.顶部linq语句转换为以下SQL:

This achieves the goal of giving me a distinct list of cases a user worked on, but the .OrderByDescending is totally ignored. The top linq statement is transformed into the following SQL:

SELECT TOP (5)
    [Limit1].[AuditEntityId] AS [AuditEntityId],
    [Limit1].[Reference] AS [Reference],
    [Limit1].[Timestamp] AS [Timestamp],
    [Limit1].[EntityName] AS [EntityName],
    [Limit1].[UserName] AS [UserName],
    [Limit1].[Action] AS [Action],
    [Limit1].[ComplaintId] AS [ComplaintId],
    [Limit1].[CaseID] AS [CaseID],
    [Limit1].[AuditReferencingStart] AS [AuditReferencingStart],
    [Limit1].[AuditReferencingEnd] AS [AuditReferencingEnd]
FROM
    (SELECT DISTINCT
        [Extent1].[CaseID] AS [CaseID]
    FROM
        [dbo].[AuditEntity] AS [Extent1] ) AS [Distinct1]
    CROSS APPLY (SELECT TOP (1)
        [Extent2].[AuditEntityId] AS [AuditEntityId],
        [Extent2].[Reference] AS [Reference],
        [Extent2].[Timestamp] AS [Timestamp],
        [Extent2].[EntityName] AS [EntityName],
        [Extent2].[UserName] AS [UserName],
        [Extent2].[Action] AS [Action],
        [Extent2].[ComplaintId] AS [ComplaintId],
        [Extent2].[CaseID] AS [CaseID],
        [Extent2].[AuditReferencingStart] AS [AuditReferencingStart],
        [Extent2].[AuditReferencingEnd] AS [AuditReferencingEnd]
    FROM
        [dbo].[AuditEntity] AS [Extent2]
    WHERE
        ([Distinct1].[CaseID] = [Extent2].[CaseID]) OR (([Distinct1].[CaseID] IS NULL) AND ([Extent2].[CaseID] IS NULL)) ) AS [Limit1]
WHERE
    ([Limit1].[CaseID] IS NOT NULL) AND ( NOT ((0 = [Limit1].[CaseID]) AND ([Limit1].[CaseID] IS NOT NULL))) AND (([Limit1].[UserName] = @p__linq__0))

提供的SQL根本没有ORDER.我可以将 .OrderByDescending 移到 .GroupBy(a => a.CaseID).Select(a => a.FirstOrDefault())之后它会在 TOP(1)被 SELECT 进行排序后对结果进行排序，这不会为我提供最新的审核条目.

The provided SQL does not have a ORDER in it at all. I can move the .OrderByDescending to after the .GroupBy(a => a.CaseID).Select(a => a.FirstOrDefault()), but then it orders the result after the TOP (1) has been SELECTed, which doesn't give me the latest audit entries.

我还尝试使用 MoreLinq 的 .DistinctBy ，但 .OrderByDescending 仍无法按预期工作:

I also tried making use of MoreLinq's .DistinctBy, but with this the .OrderByDescending still does not work as intended:

var lastItems = baseController.db.AuditEntities
                                 .Where(a => a.CaseID != null && a.CaseID != 0 && a.UserName == filterContext.HttpContext.User.Identity.Name)
                                 .DistinctBy(a => a.CaseID)
                                 .OrderBy(a => a.AuditEntityId)
                                 .Take(5)
                                 .ToList();

推荐答案

您需要订购结果集.试试

You need to order your result set. Try

var lastItems = baseController.db.AuditEntities                                 
                             .GroupBy(a => a.CaseID)
                             .Select(a => a.FirstOrDefault())
                             .Where(a => a.CaseID != null && a.CaseID != 0)
                             .Where(a => a.UserName == filterContext.HttpContext.User.Identity.Name)
                             .OrderByDescending(a => a.AuditEntityId)
                             .Take(5)
                             .ToList();

在 AuditEntityId 排序后的 CaseID分组中，随后进行其他操作时，该订单 OrderBy 不会对结果产生影响设置.

When you Group By CaseID after Order By AuditEntityId, followed by other operations, that order OrderBy does not have an effect on the result set.

修改

我不知道确切的模式.但是，通过我想要具有最高[AuditEntityId]的特定[UserName]的[CaseID]的不同列表" ，您可以尝试

Without knowing the exact schema, I can't be certain. But going by "I want a distinct list of [CaseID]'s for a specific [UserName] that has the highest [AuditEntityId]", you can try this

.db.AuditEntities
    .Where(a => a.CaseID != null 
                && a.CaseID != 0
                && a.UserName == filterContext.HttpContext.User.Identity.Name)
    .GroupBy(a => a.CaseID)
    .OrderByDescending(grp => grp.Max(g => g.AuditEntityId))        
    .Take(5)
    .Select(a => a.FirstOrDefault())
    .ToList();

这篇关于Linq to SQL:选择最新的DISTINCT条目的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！