Support ability to select top N of each group

[Jonatthu]

I am working on a batch query generator, this should be able to get an array of Ids and get first 10 of each id, and be able to skip, order and others. This query behaves like the result in the image but currently in EF Core 2.1 is resolved in memory which is extremely not performant.

The next query represents a similar result but this is resolved on memory.

var goo = this.dbSet
	.Where(x => new[]{1 , 2}.Contains(x.WaterId))
	.OrderBy(x => x.Id)
	.ThenByDescending(x => x.NumberOfDrops)
        .GroupBy(x => x.Id)
	.Skip(0)
	.Take(10)
	.Select(x => new Drops {
		Id = x.First().Id,
		WaterId = x.First().WaterId
	})
        .ToList();

I am not looking for give support to that query in specific, but need a query that is able to get results like the image making sorting, offsets and selecting top n's

[ajcvickers]

@divega to follow up.

[divega]

@Jonatthu Based on the SQL example provided and your description of the problem, I believe you intended to write a query that looks more less like this in LINQ:

                var top = db.Employees
                    .GroupBy(x => x.Occupation)
                    .Select(g => g.OrderByDescending(x => x.YearlyIncome).Take(2))
                    .SelectMany(g => g);

It is true that for this kind of query, we do part of the evaluation in memory. We bring all the rows into memory, and we discard rows of each group that didn't fit in the Take count. However we push down an ORDER BY Occupation to the database, to be able to process each group together.

We have don't some (although relatively little) thinking about translating this kind of conditions. This is closely related to the issue about window functions, tracked in the backlog at #12747.

In the meantime, I would suggest you can work around passing the actual SQL query to FromSql. For example:

                var top = db.Employees.FromSql(@"
                    SELECT [y].[Id], [y].[Name], [y].[Occupation], [y].[YearlyIncome]
                    FROM (
                        SELECT [x].*, 
                            ROW_NUMBER() OVER (
                                PARTITION BY [x].[Occupation] 
                                ORDER BY [x].[YearlyIncome] DESC) AS [ROW_NUMBER]
                        FROM [Employees] AS [x]) AS [y] 
                    WHERE [y].[ROW_NUMBER] < = 2"
                  );

@smitpatel suggested that there is probably a different workaround using OUTER APPLY.

[divega]

Note for triage: Leaving this open because I think this is a reasonable scenarios for us to support in the long term. It is also related to window functions tracked in #12747.

[Jonatthu]

@divega The workaround would work if there would not be other ".Where" filters to apply before create the GroupBy, I would need to wait until this becomes a reality and yes you are right the query that you wrote gives the desired result but resolved on memory.

I think this is an important feature something like this:

Expression<Func<Employee, Employee>> customLamdaSelector = ...;

var top = db.Employees
.Where(x => new [] {1, 3, 5}.Contains(x.RelatedFKId))
.Where(x => x.name == "SomeName")
.Where(x => x.AddedDate < SomeDate)
.GroupBy(x => x.Occupation)
.Select(g => 
    g.OrderByDescending(x => x.YearlyIncome)
      .ThenOrderBy(x => x.Id)
      .Skip(10)
      .Take(2)
)
.SelectMany(customLamdaSelector);

[smitpatel]

Any where before GroupBy would be server evaluated if they are translatable.

[yyjdelete]

As an workaround, if directly groupby an table which has primary key, the below code may can be used to get the result(but may be slow)
Test the below code works with SQLServer(2.1.8,3.0.0preview2), PostgreSQL(3.0.0preview1), but seems hit some strange limited and exception edge when try to write case2

//Take 1, simple PK
                /*
                      SELECT [outer].[PK], [outer].[GroupByKey], [outer].[OrderByKey]
                      FROM [Test001] AS [outer]
                      WHERE (
                          SELECT TOP(1) [inner].[PK]
                          FROM [Test001] AS [inner]
                          WHERE [inner].[GroupByKey] = [outer].[GroupByKey]
                          ORDER BY [inner].[OrderByKey]
                      ) = [outer].[PK]
                */
                var case1 = ctx.Test001.Where(outer => ctx.Test001
                                .Where(inner => inner.GroupByKey == outer.GroupByKey)
                                .OrderBy(inner => inner.OrderByKey)
                                .Select(inner => (int?)inner.PK)//must cast to nullable here
                                .FirstOrDefault() == outer.PK)
                                .ToList();

