You signed in with another tab or window. Reload to refresh your session.You signed out in another tab or window. Reload to refresh your session.You switched accounts on another tab or window. Reload to refresh your session.Dismiss alert
{{ message }}
Repository navigation
No translation for dictionary indexer on jsonb columns #1529
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.
Reacted by Martin Zima, Kaeso, astarche, Viacheslav Sergeev, mrRashidkk, Arseny Garelyshev, Paul, Lars Mæhlum, Volodymyr Rets, Slava Potapov and 16 more
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?
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?
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.
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.
privateMethodInfoPropertyMethod=typeof(EF).GetTypeInfo().GetDeclaredMethod(nameof(EF.Property));protectedoverrideExpressionVisitMethodCall(MethodCallExpressionnode){if(node.Method.Name=="get_Item"){varpropName=(string)((ConstantExpression)node.Arguments[0]).Value;varproperty=((MemberExpression)node.Object).Member.Name;/* var type = (create dynamic class here of your known properties and their types and cache this if you can) */varexpr=Expression.Call(PropertyMethod.MakeGenericMethod(type),_parameters[0],Expression.Constant(property));returnExpression.Property(expr,propName);}returnbase.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.
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.
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.
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.
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.
@roji thanks for the reply. As far as I know, OData only works with Dictionary<>, so that's not going to help. But I had some mediocre success with IMtehodCallTranslator, so I guess that's the way for now! :)
Activity
roji commentedon Oct 10, 2020
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 commentedon Oct 10, 2020
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 commentedon Oct 10, 2020
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 commentedon Oct 11, 2020
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.
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 commentedon Nov 28, 2020
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.
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 commentedon Apr 20, 2023
Any news?
roji commentedon Apr 20, 2023
@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 commentedon Dec 18, 2025
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 commentedon Dec 18, 2025
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.
NC-WOST commentedon Dec 19, 2025
@roji thanks for the reply. As far as I know, OData only works with
Dictionary<>, so that's not going to help. But I had some mediocre success withIMtehodCallTranslator, so I guess that's the way for now! :)