JSON Column handling with unknown/dynamic structure

[mawi1512]

Hello everyone,

I’m attempting to use the JSON columns feature in Entity Framework Core with a database where JSON data is stored as strings. The problem is that the structure of these JSON objects varies and is not known at compile time.

I’ve tried to handle this using dynamic, ExpandoObject, JsonObject, and Dictionary, but haven’t had any success.

Here’s a simplified version of my entity:

public partial class MyEntity
{
    public string Id { get; set; } = null!;
    public JsonObject Content { get; set; } = null!;
}

My goal is to create a web API that allows users to apply filters to the JSON content (similar to OData). Currently, I’m serializing/deserializing my JSON as a string, which doesn’t allow for server-side dynamic querying/filtering. This has to be done client-side, which isn’t very efficient.

So basically i want to do something like that (pseudo code):

var result = context.MyEntity
        .Where(entity=> entity.JsonObject[PathInsideJson] == filterValue)
        .ToList();

Is it somehow possible to query dynamic jsons? Should I resort to writing SQL queries? Can dynamic linq help? Any guidance or suggestions would be greatly appreciated.

Thank you for your help!

---

EF Core version: 8.0 RC1
Database provider: Microsoft.EntityFrameworkCore.SqlServer
Target framework: NET 8.0 RC1
Operating system: Windows
IDE: Visual Studio 2022 17.8.0 Preview 2

[roji]

Duplicate of #29825 and/or #28871.

EF doesn't yet support dynamic JSON mapping; it currently requires that the JSON document shape be known in advance, and modeled via a .NET POCO type. #29825 tracks allowing mapping dynamic JSON as a dictionary, whereas #28871 tracks doing so via the the System.Text.Json APIs (JsonDocument/JsonElement).

[mawi1512]

Thanks for your fast response. I have set my votes :)