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