如何对记录进行分组并仅检索前N个记录的第一个组

本文介绍了如何对记录进行分组并仅检索前N个记录的第一个组的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！ 问题描述

我有以下记录集

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，而是在内存中处理它们(所谓的客户端评估)，这使它们的效率非常低下.考虑到这一点，您最好将工作分为两个查询:一个查询采用第一组的 ClientName ，第二查询采用所需的结果:

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