Question 110646 in Json Question posted in
Our archive of expertly curated questions and answers provides insights and solutions to common problems related to this popular data interchange format. From parsing and manipulating JSON data to integrating it with various programming languages and web services, our archive has got you covered. Start exploring today and take your JSON skills to the next level

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

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.

Tags:

2

Answers


  1. 0

    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.

    Login or Signup to reply.
  2. 0

    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.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search