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?

1
1
4/16/2020 7:25:07 AM

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)
    );
5
12/24/2019 11:53:03 AM

Popular Answer

maybe try a different approach?

 var partnersList = context.partnerIds.Where(x=> "your where clause here").Take(10).Tolist()


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow