How to group records and retrieve only first group with top N records

Question

I have the following record set

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

I am using LINQ to SQL in EF Core.

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.

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

So here is my query

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

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

Update 1

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]

Your `OrderBy` isn't going to have much meaning after the `GroupBy` - you have to order the groups. So how do you want to order the groups? (Also, a simple `x.BatchID == null` may product better SQL.) — NetMage, Feb 16 '18 at 20:35
FYI, EF Core has nothing in common with LINQ to SQL or EF6. For instance, as you already noticed it currently does not translate `GroupBy` queries to SQL. — Ivan Stoev, Feb 16 '18 at 20:35
well the grouping has to be done in SQL. Whats the use of retrieving all the records in memory and do grouping in memory and just take top N. — LP13, Feb 16 '18 at 20:47
You will have to wait for EF Core 2.1. That's one reason I don't think EF Core is ready for production. — NetMage, Feb 16 '18 at 20:52

score 4 · Accepted Answer · answered Feb 16 '18 at 21:12

First, usually OrderBy has no effect (is ignored) if followed by GroupBy in Queryable implementations which translate LINQ queries to 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();

score 2 · Answer 2 · answered Feb 16 '18 at 20:36

2

You will have to project the group result like something this:

result = await _DBContext.BatchRequests
            .Where(x => x.BatchID.HasValue == false)
            .OrderBy(x => x.CreatedDateTime)
            .ThenBy(x => x.ClientName)
            .GroupBy(x => x.ClientName)
            .Select( x => new { ClientName= x.ClientName,
                                 TopFive = x.Take(5)
                         })
            .FirstAsync();

answered Feb 16 '18 at 20:36

Ehsan Sajjad

61,834
16
105
160

did not work. It produces the same result and returns client `B`. In-fact the SQL profiler shows the exact same SQL as my query as i posted in Update 1 above – LP13 Feb 16 '18 at 20:53
Are you sure this is the SQL getting generated? – Ehsan Sajjad Feb 16 '18 at 20:57
yes, as someone pointed out there is no effect of order by when you have group by – LP13 Feb 16 '18 at 21:32

How to group records and retrieve only first group with top N records

2 Answers2

Linked