No translation for dictionary indexer on jsonb columns

[ColonelBundy]

Consider the following entity:

public class User
{
      [Key]
      public Guid Id { get; set; }

      [Column(TypeName = "jsonb")]
      public Dictionary<string, object> Properties { get; set; }
}

And then querying:

context.User.Where(x => x.Properties["something"] == "something else").ToList();

Ends up not being able to translate.

Translating the method call of get_Item to something npgsql can understand has proven to be difficult. And one would assume translating it into the proper binding is not possible due to the dynamic nature. I would assume some internal work is needed to properly translate get_Item into sql directly instead.

I looked into perhaps doing a custom Visitor to handle this case but my Expression knowledge lacks in this case and it does not seem possible in the end anyways.
Also to clarify, changing the dictionary to a JsonDocument type is not warranted in this case.

[roji]

This is indeed unsupported at the moment. The mapping itself works since System.Text.Json can serialize/deserialize dictionary as JSON, but the query pipeline does not support the translating of the dictionary indexer, as you've noticed. There's nothing impossible or special about it - just like we translate the various methods on JsonDocument, it's possible to translate this.

We are planning a general JSON overhaul for EF Core 6.0 - not just for the PostgreSQL provider - so at this point I'm a bit reluctant to make this change. Is there any specific reason you don't want to use JsonDocument, except for aesthetics?

[ColonelBundy]

Oh I didn't mean it wasn't possible in anyway I was referring to it seems impossible to hook up a custom visitor as a prestep before efcore kicks in to for instance translate my dictionary indexer to JsonDocument compatible calls which could indeed solve my use case.

The reason I can't use JsonDocument directly on the Entity is because I'm migrating an existing system that's using EAV to ef core so changing to JsonDocument would indeed be a larger undertaking than hooking up a custom ExpressionVisitor.

Glad to hear you guys are planning a json overhaul, one would assume this also perhaps means supporting the new jsonpath syntax too?

[roji]

Thanks for the added details. Yeah, mapping Dictionary<string, object> and translating the indexer seems like a very valid scenario to me - I've noted it for 6.0 (I'll probably be doing work on this in EF itself). Jsonpath is definitely also on my list, tracked by #1045 for PG specifically - but again I'll look into this from a cross-database perspective for 6.0.

Will keep this open for now to specifically track dictionary indexer for EFCore.PG.

[ColonelBundy]

Quick update for anyone running into this issue but are looking for a temporary solution until a viable solution arrives in EF core 6.

I solved it using a custom Expression visitor and since I know the properties and their type at some time in the lifecycle of the application I can then create a dynamic class using IL with the proper properties and types. This way the Expression visitor can translate it to something EF can understand.

private MethodInfo PropertyMethod = typeof(EF).GetTypeInfo().GetDeclaredMethod(nameof(EF.Property));

protected override Expression VisitMethodCall(MethodCallExpression node)
{
    if (node.Method.Name == "get_Item")
    {
        var propName = (string)((ConstantExpression)node.Arguments[0]).Value;
        var property = ((MemberExpression)node.Object).Member.Name;
        /* var type = (create dynamic class here of your known properties and their types and cache this if you can) */ 

        var expr = Expression.Call(PropertyMethod.MakeGenericMethod(type), _parameters[0], Expression.Constant(property));
        return  Expression.Property(expr, propName);
    }

    return base.VisitMethodCall(node);
}

The application I'm migrating is offering the end user a way to create properties (like any other EAV based application) and since I do let them set the name and type of the property. it'll then let me create a dynamic class based on that and cache it.

So if you don't know the property name and it's type beforehand, then this is not for you.

[martinzima]

We have run into this issue as well, basically when implementing something like EAVs. Our requirement was to be able to search the values in the dictionary with Web API OData using its open types which map the dynamic values to an IDictionary<string, object>.

Generating new classes with IL didn't seem like an option for my use case, so I took a different approach and implemented a custom expression visitor and a method call translator to translate the dictionary operations.

public SqlExpression Translate(SqlExpression instance, MethodInfo method, IReadOnlyList<SqlExpression> arguments, IDiagnosticsLogger<DbLoggerCategory.Query> logger)
{
    if (method.DeclaringType == typeof(IDictionary<string, object>) && instance.TypeMapping is NpgsqlJsonTypeMapping
        method == ContainsKey)
    {
        return sqlExpressionFactory.MakePostgresBinary(PostgresExpressionType.JsonExists, Jsonb(instance), arguments[0]);
    }
    else if (method.DeclaringType == typeof(PgsqlFunctions) && arguments.Count == 2
        && arguments[0].TypeMapping is NpgsqlJsonTypeMapping instanceMapping && method == JsonGet)
    {
        return sqlExpressionFactory.JsonTraversal(arguments[0],
            new[] { arguments[1] }, true, typeof(string), stringTypeMapping);
    }

    SqlExpression Jsonb(SqlExpression e) => sqlExpressionFactory.ApplyTypeMapping(e, jsonbTypeMapping);
    return null;
}

In case anyone was interested, I've put up a more complete proof of concept here:
https://gist.github.com/martinzima/40b61ef3c3a6aa8512017344a713589c

This is much more flexible as you don't have to know all the properties and generate the types with IL beforehand like with the approach proposed before. Still, please bear in mind that for now, this supports only IDictionary<string, object> and relies on using internal Npgsql API, so it may not work as expected in the future versions.

[eduardocp]

Any news?

[roji]

@eduardocp this is in the backlog - it's not really helpful to comment asking for status. Please vote up this issue to indicate your interest.

[NC-WOST]

It's been quite a long time now, and this issue gained some traction. Additionally, for example #1825 is linked to it (and maybe some others, I didn't go through all of them). So... how is it going? If you want to use OData Open Types (Open Types in OData v4 with ASP.NET Web API you're forced to use IDictionary<string, object>() and all OData's functionality just falls apart due to missing translations.

[roji]

I'm sorry, but I don't have much spare time to invest in this, and effort is generally going into modeled JSON access via complex types (introduced in EF 10) - that's the natural way to interact with JSON data via EF, which is a strongly-typed ORM. I generally recommend switching away from Dictionary JSON mapping (which is weakly typed) to complex type JSON mapping. I have no idea whether OData allows that though.