//Take more than one line Or composite PK used
                /*
      SELECT [outer].[PK1], [outer].[PK2], [outer].[GroupByKey], [outer].[OrderByKey]
      FROM [Test002] AS [outer]
      WHERE EXISTS (
          SELECT 1
          FROM (
              SELECT TOP(2) [inner].[PK1], [inner].[PK2]
              FROM [Test002] AS [inner]
              WHERE [inner].[GroupByKey] = [outer].[GroupByKey]
              ORDER BY [inner].[OrderByKey]
          ) AS [t]
          WHERE ([t].[PK1] = [outer].[PK1]) AND ([t].[PK2] = [outer].[PK2]))
                */
                var case2 = ctx.Test002.Where(outer => ctx.Test002
                                .Where(inner => inner.GroupByKey == outer.GroupByKey)
                                .OrderBy(inner => inner.OrderByKey)
                                .Select(inner => new { inner.PK1, inner.PK2 })//if replace `inner` to `outer` by mistake, System.InvalidOperationException:“variable 'outer' of type 'ConsoleApp15.Test002' referenced from scope '', but it is not defined”
                                .Take(2)
                                .Where(inner => inner.PK1 == outer.PK1 && inner.PK2 == outer.PK2)//try directly equals with anonymous obj when only Take(1)->FirstOrDefault() is needed like case1, but it not works(ClientEvaluation)
                                .Select(inner => 1)//use this to workaround an unknown limited(ClientEvaluation)
                                .Any()//try .Select(inner => true).FirstOrDefault() instead of `.Select(inner => 1).Any()` lead to an SQL error: `WHERE COALESCE((SELECT TOP (1) ...), 0) = 1`, and no `EXISTS` is used
                                )
                                .ToList();

                var case2Lite = ctx.Test002.Where(outer => ctx.Test002
                                .Where(inner => inner.GroupByKey == outer.GroupByKey)
                                .OrderBy(inner => inner.OrderByKey)
                                .Take(2)
                                .Where(inner => inner == outer)
                                .Any())
                                .ToList();

Also write the failed modified case2 in case somebody need it. Seems can't find any active issue for the 3 exceptions.

failed modified case2

1. System.InvalidOperationException:“variable 'outer' of type 'ConsoleApp15.Test002' referenced from scope '', but it is not defined”
   (not sure if this will happen with normal join or cross apply)
   Looks like 2.1 regression: "variable referenced from scope '', but it is not defined" exception in GroupBy query translation #11904, but it's already fixed.

                /*var e = ctx.Test002.Where(outer => ctx.Test002
                                .Where(inner => inner.GroupByKey == outer.GroupByKey)
                                .OrderBy(inner => inner.OrderByKey)
                                .Select(inner => new { outer.PK1, outer.PK2 })//This doesn't work, should be inner here, just hit this exception by mistake
                                .FirstOrDefault() == new { outer.PK1, outer.PK2 })
                                .ToList();*/

2. System.InvalidOperationException:“No coercion operator is defined between types 'System.Int32' and 'System.Nullable`1[System.Boolean]'.”

                ctx.Test001.Where(d1 => ctx.Test001
                    .Select(d2 => new { A = d2.PK })
                    .OrderBy(d2 => d2.A)
                    .Take(2)
                    .Where(d2 => d2.A == 1)
                    .Select(d2 => true)
                    .FirstOrDefault())
                    .ToList();

3. Wrong SQL:

//simple `.Select(inner => true).FirstOrDefault()` works well.
                var f = ctx.Test002.Where(outer => ctx.Test002
                                .Where(inner => inner.GroupByKey == outer.GroupByKey)
                                .OrderBy(inner => inner.OrderByKey)
                                .Select(inner => new { inner.PK1, inner.PK2 })
                                .Take(2)
                                .Where(inner => inner.PK1 == outer.PK1 && inner.PK2 == outer.PK2)
                                //.Select(inner => 1)
                                .Select(inner => true)
                                .FirstOrDefault()
                                )
                                .ToList();

->

      SELECT [outer].[PK1], [outer].[PK2], [outer].[GroupByKey], [outer].[OrderByKey]
      FROM [Test002] AS [outer]
      WHERE COALESCE((
          SELECT TOP(1) [t].[PK1], [t].[PK2]
          FROM (
              SELECT TOP(2) [inner].[PK1], [inner].[PK2]
              FROM [Test002] AS [inner]
              WHERE [inner].[GroupByKey] = [outer].[GroupByKey]
              ORDER BY [inner].[OrderByKey]
          ) AS [t]
          WHERE ([t].[PK1] = [outer].[PK1]) AND ([t].[PK2] = [outer].[PK2])
      ), 0) = 1

ClientEvaluation:

//NOTE: It's really strange that simply add an `.Select(inner => 1)` before `.Any()` make it works
                var c = ctx.Test002.Where(outer => ctx.Test002
                                .Where(inner => inner.GroupByKey == outer.GroupByKey)
                                .OrderBy(inner => inner.OrderByKey)
                                .Select(inner => new { inner.PK1, inner.PK2 })
                                .Take(2)
                                .Where(inner => inner.PK1 == outer.PK1 && inner.PK2 == outer.PK2)
                                //.Select(inner => 1)
                                .Any()
                                )
                                .ToList();

