EF Core 7 can't deserialize dynamic-members in JSON column

Question

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

1. Using an empty class with a [JsonExtensionData] dictionary inside
2. Using a : DynamicObject inheritance and implementing GetDynamicMembers, TryGetMember, TrySetMember, TryCreateInstance
3. Directly mapping to a string dictionary.
4. 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.

Answer 1

I think currently it's not fully supported:

1. You can not use dynamic operations on an expression tree like a Select statement because it needs to be translated.
2. JsonValue and JsonQuery requires a path to be resolved.
3. If you specify OwnsOne(entity = >entity.owned, owned => owned.ToJson()) and the Json could not be parsed you will get an error.

I suggest this workaround while the EF team improves the functionality.

1. 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.

       public static class DBF
       {
           public static string JsonValue(this string column, [NotParameterized] string path)
           => throw new NotSupportedException();
   
           public static string JsonQuery(this string column, [NotParameterized] string path) => throw new NotSupportedException();
          }
   

2. Include the database functions on your OnModelCreating method.

           protected override void OnModelCreating(ModelBuilder modelBuilder)
           {
               base.OnModelCreating(modelBuilder);
   
               modelBuilder.HasDbFunction(
                 typeof(DBF).GetMethod(nameof(DBF.JsonValue))!
               ).HasName("JSON_VALUE").IsBuiltIn();
   
               modelBuilder.HasDbFunction(
                   typeof(DBF).GetMethod(nameof(DBF.JsonQuery))!
               ).HasName("JSON_QUERY").IsBuiltIn();
   
               /// ...
   
               modelBuilder.Entity(entity => {
                   //treat entity as text
                   entity.Property(x => x.Metadata)
                         .HasColumnType("varchar") 
                         .HasMaxLength(8000); 
   
               });
            }
   

3. Call them dynamically with LINQ.

        var a = await _context.FileInformation
           .AsNoTracking()
           .Where(x => x.Metadata!.JsonValue("$.Property1") == "some value")
           .Select(x => x.Metadata!.JsonValue("$.Property2"))
           .ToListAsync();
   

You can add casts or even build anonymous types with this method.

Answer 2

My solution was I added a new class which has KEY and VALUE , which will represent the dictionary i needed :

public class DictionaryObject
{
    public string Key { set; get; }
    public string Value { set; get; }
}

and instead of having this line in the JSON class :

public Dictionary<string, string> Name { get; set; }

I changed to :

public List<DictionaryObject> Name { get; set; }

Hope it helps.