Support ability to select top N of each group
Jonatthu opened this issue · 45 comments
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
@divega to follow up.
@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.
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.
@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);
Any where before GroupBy would be server evaluated if they are translatable.
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
- 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 #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();*/- 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();- 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) = 1ClientEvaluation:
//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; }
}I hope this can be implemented on the framework since will make some scenarios possible
Also cover linked issues.
@smitpatel and @divega Is this query possible now with ef core 3?
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)
@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.
@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?
Use FromSql with keyless entity type to fetch data from the server and then change shape on the client side to desired shape.
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.
@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)
By using this, helps to implement that interface, and it's only for efcore 3.0
This is pretty much a mandatory feature that is missing, as right now there is no good way of getting the newest records for a group based on a list of ids. My use case is getting the newest chat message for an arbitrary number of chats. Please implement.
What I want to do:
Fetch the latest chat message for a list of chat ids.
This is not supported:
var lastChatMessagePerChat = await this.chatContext.ChatMessages.AsNoTracking()
.Where(t => chatRoomIds.Contains(t.ChatRoomId) && !t.DeletedAtUtc.HasValue)
.GroupBy(t => t.ChatRoomId)
.Select(g => g.OrderByDescending(t => t.CreatedAtUtc).First())
.ToListAsync();Thanks.
Will this feature be implemented?
My use case is:
await _db.RetailerRates
.GroupBy(r => Currency)
.Select(g => g.OrderByDescending(r => r.CreatedAt).First())
.ToListAsync();
I too would require such feature
Pretty please, it's a little bit deflating having to design your database around limitations in the ORM. I think this is a must-have feature and would be thrilled if it would be implemented soon.
Thanks!
Add my name to the list. Migrating from EF6, this is causing us to implement crazy workarounds. Selecting TOP N from each group seems a very common scenario:
Last article in each category
Last message in each chat
Last edit for each document
etc etc. This is not an edge case, it's missing fundamental functionality.
This query pattern shows up quite a bit in my under-way 1000 table LINQ to SQL migration project.
Example use case: You have a list of blogs. For each blog you want to display a single highlighted post (the most upvoted or most recent one). So you might group by blog and take the first item from each group.
This can be translated with ROW_NUMBER ... WHERE r = 1 or with CROSS APPLY (SELECT TOP 1 ...). Both variants have different query plan shapes and performance characteristics. It would be nice if the shape could be selected by writing the LINQ query in a particular way.
This query could result in the ROW_NUMBER shape:
ctx.BlogPosts
.GroupBy(bp => bp.Blog)
.Select(g => g.OrderByDescending(bp => bp.CreatedAt).FirstOrDefault())
.ToList();
This query could result in the CROSS APPLY shape:
ctx.BlogPosts
.GroupBy(bp => bp.Blog)
.SelectMany(g => g.OrderByDescending(bp => bp.CreatedAt).Take(1).DefaultIfEmpty())
.ToList();
But any way to support this would be better than no way so that the migration can proceed.
The ROW_NUMBER plan leads SQL Server to sort all rows which could be billions. The CROSS APPLY lends itself to a loop join plan that can query an index very selectively. ROW_NUMBER is great if the fraction of rows selected is high. CROSS APPLY is great when picking just a few items.
Depending on the scenario the performance difference can be significant.
One more to support that implementation! upvote!
my use case:
_context.Services
.OrderByDescending(o => o.CreatedAt)
.Where(o => equipments
.Select(eq => eq.ID)
.Contains(o.Equipment.ID)
)
.GroupBy(o => o.Equipment)
.Select(o => o.FirstOrDefault());
@ajcvickers @divega @smitpatel Hi guys! I really like Entity Framework but this is driving me nuts. Selecting the top message, blog, whatever is such a common scenario that I constantly run into this, and so do a lot of other people as well. May I inquire when you think this could be implemented?
Thanks!
We have also run into this problem when trying to select the "latest" version of a stylesheet.
Each stylesheet has a unique id, but its the name that defines the stylesheet. If a stylesheet is updated, a new version is created.
e.g
| StylesheetVersionID | StylesheetName | Version |
|---|---|---|
| 1 | Upload.xslt | 1 |
| 2 | Upload.xlst | 2 |
How we would attempt to write this in EF Core is something like this using the overload of group by which cannot be translated.
StyleSheetVersions
.GroupBy(s => s.StylesheetName, (key,g) => g.OrderByDescending(x => x.Version).FirstOrDefault())
.Dump()for the moment as a workaround, we are using the FromSqlRaw method and manually writing the sql.
SELECT
StyleSheets.StylesheetVersionID
, StyleSheets.StylesheetName
, StyleSheets.Version
, StyleSheets.UserId
, StyleSheets.DateModified
, StyleSheets.XSLT
FROM
(
SELECT
StylesheetVersionID
, StylesheetName
, Version
, UserId
, DateModified
, XSLT
, ROW_NUMBER() OVER(PARTITION BY StylesheetName ORDER BY Version DESC) AS RowNum
FROM [dbo].[StyleSheetVersions]
) StyleSheets
WHERE StyleSheets.RowNum = 1It would be nice if this was available using the query syntax 👍
I was able to make this using my own EF.Function
EF.Functions.RowNumber(x.ClientId, EF.Functions.OrderByDescending(x.Id))
It is generating perfect queries similar to yours @tjackadams
I was able to make this using my own EF.Function
EF.Functions.RowNumber(x.ClientId, EF.Functions.OrderByDescending(x.Id))It is generating perfect queries similar to yours @tjackadams
Hi! I haven't implemented any custom EF functions, so is there any chance you could post the code here? Thanks.
it literally took me all this code so posting it here would not be easy.
@Jonatthu Oh wow, that's like an entire project in itself! If you could make that into a NuGet I'd be forever thankful :)
@ajcvickers Is this better as a pull request or as a nuget package? I think this should be part of EF
@divega
EF Core already has RowNumberExpression support. Identifying Grouping.Take(n) is not an easy task and requires handling it in various different parts of the pipeline. We don't think it is something suitable for new contributors to query pipeline. If you think you can handle complexity then feel free to submit a design.
This issue does not track adding any LINQ method like EF.Functions.RowNumber, if you feel that EF Core should add it then please file a new issue for feature with API design and use-case in mind.
work around:
var sub = _db.HourlyData.OrderByDescending(a => a.GoldCoin);
var q = _db.HourlyData
.Select(a => a.RoomId)
.Distinct()
.SelectMany(a => sub.Where(b => b.RoomId == a).Take(1), (a, b) => b);instead of
var q = _db.HourlyData
.GroupBy(a => a.RoomId)
.SelectMany(a => a.OrderByDescending(a => a.GoldCoin).Take(1));@darkflame0 this is the SQL generated
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (6ms) [Parameters=[@__p_1='?' (DbType = Int32)], CommandType='Text', CommandTimeout='30']
SELECT [t0].[Id], [t0].[CaptionId], [t0].[ClientKeyId], [t0].[CustomCaption], [t0].[Position], [t0].[ProfileId], [t0].[Sub], [t0].[Type], [t0].[Uri]
FROM (
SELECT DISTINCT [p].[ProfileId]
FROM [ProfileMedia] AS [p]
WHERE [p].[ProfileId] IN (8)
) AS [t]
CROSS JOIN (
SELECT TOP(@__p_1) [p0].[Id], [p0].[CaptionId], [p0].[ClientKeyId], [p0].[CustomCaption], [p0].[Position], [p0].[ProfileId], [p0].[Sub], [p0].[Type], [p0].[Uri]
FROM [ProfileMedia] AS [p0]
ORDER BY [p0].[Position]
) AS [t0]
When this may work or behave similar I am not sure the performance of this kind of query vs a row_number query
This is the one query we want to be generated.
Which I have achieved but I have to use EF.Functions extensions, I rather would like to have a LINQ expression solution.
Microsoft.EntityFrameworkCore.Database.Command: Information: Executed DbCommand (213ms) [Parameters=[@__p_2='?' (DbType = Int64), @__p_3='?' (DbType = Int64)], CommandType='Text', CommandTimeout='30']
SELECT [t].[Id], [t].[CaptionId], [t].[ClientKeyId], [t].[CustomCaption], [t].[Position], [t].[ProfileId], [t].[Sub], [t].[Type], [t].[Uri]
FROM (
SELECT [p].[Id], [p].[CaptionId], [p].[ClientKeyId], [p].[CustomCaption], [p].[Position], [p].[ProfileId], [p].[Sub], [p].[Type], [p].[Uri], ROW_NUMBER() OVER(PARTITION BY [p].[ProfileId] ORDER BY [p].[Id] DESC) AS [c]
FROM [ProfileMedia] AS [p]
WHERE [p].[ProfileId] IN (8, 32, 36, 37, 38, 39, 40, 41, 43, 44)
) AS [t]
WHERE ([t].[c] > @__p_2) AND ([t].[c] <= @__p_3)
Will this ever work?
Please. The lack of this feature is extremely limiting, and it's such a common use case that I would assume it to be supported.
Thank you for considering!
@darkflame0 thanks for the example, based on that, he's a workaround if you need a first/first or default related query
Original:
var nodeId = new Guid("dabf23c8-2904-4f83-840c-d468c733998c");
var language = 1043;
ArticleProperties
.Where(ap => ap.ClassificationNodeId == nodeId && ap.LanguageId == language)
.GroupBy(ap => ap.HeaderId)
.Select(ap => new
{
Name = ap.First().HeaderName,
HeaderId = ap.Key
})
.ToList()
.Dump();Workaround:
ArticleProperties
.Where(ap => ap.ClassificationNodeId == nodeId && ap.LanguageId == language)
.GroupBy(ap => ap.HeaderId)
.Select(ap => new
{
Name = ArticleProperties
.First(s => s.ClassificationNodeId == nodeId && s.LanguageId == language && s.HeaderId == ap.Key)
.HeaderName,
HeaderId = ap.Key
})
.ToList()
.Dump();I use LINQPad to test these queries, and it seems to have some kind of workaround inbuilt, where both versions work. If I look at the generated SQL, it looks like it's near identical between the original and workaround versions, but it declares the query parameters twice, as it sees no relation between the .First query of the sub-query's values and the .Where query on the parent query's values.
Looks like these queries would have the same performance. Although I can't confirm that this is exactly how EF6 does it, but I can't imagine it's much different.
Original
-- Region Parameters
DECLARE @p0 UniqueIdentifier = 'dabf23c8-2904-4f83-840c-d468c733998c'
DECLARE @p1 Int = 1043
-- EndRegion
SELECT (
SELECT [t3].[HeaderName]
FROM (
SELECT TOP (1) [t2].[HeaderName]
FROM [ArticleProperties] AS [t2]
WHERE ([t1].[HeaderId] = [t2].[HeaderId]) AND ([t2].[ClassificationNodeId] = @p0) AND ([t2].[LanguageId] = @p1)
) AS [t3]
) AS [Name], [t1].[HeaderId]
FROM (
SELECT [t0].[HeaderId]
FROM [ArticleProperties] AS [t0]
WHERE ([t0].[ClassificationNodeId] = @p0) AND ([t0].[LanguageId] = @p1)
GROUP BY [t0].[HeaderId]
) AS [t1]Workaround
-- Region Parameters
DECLARE @p0 UniqueIdentifier = 'dabf23c8-2904-4f83-840c-d468c733998c'
DECLARE @p1 Int = 1043
DECLARE @p2 UniqueIdentifier = 'dabf23c8-2904-4f83-840c-d468c733998c'
DECLARE @p3 Int = 1043
-- EndRegion
SELECT (
SELECT [t3].[HeaderName]
FROM (
SELECT TOP (1) [t2].[HeaderName]
FROM [ArticleProperties] AS [t2]
WHERE ([t2].[ClassificationNodeId] = @p2) AND ([t2].[LanguageId] = @p3) AND ([t2].[HeaderId] = [t1].[HeaderId])
) AS [t3]
) AS [Name], [t1].[HeaderId]
FROM (
SELECT [t0].[HeaderId]
FROM [ArticleProperties] AS [t0]
WHERE ([t0].[ClassificationNodeId] = @p0) AND ([t0].[LanguageId] = @p1)
GROUP BY [t0].[HeaderId]
) AS [t1]This still don't work with efcore 5.0.9
@bhugot Where do you see this? (https://github.com/dotnet/efcore/releases/tag/v5.0.9)
forget it i'm not well awake :(
@ajcvickers How is this supported atm? I can't seem to find examples in the release notes or documentation.
@shoooe Take a look at Improved GroupBy support.