CODEWITHSUNDEEP
c#sqllinqlinq-to-sql
kevlar1818
kevlar1818

Reputation: 3125

Linq query for only the first N rows for each unique ID

Say I have an IQueryable that will return a datatype with an ID property (column).

I want to further filter my query (I don't want to evaluate the query) as follows:

For each unique ID from the main query, I want to Take(n), where n is some arbitrary number.

That is, I want to only keep the first n rows for each unique ID.

I can get the distinct ID's...

var ids = query.Select(q => q.ID).Distinct();

and I can Take(n) with the rest of them, but I'm stumped on connecting the two:

query = query.<FOR EACH DISTINCT ID>.Take(n);

The accepted answer works, but is slow for a large table. I wrote this question as a follow-up.

Upvotes: 6

Views: 3145

Answers (1)

Sergey Kalinichenko
Sergey Kalinichenko

Reputation: 726559

You can do it like this:

query = query.GroupBy(q => q.ID).SelectMany(g => g.Take(n));

The GroupBy brings together the records with identical IDs, letting you process them as a group; SelectMany takes each group, limits the number of its members to n, and puts the results back into one flat list.

Upvotes: 6

Related Questions