Selecting mutiple rows for each distinct value in column EF Core in C#
c# entity-framework entity-framework-core
Question
Language: C#
For simplicitys sake lets say I have a DbSet containing products and the Product class looks like this:
public class Product
{
public DateTime Time { get; set; }
public string Category { get; set; }
}
Time is a record of when the product was added to the database.
I have a list of strings with all categories that exist in the database, they are "A", "B", "C", "D" and "E". For each category I want to select the 10 most recently added products. Is there a way to do this with a single query in EF Core?
Accepted Answer
This is a common problem, unfortunately not supported by EF Core 3.0/3.1 query translator specifically for GroupBy.
The workaround is to do the groping manually by correlating 2 subqueries - one for keys and one for corresponding data.
Applying it to your examples would be something like this.
If you need (key, items) pairs:
var query = context.Set<DbDocument>()
.Where(t => partnerIds.Contains(t.SenderId))
.Select(t => t.SenderId).Distinct() // <--
.Select(key => new
{
Key = key,
Documents =
context.Set<DbDocument>().Where(t => t.SenderId == key) // <--
.OrderByDescending(t => t.InsertedDateTime).Take(10)
.ToList() // <--
});
If you need just flat result set containing top N items per key:
var query = context.Set<DbDocument>()
.Where(t => partnerIds.Contains(t.SenderId))
.Select(t => t.SenderId).Distinct() // <--
.SelectMany(key => context.Set<DbDocument>().Where(t => t.SenderId == key) // <--
.OrderByDescending(t => t.InsertedDateTime).Take(10)
);
Popular Answer
maybe try a different approach?
var partnersList = context.partnerIds.Where(x=> "your where clause here").Take(10).Tolist()
Related Questions
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow