如何对记录进行分组并仅检索前N个记录的第一个组 [英] How to group records and retrieve only first group with top N records

问题描述

我有以下记录集

ID          BatchID     ClientName           CreatedDateTime
----------- -------------- --------------- -----------------------
1           NULL           B             2018-02-16 19:07:46.320
2           NULL           B             2018-02-16 19:07:46.320
3           NULL           B             2018-02-16 19:07:46.597
4           NULL           B             2018-02-16 19:07:46.597
5           NULL           B             2018-02-16 19:10:10.260
6           NULL           B             2018-02-16 19:10:10.260
7           NULL           B             2018-02-16 19:21:34.303
8           NULL           B             2018-02-16 19:21:34.303
9           NULL           B             2018-02-16 19:21:44.780
10          NULL           B             2018-02-16 19:21:44.780
11          NULL           A             2018-02-16 19:24:35.623
12          NULL           A             2018-02-16 19:24:35.623
13          NULL           A             2018-02-16 19:24:42.867
14          NULL           A             2018-02-16 19:24:42.867

我在EF Core中使用LINQ to SQL.

I am using LINQ to SQL in EF Core.

我想过滤 BatchID 为 NULL 的记录，然后按 CreatedDateTime 排序过滤的记录，然后按进行分组ClientName ，然后从第一个组中获取前5条记录.

I want to filter the records where BatchID is NULL and then order the filtered records by CreatedDateTime and then group them by ClientName and then take top 5 records from the first Group.

基于上面的给定记录集，它应该返回ClientName B

Based on the given record set above it should return records with Ids 1,2,3,4,5 for ClientName B

这是我的查询

 var result = await _DBContext.BatchRequests
                .Where(x => x.BatchID.HasValue == false)
                .OrderBy(x => x.CreatedDateTime)
                .GroupBy(x => x.ClientName)
                .FirstAsync();

问题
1>查询返回客户端 A
2>如何仅获取前5条记录

ISSUE
1> The query returns Client A
2> How do i Take only top 5 records

更新1

Sql事件探查器显示以下内容，它甚至不在SQL中分组

Sql Profiler show the following, it doesnt even group in SQL

SELECT [x].[ID], [x].[BatchID], [x].[ClientName], [x].[CreatedDateTime]
FROM [BatchRequests] AS [x]
WHERE CASE
    WHEN [x].[BatchID] IS NULL
    THEN CAST(1 AS BIT) ELSE CAST(0 AS BIT)
END <> 0
ORDER BY [x].[ClientName]

推荐答案

首先，通常 OrderBy 不起作用(被忽略)，如果在中紧跟 GroupBy 可查询的实现将LINQ查询转换为SQL.

First, usually OrderBy has no effect (is ignored) if followed by GroupBy in Queryable implementations which translate LINQ queries to SQL.

其次，EF Core当前不将 GroupBy 查询转换为SQL，而是在内存中处理它们(所谓的

Second, EF Core currently does not translate GroupBy queries to SQL, but processes them in memory (so called client evaluation), which makes them highly inefficient. With that taken into account, you'd better split the work on two queries - one to take the ClientName of the first group, and second to take the desired result:

var baseQuery = _DBContext.BatchRequests
    .Where(x => x.BatchId == null)
    .OrderBy(x => x.CreatedDateTime);

var clientName = await baseQuery
    .Select(x => x.ClientName)
    .FirstOrDefaultAsync();

var result = await baseQuery
    .Where(x => x.ClientName == clientName)
    .Take(5)
    .ToListAsync();

可以将两个查询组合起来，但是我不确定它是否会更有效(可能更糟):

Actualy you can combine the two queries, but I'm not sure whether it will be more efficient (could be worse):

var baseQuery = _DBContext.BatchRequests
    .Where(x => x.BatchId == null)
    .OrderBy(x => x.CreatedDateTime);

var result = await baseQuery
    .Where(x => x.ClientName == baseQuery.Select(y => y.ClientName).FirstOrDefault())
    .Take(5)
    .ToListAsync();

这篇关于如何对记录进行分组并仅检索前N个记录的第一个组的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！