Pomelo.entityframeworkcore.mysql: Update to 5.0.0-alpha.2 breaks Newtonsoft JObject query support

[rabberbock]

Steps to reproduce

Repro at https://github.com/rabberbock/PomeloMySqlJson

The issue

Json query for Newtonsoft JObject cannot be translated to DB query.

Unhandled exception. System.InvalidOperationException: The LINQ expression 'DbSet<Blog>()
    .Where(b => b.Metadata.get_Item("title")
or switch to client evaluation explicitly by inserting a call to 'AsEnumerable', 'AsAsyncEnumerable', 'ToList', or 'ToLi   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.<VisitMethodCall>g__CheckTranslated|15_0(ShapedQueryExpression translated, <>c__DisplayClass15_0& )
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.VisitMethodCall(MethodCallExpression methodCallExpression)
   at System.Linq.Expressions.ExpressionVisitor.Visit(Expression node)
   at Microsoft.EntityFrameworkCore.Query.QueryableMethodTranslatingExpressionVisitor.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.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 System.Linq.Queryable.FirstOrDefault[TSource](IQueryable`1 source)
   at NestedJsonTestPomelo.Program.Main(String[] args) in C:\Users\RaffiAbberbock\Projects\NestedJsonTestPomelo\Program.cs:line 39

Further technical details

MySQL version: 8.0.17
Operating system: Windows 10 Pro
Pomelo.EntityFrameworkCore.MySql version: 5.0.0-alpha.2
Microsoft.AspNetCore.App version: .Net 5

Thanks for all the amazing work!

[mguinness]

When you say 5.0.0-alpha.2 breaks query, did the same code work w/ 3.2?

Also I noted that Metadata field type is JObject, so shouldn't query be:

.Where(b => b.Metadata.Root["title"].Value<string>() == "My new Blog")

instead of:

.Where(b => b.Metadata["title"].Value<string>() == "My new Blog")

[rabberbock]

@mguinness Awesome, that did the trick!

When you say 5.0.0-alpha.2 breaks query, did the same code work w/ 3.2?

Yeah, this syntax worked in 3.2. Here is the repro for that https://github.com/rabberbock/PomeloMySqlJson/tree/netcore3.2.

[lauxjpn]

@rabberbock Your original sample code works, if you use JToken instead of JObject.

However, I would have expected both of them to work and LINQ to JSON suggests that this should work as well.
Also, I am not aware of any breaking changes in this area for 5.0, so whatever worked before for Newtonsoft.Json should have still worked.

So I will take a look at this.

[lauxjpn]

Looks like this is a bug in EF Core upstream that got introduced in 5.0.

[lauxjpn]

There are multiple possibilities for implementing a workaround by us, but all are a bit of a hack. So lets first see what the EF Core team says.

[lauxjpn]

Alright, this has been fixed upstream and will ship with EF Core 5.0.1.
Since there is a workaround (using the Root property as @mguinness pointed out), I propose that we don't implement a temporary fix and just wait for the upstream release.