Mapping a semi-structured JSON column into a class in EF Core 7

Question

I would like to use the new JSON Columns feature of EF Core 7 to store and retrieve data in the following format in and from my PostgreSQL database:

{
    "Name": "Email_AND_Phone_OR_RootUser",
    "Rules": [
       ["HasEmail", "HasPhone"],
       ["IsRoot"]
    ]
 }

This array of string arrays has a dynamic length and the string arrays within it too. If i understand correctly, i should create an owned class, reference it in my entity and either add the appropriate data attribute or configure it OnModelCreating. In the examples i find in the internet, i don't see any use of lists or arrays within the JSON mapping class. Are the following mapping classes valid ?

public class Policy
{
    [MaxLength(30)] public string Name { get; set; } = null!;
    public List<List<string>> RuleBinding { get; set; } = new();
}

Or, as an array of string arrays:

public class Policy
{
    [MaxLength(30)] public string Name { get; set; } = null!;
    public string[][] RuleBinding { get; set; } = null!;
}

Also, is the use of JSON Columns appropriate in this case or not?

Guru Stron · Accepted Answer · 2023-01-19 12:18:24Z

1

EF Core library for PostgreSQL (Npgsql.EntityFrameworkCore.PostgreSQL) has it's own support for JSON which was build prior to EF Core 7 and EF 7 APIs are not supported yet - see JSON Mapping doc:

EF Core 7.0 introduced support for JSON columns. Npgsql's JSON support - detailed below - is different, and has been available since version 3.0. We plan to adopt EF's JSON support in version 8.0.

Both mapping should be supported AFAIK.

public class Policy
{
    [MaxLength(30)] public string Name { get; set; } = null!;
    [Column(TypeName = "jsonb")] public List<List<string>> RuleBinding { get; set; } = new();
}

answered Jan 19, 2023 at 12:18

Guru Stron

151k11 gold badges186 silver badges232 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Arkena Over a year ago

Thank you for the answer. Is there a preferable data structure to use for JsonB? Is my approach (using arrays or lists) okay or should i preferably store it as a string or a Dictionary<string, object>?

Guru Stron Over a year ago

@Arkena it depends on your goals, personally I find structured approaches more suitable (you can use classes even over Dictionaryes), but not always they can be used, in some cases you can even use JsonDocument as mentioned in the docs.

Arkena Over a year ago

The data i store as jsonb is related to policies and rules for ABAC authorization. I'm using postgres to persist the rules and policies and update them. For reads, it will be accessible from the local cache. In my services, the administrators should be allowed to add and remove rules / policies and attach them to the endpoints they see fit through the user interface. Since authorization is a blocking task that occurs at each request and since there will be some sorting and validation involved in the process i'm afraid to use a data structure that increases latency and memory usage.

Collectives™ on Stack Overflow

Mapping a semi-structured JSON column into a class in EF Core 7

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related