I am trying to map my Name
column to a dynamic object. This is how the raw JSON data looks (note that this is SQL-morphed from our old relational data and I am not able to generate or interact with this column via EF Core):
{ "en": "Water", "fa": "آب", "ja": "水", ... }
Just to note, available languages are stored in a separate table and thus are dynamically defined.
Through T-SQL I can perfectly interact with these objects eg
SELECT *
FROM [MyObjects]
WHERE JSON_VALUE(Name, '$.' + @languageCode) = @searchQuery
But it seems EF Core doesn’t want to even deserialize these objects as whole, let alone query them.
What I get in a simple GetAll
query is an empty Name
. Other columns are not affected though.
I have tried so far
- Using an empty class with a
[JsonExtensionData]
dictionary inside - Using a
: DynamicObject
inheritance and implementingGetDynamicMembers
,TryGetMember
,TrySetMember
,TryCreateInstance
- Directly mapping to a string dictionary.
- Combining 1 & 2 and adding an indexer operator on top.
All yield the same results: an empty Name
.
I have other options like going back to a junction table relational which I have many issues with, hardcoding languages which is not really intuitive and might cause problems in the future, using HasJsonConversion
which basically destroys the performance on any search action… so I’m basically stuck here with this.
2
Answers
My solution was I added a new class which has
KEY
andVALUE
, which will represent the dictionary i needed :and instead of having this line in the JSON class :
I changed to :
Hope it helps.
I think currently it’s not fully supported:
I suggest this workaround while the EF team improves the functionality.
Create a static class with static methods to be used as decoys in the expression tree. This will be mapped to the server built-in functions.
Include the database functions on your OnModelCreating method.
Call them dynamically with LINQ.
You can add casts or even build anonymous types with this method.