Query JSON array column size throws exception

[xuzhg]

Include your code

I follow up JSON column from post https://devblogs.microsoft.com/dotnet/announcing-ef7-release-candidate-2/ .

And working a sample at: https://github.com/xuzhg/mydotnetconf/tree/JsonColumn/JsonColumn

Now, I have a question related to the JSON Array Column. How can I get the array size? (I don't want to use "ToListAsync" or "ToEnumerableAsync" something like these.

For example:

public class Author
{
     public int Id { get; set; }   
     public IList<Address> Addresses { get; set; }
}

Where: 'Addresses' is saved as a JSON array column on the DB side, (I call ToJson in the DbContext)

So, when I do like:

Context.Authors.Where(a => a.Addresses.Count == 2);   // ==> throw exception (see below)

Of course, I can do as follows (it works), but that's not what I want.

Context.Authors.AsEnumerable().Where(a => a.Addresses.Count == 2);  

Did I do anything wrong? Thanks.

I am using EFCore.SqlServer 7.0.2

<PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.2" />

System.InvalidOperationException: The LINQ expression 'DbSet<Author>()
    .Where(a => EF.Property<IList<Address>>(a, "Addresses")
        .AsQueryable()
        .Count() == 2)' could not be translated. 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.
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0&)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   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.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 JsonColumn.Controllers.AuthorsController.Get() in D:\github\xuzhg\mydotnetconf\JsonColumn\JsonColumn\Controllers\AuthorsController.cs:line 23
   at lambda_method3(Closure, Object, Object[])
   at Microsoft.AspNetCore.Mvc.Infrastructure.ActionMethodExecutor.SyncActionResultExecutor.Execute(ActionContext actionContext, IActionResultTypeMapper mapper, ObjectMethodExecutor executor, Object controller, Object[] arguments)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeActionMethodAsync>g__Logged|12_1(ControllerActionInvoker invoker)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.<InvokeNextActionFilterAsync>g__Awaited|10_0(ControllerActionInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Rethrow(ActionExecutedContextSealed context)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted)
   at Microsoft.AspNetCore.Mvc.Infrastructure.ControllerActionInvoker.InvokeInnerFilterAsync()

[maumar]

currently querying capabilities are not supported - you can only project the JSON object or it's part. All querying must be done on the client. We track enabling this scenario here: https://github.com/dotnet/efcore/issues/28616