//4. An simpler case of c
               var c1 =  ctx.Test001.Where(d1 => ctx.Test001
                    .Select(d2 => new { PK = d2.PK })
                    .OrderBy(d2 => d2.PK)
                    .Take(2)
                    .Where(d2 => d2.PK == 1)
                    //.Select(d2 => 1)
                    .Any())
                    .ToList();

//It's ok that it just not works, it may get the same result with case2(Take(2)->Take(1))
                var d = ctx.Test002.Where(outer => ctx.Test002
                                .Where(inner => inner.GroupByKey == outer.GroupByKey)
                                .OrderBy(inner => inner.OrderByKey)
                                .Select(inner => new { inner.PK1, inner.PK2 })
                                .FirstOrDefault() == new { outer.PK1, outer.PK2 })
                                .ToList();

dbcontext

    public partial class TestContext : DbContext
    {
        public TestContext()
        {
        }

        public TestContext(DbContextOptions<TestContext> options)
            : base(options)
        {
        }

        public virtual DbSet<Test001> Test001 { get; set; }

        public virtual DbSet<Test002> Test002 { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            if (!optionsBuilder.IsConfigured)
            {
                //optionsBuilder.UseNpgsql("Host=127.0.0.1;Database=test001;Username=postgres;Password=123456");
                optionsBuilder.UseSqlServer("Server=(localdb)\\MSSQLLocalDB;Database=Test_Database001;Trusted_Connection=True;");
                //optionsBuilder.ConfigureWarnings(warnings => warnings.Throw(Microsoft.EntityFrameworkCore.Diagnostics.RelationalEventId.QueryClientEvaluationWarning));
            }
            var logger = LoggerFactory.Create(conf =>
            {
                conf.AddConsole();
            });
            optionsBuilder.UseLoggerFactory(logger);
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Test001>(entity =>
            {
                entity.HasKey(d => d.PK);
            });

            modelBuilder.Entity<Test002>(entity =>
            {
                entity.HasKey(d => new { d.PK1, d.PK2 });
            });
        }
    }

    public partial class Test002
    {
        public int PK1 { get; set; }
        public int PK2 { get; set; }
        public int GroupByKey { get; set; }
        public int OrderByKey { get; set; }
    }

    public partial class Test001
    {
        public int PK { get; set; }
        public int GroupByKey { get; set; }
        public int OrderByKey { get; set; }
    }

[Jonatthu]

I hope this can be implemented on the framework since will make some scenarios possible

[smitpatel]

Also cover linked issues.

[Jonatthu]

@smitpatel and @divega Is this query possible now with ef core 3?

[Jonatthu]

As an example of the query I wanna generate is

SELECT *
FROM (
    SELECT Id, [Key], [Type], ROW_NUMBER() 
    OVER (
        PARTITION BY [Type]
        ORDER BY [Key] ASC
    ) AS RowNum
    FROM [AutthuServerDb].[dbo].[GenPermission]
) a
WHERE a.RowNum <= 2 AND a.[Type] IN (0, 1, 2, 11)

[smitpatel]

@Jonatthu - This issue is still open and in the backlog milestone, which indicates that it is not supported yet. We plan to do some day.

[Jonatthu]

@smitpatel considering this has a year old, I guess will take another year or two, Is there any workaround that EFCore team can provide, even using the function FromQuery but allowing to extend that from query with more linq?

[smitpatel]

Use FromSql with keyless entity type to fetch data from the server and then change shape on the client side to desired shape.

[bdongus]

At least for us, this is a showstopper. EF core 3.0 throws an exception if something is executed in memory and using SQL would bring infrastructure responsibitilies into the business layer.

Please consider the implementation of .First() too. In many cases we just need the first row of every group.

[Jonatthu]

@bdongus I just made the implementation!
By using IMethodCallTranslator but only for SQL Server for now
I need to do the same fo SQLite

(SQLite not sure if i can do this)

[Jonatthu]

https://docs.microsoft.com/en-us/dotnet/api/microsoft.entityframeworkcore.query.sqlexpressions.rownumberexpression?view=efcore-3.0

By using this, helps to implement that interface, and it's only for efcore 3.0

[bhugot]

This still don't work with efcore 5.0.9

[n9]

@bhugot See the milestone.

[bhugot]

@n9 I agree but I see the PR #25495 is included in 5.0.9

[n9]

@bhugot Where do you see this? (https://github.com/dotnet/efcore/releases/tag/v5.0.9)

[bhugot]

forget it i'm not well awake :(

[shoooe]

@ajcvickers How is this supported atm? I can't seem to find examples in the release notes or documentation.

[mguinness]

@shoooe Take a look at Improved GroupBy support.