oData 8.0.4 with .NET6 contains could not be translated

[VictorQualitas]

after update from odata 8.0.3 .NET5 to 8.0.4 .NET6, query with contains return error

https://localhost:5001/service/Vagas?$filter=VagaCampos/PalavrasChave/any(palavraschave:contains(palavraschave, 'operador'))

error:
fail: Microsoft.AspNetCore.Server.Kestrel[13] Connection id "0HMDBCEVFSRT6", Request id "0HMDBCEVFSRT6:00000002": An unhandled exception was thrown by the application. System.InvalidOperationException: The LINQ expression 'palavraschave => palavraschave.Contains(__TypedProperty_0)' 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.RelationalSqlTranslatingExpressionVisitor.VisitLambda[T](Expression 1 lambdaExpression) at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at Pomelo.EntityFrameworkCore.MySql.Query.ExpressionVisitors.Internal.MySqlSqlTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.TranslateInternal(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalSqlTranslatingExpressionVisitor.Translate(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateExpression(Expression expression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateLambdaExpression(ShapedQueryExpression shapedQueryExpression, LambdaExpression lambdaExpression) at Microsoft.EntityFrameworkCore.Query.RelationalQueryableMethodTranslatingExpressionVisitor.TranslateWhere(ShapedQueryExpression source, LambdaExpression predicate) at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression) at System.Linq.Expressions.MethodCallExpression.Accept(ExpressionVisitor visitor) 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.System.Collections.IEnumerable.GetEnumerator() at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteResourceSetAsync(IEnumerable enumerable, IEdmTypeReference resourceSetType, ODataWriter writer, ODataSerializerContext writeContext) at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectInlineAsync(Object graph, IEdmTypeReference expectedType, ODataWriter writer, ODataSerializerContext writeContext) at Microsoft.AspNetCore.OData.Formatter.Serialization.ODataResourceSetSerializer.WriteObjectAsync(Object graph, Type type, ODataMessageWriter messageWriter, ODataSerializerContext writeContext) at Microsoft.AspNetCore.OData.Formatter.ODataOutputFormatterHelper.WriteToStreamAsync(Type type, Object value, IEdmModel model, ODataVersion version, Uri baseAddress, MediaTypeHeaderValue contentType, HttpRequest request, IHeaderDictionary requestHeaders, IODataSerializerProvider serializerProvider) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResultFilterAsync>g__Awaited|30_0[TFilter,TFilterAsync](ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResultExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.ResultNext[TFilter,TFilterAsync](State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeResultFilters() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeNextResourceFilter>g__Awaited|25_0(ResourceInvoker invoker, Task lastTask, State next, Scope scope, Object state, Boolean isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Rethrow(ResourceExecutedContextSealed context) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.Next(State& next, Scope& scope, Object& state, Boolean& isCompleted) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.InvokeFilterPipelineAsync() --- End of stack trace from previous location --- at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Mvc.Infrastructure.ResourceInvoker.<InvokeAsync>g__Logged|17_1(ResourceInvoker invoker) at Microsoft.AspNetCore.Routing.EndpointMiddleware.<Invoke>g__AwaitRequestTask|6_0(Endpoint endpoint, Task requestTask, ILogger logger) at Microsoft.AspNetCore.Authorization.AuthorizationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Authentication.AuthenticationMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.OData.Batch.ODataBatchMiddleware.Invoke(HttpContext context) at Swashbuckle.AspNetCore.SwaggerUI.SwaggerUIMiddleware.Invoke(HttpContext httpContext) at Swashbuckle.AspNetCore.Swagger.SwaggerMiddleware.Invoke(HttpContext httpContext, ISwaggerProvider swaggerProvider) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Diagnostics.DeveloperExceptionPageMiddleware.Invoke(HttpContext context) at Microsoft.AspNetCore.Watch.BrowserRefresh.BrowserRefreshMiddleware.InvokeAsync(HttpContext context) at Microsoft.AspNetCore.Server.Kestrel.Core.Internal.Http.HttpProtocol.ProcessRequests[TContext](IHttpApplication 1 application)

[xuzhg]

@VictorQualitas Does it work with:

1. 8.0.4 and .NET5 or
2. 8.0.3 and .NET6

[VictorQualitas]

work in 8.0.4 and .NET5

[xuzhg]

@VictorQualitas Do you mind sharing your repo?

[VictorQualitas]

after an investigation I found that it has something to do with using JSON fields with lists/arrays

i made a simple model, like

public class TesteJson {
   public List<string> innerFields { get; set; }
}

public partial class Teste{
   public TesteJson field {get; set;}
}

   entity.Property(e => e.field )
   .HasColumnType("json")
   .HasColumnName("field");

opt.UseMySql(connection, ServerVersion.AutoDetect(connection), b=> b.UseMicrosoftJson())

[smitpatel]

cc: @lauxjpn

[lauxjpn]

@VictorQualitas Since you have not shared your LINQ query with us, I can't say for sure what is going in your code, but judging from the exception, it is possible that you are trying to do something like .Where(e => e.JsonField.SomeList.Contains("foo")), which is not going to work (neither in Pomelo 5.0, nor in Pomelo 6.0).

What you want to do instead, is to use EF.Functions.JsonContains().

Here is a short sample:

Program.cs

using System.Collections.Generic;
using System.Diagnostics;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

namespace IssueConsoleTemplate
{
    /// <summary>
    /// EF Core entity.
    /// </summary>
    public class IceCream
    {
        public int IceCreamId { get; set; }
        public string Name { get; set; }
        public IceCreamDetails Details { get; set; }
    }

    /// <summary>
    /// JSON object.
    /// </summary>
    public class IceCreamDetails
    {
        public int Kilojoule { get; set; }
        public List<string> FoodAdditives { get; set; }
    }

    public class Context : DbContext
    {
        public DbSet<IceCream> IceCreams { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            var connectionString = "server=127.0.0.1;port=3306;user=root;password=;database=AspNetCoreODataIssue386";
            var serverVersion = ServerVersion.AutoDetect(connectionString);

            optionsBuilder
                .UseMySql(connectionString, serverVersion, options => options.UseMicrosoftJson())
                .UseLoggerFactory(
                    LoggerFactory.Create(
                        b => b
                            .AddConsole()
                            .AddFilter(level => level >= LogLevel.Information)))
                .EnableSensitiveDataLogging()
                .EnableDetailedErrors();
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<IceCream>(
                entity =>
                {
                    entity.Property(e => e.Details)
                        .HasColumnType("json");

                    entity.HasData(
                        new IceCream
                        {
                            IceCreamId = 1,
                            Name = "Vanilla",
                            Details = new IceCreamDetails
                            {
                                Kilojoule = 866,
                                FoodAdditives = new List<string> { "E102" },
                            }
                        },
                        new IceCream
                        {
                            IceCreamId = 2,
                            Name = "Chocolate",
                            Details = new IceCreamDetails
                            {
                                Kilojoule = 904,
                                FoodAdditives = new List<string> { "E124", "E155" },
                            }
                        });
                });
        }
    }

    internal static class Program
    {
        private static void Main()
        {
            using var context = new Context();

            context.Database.EnsureDeleted();
            context.Database.EnsureCreated();
            
            var redFoodColoringENumber = "E124";
            var iceCreamsWithE124 = context.IceCreams
                .Where(i => EF.Functions.JsonContains(i.Details.FoodAdditives, EF.Functions.JsonQuote(redFoodColoringENumber), "$"))
                .ToList();
            
            Trace.Assert(iceCreamsWithE124.Count == 1);
            Trace.Assert(iceCreamsWithE124[0].Name == "Chocolate");
        }
    }
}

Output (SQL)

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (6ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      CREATE DATABASE `AspNetCoreODataIssue386`;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      ALTER DATABASE CHARACTER SET utf8mb4;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (55ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      CREATE TABLE `IceCreams` (
          `IceCreamId` int NOT NULL AUTO_INCREMENT,
          `Name` longtext CHARACTER SET utf8mb4 NULL,
          `Details` json NULL,
          CONSTRAINT `PK_IceCreams` PRIMARY KEY (`IceCreamId`)
      ) CHARACTER SET utf8mb4;

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (4ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      INSERT INTO `IceCreams` (`IceCreamId`, `Details`, `Name`)
      VALUES (1, '{"Kilojoule":866,"FoodAdditives":["E102"]}', 'Vanilla');

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']

      INSERT INTO `IceCreams` (`IceCreamId`, `Details`, `Name`)
      VALUES (2, '{"Kilojoule":904,"FoodAdditives":["E124","E155"]}', 'Chocolate');

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (11ms) [Parameters=[@__redFoodColoringENumber_1='E124' (Size = 4000)], CommandType='Text', CommandTimeout='30']

      SELECT `i`.`IceCreamId`, `i`.`Details`, `i`.`Name`
      FROM `IceCreams` AS `i`
      WHERE JSON_CONTAINS(JSON_EXTRACT(`i`.`Details`, '$.FoodAdditives'), JSON_QUOTE(@__redFoodColoringENumber_1), '$')

More sample queries and usage scenarios can be found in PomeloFoundation/Pomelo.EntityFrameworkCore.MySql#14 (comment).

[VictorQualitas]

But i'm using Odata end point

?$filter=JsonField/JsonInner/any(x:contains(x, 'test'))

in this case OData middleware need to identify a json field and use EF.Functions.JsonContains() ?

[lauxjpn]

in this case OData middleware need to identify a json field and use EF.Functions.JsonContains() ?

@VictorQualitas In theory, yes. In practice, that is unlikely to happen, because EF.Functions.JsonContains() is a proprietary extension that Pomelo introduces (it is not a function that is generally available to all EF Core providers).

So you have a couple of options here:

• Just implement the query yourself (e.g. in your controller)
• Query all the data and filter later using LINQ (...to Objects)
• Add your own expression translation logic to either OData or Pomelo