EF.Functions.JsonExtract path parameter cannot contain variable

[wjadevries]

Steps to reproduce

This works: var c = dbContext.Set().OrderByDescending(o => EF.Functions.JsonExtract(o.Opportunity_Customfields, "$.field_2")).ToList();

But this throws an error: var path = $"$.{propertyName}"; var d = dbContext.Set().OrderByDescending(o => EF.Functions.JsonExtract(o.Opportunity_Customfields, path)).ToList();

Same problem on the Where: Works: var a = dbContext.Set().Where(e => EF.Functions.JsonExtract(e.Opportunity_Customfields, "$.field_2") == value);

Doesn't work: var b = dbContext.Set().Where(o => EF.Functions.JsonExtract(o.Opportunity_Customfields, path) == value).ToList();

The issue

I don't understand what the use is of the JsonExtract if you can't supply a variable as the path parameter. Json is by nature a flexible format, so when created by external sources or by using it to store custom fields, I don't know what the key names are. You should be able to supply a variable for the keys (i.e. the path parameter), otherwise JsonExtract is useless.

Exception message:
System.InvalidOperationException: 'The LINQ expression 'DbSet<opportunity>()
    .OrderByDescending(o => __Functions_0
        .JsonExtract(
            json: o.Opportunity_Customfields, 
            paths: new string[]{ __path_1 }))' could not be translated. Additional information: The query contained a new array expression containing non-constant elements, which could not be translated: 'new string[]{ __path_1 }'. 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.'

Stack trace:

Further technical details

MariaDB version: 11.4 Operating system: Windows 11 Pomelo.EntityFrameworkCore.MySql version: 8.0.2 Microsoft.AspNetCore.App version: 8

[lauxjpn]

I don't understand what the use is of the JsonExtract if you can't supply a variable as the path parameter.

JSON support was added at a time, where we needed to apply some limitations to make it work (e.g. I believe that SQL parameters are not supported as JSON paths in the MariaDB/MySQL implementation, though my memory might be wrong, since it has been a couple of years). Anyways, nowadays we should be able to also extract the path from a parameter, and inline it as a constant value, so it would make sense to enhance the behavior to support such cases as well.

---

If you are willing to use an EF Core 9 compatible Pomelo release (currently 9.0.0-preview.2.efcore.9.0.0 is the latest) then a simple workaround should be to wrap your path variable with a EF.Constant() call inside the query, which will let EF Core translate your variable to a constant value, instead of a SQL parameter:

dbContext.Set()
    .OrderByDescending(o => EF.Functions.JsonExtract(o.Opportunity_Customfields, EF.Constant(path)))
    .ToList();