Operations on IQueryable after GroupBy.Select

[OpenSpacesAndPlaces]

Where "MyField" and "MyOtherField" are non-nullable integers.

I'm able to create distinct groupings no problem like:
GroupBy(p=> new { MyField = p.MyField, MyOtherField = p.MyOtherField}).Select(p=> p.First())

I'm also able to preorder the data:
OrderBy(p=> p.MyField).GroupBy(p=> new { MyField = p.MyField, MyOtherField = p.MyOtherField}).Select(p=> p.First())

---

But if I try to Count:
GroupBy(p=> new { MyField = p.MyField, MyOtherField = p.MyOtherField}).Select(p=> p.First()).Count()

Or shape the return data:
GroupBy(p=> new { MyField = p.MyField, MyOtherField = p.MyOtherField}).Select(p=> p.First()).Select(p=>p.MyField)

post Order the data:
GroupBy(p=> new { MyField = p.MyField, MyOtherField = p.MyOtherField}).Select(p=> p.First()).OrderBy(p=> p.MyField)

I get an error like:

System.InvalidOperationException: Nullable object must have a value.
   at System.Nullable`1.get_Value()
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ClientProjectionRemappingExpressionVisitor.Visit(Expression expression)
   at System.Linq.Expressions.ExpressionVisitor.VisitUnary(UnaryExpression node)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ClientProjectionRemappingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.SqlExpressions.SelectExpression.ApplyProjection(Expression shaperExpression, ResultCardinality resultCardinality, QuerySplittingBehavior querySplittingBehavior)
   at Microsoft.EntityFrameworkCore.Query.Internal.SelectExpressionProjectionApplyingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryTranslationPostprocessor.Process(Expression query)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass12_0`1.<ExecuteAsync>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.ExecuteAsync[TResult](Expression query, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.ExecuteAsync[TResult](Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, Expression expression, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ExecuteAsync[TSource,TResult](MethodInfo operatorMethodInfo, IQueryable`1 source, CancellationToken cancellationToken)
   at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.CountAsync[TSource](IQueryable`1 source, CancellationToken cancellationToken)

I've seen the error come up in other threads related to Navigation properties, but for the test case, the table has none.

If I avoid the avoid anonymous types (e.g. single column) - everything works fine for something like Count:
GroupBy(p=> p.MyField).Select(p=> p.First()).Count()

Any help appreciated!

---

EF Core 6
Microsoft.EntityFrameworkCore.SqlServer
.NET 6.0
Win 11
VS 22

[smitpatel]

Not a regression since grouping.First didn't work in previous release.
Queries with OrderBy/Select after grouping+Select throws client eval error for me since we couldn't translate p=> p.MyField Filed #26753

Count is failing with exception like above.
Work-around would be do Count after grouping directly as the result will be the same.

[smitpatel]

Same issue exist for Any. (kind for All also) Any ends up working because the stale projection isn't getting expanded where above error occurs.
None of this works if it contains a predicate (which All always has) it will run into #26753

[OpenSpacesAndPlaces]

@ajcvickers Any chance this could make it into the next minor release or is a big push needed to fix this?

[ajcvickers]

@OpenSpacesAndPlaces If by minor release you mean a 6.x minor release, then current plans are for no such releases before 7.0. If you mean a 6.0.x patch release, then we don't typically add new functionality in patch releases.

[OpenSpacesAndPlaces]

@ajcvickers Ok - I didn't realize there were no minors this cycle - I was reacting to the next major being 11/2022.

Well then, yeah, it's probably a stretch to say this would be in a patch only cycle since the features section doesn't talk about anonymous support.
https://docs.microsoft.com/en-us/ef/core/what-is-new/ef-core-6.0/whatsnew#improved-groupby-support

Thanks for the dialog on this!

[SoftCircuits]

I can work around the same error in the following code:

var query = DbContext.TripSegments
    .Where(ts => ts.RailcarTrip.WaybillRailcar.Waybill.CompanyCode == companyCode &&
        ts.IsLoaded && ts.EndDate == null &&
        ts.DestinationCity == city && ts.DestinationState == state);

query = query
    .GroupBy(ts => ts.RailcarTrip.WaybillRailcar.RailcarNumber)
    .Select(x => x.OrderByDescending(ts => ts.RailcarTrip.StartDate).First());

int count = await query.CountAsync();

By using Count() directly (as suggested above).

int count = await query.GroupBy(ts => ts.RailcarTrip.WaybillRailcar.RailcarNumber).CountAsync();

But is there a workaround that allows me to add a final SELECT clause?

var query = DbContext.TripSegments
    .Where(ts => ts.RailcarTrip.WaybillRailcar.Waybill.CompanyCode == companyCode &&
        ts.IsLoaded && ts.EndDate == null &&
        ts.DestinationCity == city && ts.DestinationState == state);

query = query
    .GroupBy(ts => ts.RailcarTrip.WaybillRailcar.RailcarNumber)
    .Select(x => x.First());

var results = query.Select(ts => new MyType()
{
    // ...
});

The entire point of the GroupBy() is to remove the older of any duplicate RailcarNumbers. I don't think there's another way to do this.

[OpenSpacesAndPlaces]

If you can skip the projection into a new type at the end it should work. At least in our travels, projecting or layering too much over the group by will cause this. Other thing I will note, unless you're paging results, there isn't going to be a huge difference for client siding most things unless your column and/or row count is massive.

[SoftCircuits]

If you can skip the projection into a new type at the end it should work. At least in our travels, projecting or layering too much over the group by will cause this.

Thanks, that didn't seem to be an option for me because the SELECT clause uses a lot of navigation properties to get the data I need.

However, I appear to have it working by actually doing the projection before the GROUPBY. And then grouping the results of that. To be honest, I was a little surprised this worked.

Other thing I will note, unless you're paging results, there isn't going to be a huge difference for client siding most things unless your column and/or row count is massive.

Yes, especially in my case as there were very few duplicates. But removing anything on the client side would definitely mess up my paging.

[vlad-bil]

Other example (please see fiddle https://dotnetfiddle.net/zBSsa1)

Pre condition:
entities are grouped by some field by GroupBy(...), then first item is taken from each group via Select(x => x.FirstOrDefault(...)).
The query itself may be compiled, but if we try to call Count it is failed

[wilari932]

My solution was to do it like this
var result = await someQuery.GroupBy(p => p.Id).Select(x => x.Key).CountAsync();
it seems that is bad support for this specially when you are left joining data

[pvg8v6g]

I can confirm this is STILL an issue in .NET 6.

Well nvm I just saw the "punted-for-7.0" label

[neobenedict]

Please for the love of C# fix this already.

[dophuquan1998]

I get the error after executing the query : "cannot be translated .OrderByDescending after Group "

[fl1k]

I get the error after executing the query : "cannot be translated .OrderByDescending after Group "

Same issue, Can't run .Select before .GroupBy

[Kebechet]

Doesn't work in .NET 8 as well. This is a very basic use case, please consider prioritizing it more.

[NiSHoW]

consider giving priority to all "ef-parity"...

[DushyantSinghChouhan]

Still getting this issue in .Net 8.0, with ef core 8.0.8. We are using KendoUI, and when groupable is applied on KEndo UI, its not working

System.InvalidOperationException: The LINQ expression 'DbSet()
.SelectMany(
collectionSelector: x => DbSetTableB()
.Where(y => (int?)y.Id == x.MatchId)
.DefaultIfEmpty(),
resultSelector: (a, b) => new {
entity = a,
vendor = b
})
.Select(x => new QueueDataModel{
QueueId = x.entity.Id,
QueueType = x.entity.Type,
Name = x.entity.Name,
Status = x.entity.Status,
TINLast4 = ContextExtensions.JsonValue(
expression: x.entity.EntityJson,
path: "$.TINLast4"),
MetaData = ContextExtensions.JsonQuery(
expression: x.entity.EntityJson,
path: "$.MetaData"),
PersonFlag = ContextExtensions.JsonValue(
expression: x.entity.EntityJson,
path: "$.PersonFlag"),
ClientContact = ContextExtensions.JsonQuery(
expression: x.entity.EntityJson,
path: "$.Contact"),
ClientLocation = ContextExtensions.JsonQuery(
expression: x.entity.EntityJson,
path: "$.Location"),
SelectedVendor = x.vendor != null ? new MatchedVendor{
Id = x.vendor.Id,
Name = x.vendor.Name
}
: null,
MatchesCount = x.entity.Matches
.AsQueryable()
.Join(
inner: DbSet(),
outerKeySelector: match => match.Id,
innerKeySelector: matchResult => matchResult.MatchId,
resultSelector: (match, matchResult) => matchResult.MatchId)
.Distinct()
.Count(),
MatchedVendors = x.entity.Matches
.AsQueryable()
.Join(
inner: DbSet(),
outerKeySelector: match => match.Id,
innerKeySelector: matchResult => matchResult.MatchId,
resultSelector: (match, matchResult) => new {
matchResult = matchResult,
MatchAlgorithmTypeEnum = match.MatchAlgorithmTypeEnum
})
.Join(
inner: DbSetTableB(),
outerKeySelector: result => result.matchResult.MatchId,
innerKeySelector: vendor => vendor.Id,
resultSelector: (result, vendor) => new MatchedVendor{
Id = vendor.Id,
Confidence = result.matchResult.Confidence,
Name = vendor.Name,
AlgorithmTypeEnum = result.MatchTypeEnum
}
)
.ToList(),
Client = x.entity.JobMatch.Client.Name,
Job = x.entity.JobMatch.Name,
JobId = x.entity.JobMatchId,
DateAdded = x.entity.CreatedUTC
}
)
.Where(item => item.Status == Review)
.OrderBy(item => item.Name)
.Skip(__p_0)
.Take(__p_1)
.GroupBy(item => item.Name)
.OrderBy(group51285273 => group51285273.Key)
.Select(group51285273 => new AggregateFunctionsGroup{
Key = group51285273.Key,
ItemCount = group51285273
.AsQueryable()
.Count(),
HasSubgroups = False,
Member = "Name",
Items = group51285273
}
)' could not be translated. Additional information: Translation of 'Select' which contains grouping parameter without composition is not supported. Either rewrite the query in a form that can be translated, or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToListAsync'. See https://go.microsoft.com/fwlink/?linkid=2101038 for more information.

[roji]

Everyone, I hope we'll do a general push for improved GroupBy support for EF 10 - stay tuned.