Group by JsonDocument's property with unknown type using Entity Framework Core

Question

I'm using Entity Framework Core 3.1.5

I’m trying to group entities by some property of JsonDocument, which I don’t know its type. AdditionalData is the JsonDocument property of the entities.

I need to execute the commands on the database.

I managed to do it with two restrictions

1. I specified the property’s type (with GetInt32)
2. The commands were executed on the client and not on the DB.

    IEnumerable<MetricEntity> metricEntities = _tenantDbContext.Set<MetricEntity>();
    var ii2 = metricEntities.Where(metric => metric.Name == metricName).GroupBy(e =>
        e.AdditionalData!.RootElement.GetProperty(groupByProperty).GetInt32())
    .Select(entities => new
    {
        Count = entities.Count(), MetType = entities.Key, Ent = entities,
    }).ToList();

I also managed to do it without getting all the entities in the result and with the restriction that the commands were executed on the client and not on the DB.

var i2 = _tenantDbContext.Set<MetricEntity>()
.Where(metric => metric.Name == metricName)
.GroupBy(e =>
    e.AdditionalData!.RootElement.GetProperty(groupByProperty).GetString())
.Select(entities => new
{
    Count = entities.Count(), MetType = entities.Key
}).ToList();

But when I’m adding the entities to the result as follow:

var i2 = _tenantDbContext.Set<MetricEntity>()
.GroupBy(e =>
    e.AdditionalData!.RootElement.GetProperty(groupByProperty).GetString())
.Select(entities => new
{
    Count = entities.Count(), MetType = entities.Key, ent = entities,
}).ToList();

I’m getting the following exception:

System.InvalidOperationException: Processing of the LINQ expression '(GroupByShaperExpression:
KeySelector: ((m.AdditionalData)#>>{(@__groupByProperty_1)}),
ElementSelector:(EntityShaperExpression:
    EntityType: MetricEntity
    ValueBufferExpression:
        (ProjectionBindingExpression: EmptyProjectionMember)
    IsNullable: False
)
)' by 'RelationalProjectionBindingExpressionVisitor' failed. This may indicate either a bug or a limitation in EF Core. See https://go.microsoft.com/fwlink/?linkid=2101433 for more detailed information.
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitExtension(Expression extensionExpression)
   at System.Linq.Expressions.Expression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.VisitNew(NewExpression newExpression)
   at System.Linq.Expressions.NewExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Visit(Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.RelationalProjectionBindingExpressionVisitor.Translate(SelectExpression selectExpression, Expression expression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateSelect(ShapedQueryExpression source, LambdaExpression selector)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryCompilationContext.CreateQueryExecutor[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Storage.Database.CompileQuery[TResult](Expression query, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.CompileQueryCore[TResult](IDatabase database, Expression query, IModel model, Boolean async)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.<>c__DisplayClass9_0`1.<Execute>b__0()
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQueryCore[TFunc](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.CompiledQueryCache.GetOrAddQuery[TResult](Object cacheKey, Func`1 compiler)
   at Microsoft.EntityFrameworkCore.Query.Internal.QueryCompiler.Execute[TResult](Expression query)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryProvider.Execute[TResult](Expression expression)
   at Microsoft.EntityFrameworkCore.Query.Internal.EntityQueryable`1.GetEnumerator()
   at System.Collections.Generic.List`1..ctor(IEnumerable`1 collection)
   at System.Linq.Enumerable.ToList[TSource](IEnumerable`1 source)

Answer 1

Each group by query should contain one or more keys and other properties which have aggregating function only in the select statement (Sum, Count and etc). EF tryed to convert LINQ to SQL query and throws the exception due no internal checks.

There is a workaround:

Evaluate table data before GroupBy. The following code loads all MetricEntities and groups it in c# code:

var i2 = _tenantDbContext.MetricEntities
.AsEnumerable()
.GroupBy(e =>
    e.AdditionalData!.RootElement.GetProperty(groupByProperty).GetString())
.Select(entities => new
{
    Count = entities.Count(), MetType = entities.Key, ent = entities,
}).ToList();