Question: json Column: support for readonly dictionaries

[JanEggers]

Ask a question

Im currently migrating a project from net6 to net7. we are already using jsoncolumns with ef6 but would like to migrate from

public class MyModel 
{
   public Child Child { get; set; }
}

public class Child 
{
   public IReadOnlyDictionary<string, object> CustomerExtensions { get; set; }
}

e.Property(p => p.Child).HasConersion(....);

to

e.OwnsOne(p => p.Child, p => p.ToJson());

but when trying this I encountered:

The property 'Child.CustomerExtensions' could not be mapped because it is of type 'IReadOnlyDictionary<string, object>', which is not a supported primitive type or a valid entity type.

also: having IReadOnlyDictionary<string, object> as Json Property is also not working.

Will this eventually be covered in .net 8 with primitive collection support?

Is there another way to enable json queries with the old conversion approach?

[roji]

Duplicate of #29825

[roji]

No, unfortunately it's not yet possible to map Dictionary to JSON for relational databases. The upcoming EF 8 support for primitive collections is also restricted to arrays/lists, and won't support dictionaries either. Rich querying currently works only for strongly-typed mapping, i.e. where you have an actual .NET type rather than a weakly-typed dictionary.

Bottom line: for now you'll have to keep doing things as you did them in EF Core 6/7, via value conversions.

[JanEggers]

thx for clarifiing

[JanEggers]

@roji FYI I was able to improve querying by adding a custom function mapping:

before I used:

return Items.FromSqlInterpolated($"SELECT * FROM Items WHERE JSON_VALUE(Extension, '$.value') = {value}");

with this function mapping:

onModelCreating() {

            modelBuilder.HasDbFunction(typeof(DbContext).GetMethod(nameof(JsonValue), new[] { typeof(string), typeof(string) }))
                .HasTranslation(args => new SqlFunctionExpression("JSON_VALUE", args, true, new [] { false, false }, typeof(string), null));
}

        public string JsonValue(string expression, string path)
        {
            throw new NotSupportedException();
        }

i can use this query which is much better because less of the query is hardcoded string

return Items.Where(sh => JsonValue((string)(object)sh.Extension, "$.value") == value);

[roji]

@JanEggers yeah, you can enable this very limited form of querying - but you won't be able to do various other types of querying, e.g. the collection querying support which we're adding in EF 8.0. But if this partial solution is sufficient for you, it's definitely fine.

[palhal]

return Items.Where(sh => JsonValue((string)(object)sh.Extension, "$.value") == value);

Wow, that was clever @JanEggers. Exactly what I needed until dictionaries are supported first class. Thanks!