How to select top N rows for each group in a Entity Framework GroupBy with EF 3.1

by

Update (EF Core 6.0):

EF Core 6.0 added support for translating GroupBy result set projection, so the original code for taking (key, items) now works as it should, i.e.

var query = context.Set<DbDocument>()
    .Where(e => partnerIds.Contains(e.SenderId))
    .GroupBy(e => e.SenderId)
    .Select(g => new
    {
        g.Key,
        Documents = g.OrderByDescending(e => e.InsertedDateTime).Take(10)
    });

However flattening (via SelectMany) is still unsupported, so you have to use the below workaround if you need such query shape.

Original (EF Core 3.0/3.1/5.0):

This is a common problem, unfortunately not supported by EF Core 3.0/3.1/5.0 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)
    );

More Related Contents:

  1. EF Core Second level ThenInclude missworks
  2. Equivalent for .HasOptional in Entity Framework Core 1 (EF7)
  3. ef core 2 apply HasQueryFilter for all entity
  4. Loop/reflect through all properties in all EF Models to set Column Type
  5. EntityFramework Core automatic migrations
  6. Using EF Core ThenInclude() on Junction tables
  7. Changes in IDENTITY column after EF core 3
  8. What goes into DbContextOptions when invoking a new DbContext?
  9. Entity Framework Core 2.0 – Run migrations step by step
  10. EF Core 2: How to apply HasQueryFilter for all entities
  11. ORM Entities vs. Domain Entities under Entity Framework 6.0
  12. How to ensure proxies are created when using the repository pattern with entity framework?
  13. Entity Framework Code First – No Detach() method on DbContext
  14. Order navigation properties when using Include and/or Select methods with EF 4.1 Code-First?
  15. How to create index in Entity Framework 6.2 with code first
  16. How can I disable code first migrations
  17. Upgrade EF 4 EDMX to EF 6
  18. DbSet.Find method ridiculously slow compared to .SingleOrDefault on ID
  19. Stuffing an anonymous type in ViewBag causing model binder issues
  20. What is the syntax for self referencing foreign keys in EF Code First?
  21. check constraint entity framework
  22. int to string in Entity Framework
  23. EF Code First foreign key without navigation property
  24. Nullable property to entity field, Entity Framework through Code First
  25. LINQ to Entities for subtracting 2 dates
  26. Entity Framework Code First Many to Many Setup For Existing Tables
  27. How to set Identity Seed value in code-first?
  28. Changing Inherited Types in Entity Framework
  29. Why is my Entity Framework Code First proxy collection null and why can’t I set it?
  30. Why does the entity framework need an ICollection for lazy loading?

Leave a Comment