Improve GroupBy support

[ajcvickers]

This is a grouping of related issues. Feel free to vote (👍) for this issue to indicate that this is an area that you think we should spend time on, but consider also voting for individual issues for things you consider especially important.

---

This is a composite issue grouping together all remaining (as of 7.0) issues still remaining for GroupBy translation.

Bugs

• GroupBy or Distinct, not supported in EF Core 3.1 in ToQuery operations for keyless types #20040
• Microsoft.Data.Sqlite.SqliteConnectionPool.GetConnection() fails with "Inconceivable!" on local test run #26016
• GroupBy debug assert: Missing alias in the list: p1,p #26104
• NorthwindSplitIncludeQuery test depend on specific implicit ordering and are hard to fix #26715
• Operations on IQueryable after GroupBy.Select #26748
• Simplification of Count(*) with multiple group by can generate wrong result #27130
• Group by with DefaultIfEmpty on a sub-query with a left join changes the subquery's joins to inner joins #27480
• The LINQ expression 'ProjectionBindingExpression: 0/1/2' could not be translated. #28125
• Group By subquery when applied on another Query throws InvalidOperation Exception #28258
• GroupBy nullable column followed by First() over group returns null record #29240
• Select(x=>new { a=Sum(NullableField)??null }) generate COALESCE error #29344
• EF Core 7 GroupBy with Owned Entity perf Regression #29593
• Error applying projection #30012
• Combination of GroupBy, FirstOrDefault and Select throws a KeyNotFoundException #30052
• GroupBy over complex key generates redundant subquery #30113
• When mapping a property to a string/json column in a group by, EF materializes inconsistent results #31675
• GroupBy() isn't working as expected unless I first load the individual items into memory #32014
• Improve GroupBy support #30173

Enhancements

• Support database window functions #12747
• Query is Executed Locally when GroupBy is Joined to another Table and Then Sorted #13393
• Cosmos: Translate GroupBy #17313
• Query regression in preview 9: Combination of GroupBy, aggregate and let throws #17673
• Translate GroupBy followed by Distinct without aggregate operators #17761
• Query: Pay your (technical) debt on time #18923
• GroupBy causes exception #20887
• Query: collection join scenario where inner collection is a group by correlated with outer should be detected and blocked #21965
• Navigation expansion improvements phase 2 #23206
• Processing of the LINQ expression 'GroupByShaperExpression #23650
• Narrow the feature gap between EF Core and EF6 #24106
• Enhance InvalidOperationException message fo better cause understaning #24452
• Precompiled queries #25009
• Query: further improve translation of queries with Distinct or Groupby in correlated subquery, when entity identifiers are not easy to obtain #26077
• Query: we can uniquely identify keyless entities that have Distinct operation applied on them #26078
• Query: Property binding on the result entity from grouping.First #26753
• Better translation for COUNT with predicate checking for non-null #26940
• GroupBy on Navigation Property not translating properly #27077
• Test: Group By aggregate additional scenarios #27133
• Translate LINQ DistinctBy #27470
• Lift GroupBy aggregate chain with navigation expansion into aggregate term #27933
• Query Translation: difference between x.Count()==0 and !x.Any() #27953
• GroupBy with SelectMany and Take() - The LINQ expression could not be translated  #28002
• Sum followed by a group by: "Cannot perform an aggregate function on an expression containing an aggregate or a subquery. " #28238
• Query: Bulk Delete on result of First family in projection #28525
• Redundant inner join when I'm using GroupBy() #28622
• subquery not working when i group by date of datetime #28977
• GroupBy JSON Entity #29011
• GroupBy entity subquery #29013
• Expand navigations on grouping key #29014
• GroupBy owned entity #29018
• Investigate single query related entity loading and orderings #29171
• JSON/Query: translate groupby followed by orderby json scalar + FirstOrDefault #29287
• GroupBy possibly causes needless duplication of predicate #29670
• Refrain from doing split query when not necessary #30025