Skip to content
New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Query JSON array column size throws exception #30132

Closed
xuzhg opened this issue Jan 24, 2023 · 1 comment
Closed

Query JSON array column size throws exception #30132

xuzhg opened this issue Jan 24, 2023 · 1 comment

Comments

@xuzhg
Copy link

xuzhg commented Jan 24, 2023

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
Copy link
Contributor

maumar commented Jan 25, 2023

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: #28616

@ajcvickers ajcvickers closed this as not planned Won't fix, can't repro, duplicate, stale Jan 28, 2023
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment
Projects
None yet
Development

No branches or pull requests

3 participants