PostgreSQL everywhere

How to store a complex object as JSON in a PostgreSQL database using Entity Framework (Npgsql)?

This post was most recently updated on September 29th, 2022.

2 min read.

Okay, that’s a title and a half. But let’s see – what are we actually doing and why?

In my particular case, I had to store a Dictionary<string, string> in a Postgres database. By default, that’s not something Entity Framework handles.

But as usual, there’s a way around that – and the same concept we’ll take a look at below should work for other complex data types or objects too!

Solution

Okay, so let’s jump into it! Here’s what you need to add to your different files (I’m presuming you just have a vanilla .NET project with Entity Framework already in use):

Time needed: 10 minutes.

How to store a complex object as JSON in a PostgreSQL database using Entity Framework (Npgsql)?

  1. Identify your complex properties

    You’ll need to figure out which properties of which entities will cause you trouble. Technically, you could familiarize yourself with the data types PostgreSQL supports, but you’ll probably grab these from Entity Framework’s error messages when the conversion fails :) These properties are the ones that’ll need some conversions to be stored successfully.

    In my particular case, the Dictionary<string, string> is going to be the problematic one.

    public class Entity
    {
    [Key]
    public string Id { get; set; }
    public string Name { get; set; }
    public Dictionary<string, string> ComplexConfiguration { get; set; }
    }

  2. Write a custom converter for your complex property

    Custom conversion is something you configure for a property on an entity, to tell EF how to serialize and deserialize it into and from JSON.

    Here’s what you can add to your DbContext’s OnModelCreating to add a custom conversion to handle serializing JSON to the database properly:

    using System;
    using System.Collections.Generic;
    using System.Text.Json;
    using Microsoft.EntityFrameworkCore;

    namespace MyDbContext
    {
    public class MyDbContext : DbContext
    {

    // ... omitted for clarity

    protected override void OnModelCreating(ModelBuilder builder)
    {
    // This Converter will perform the conversion to and from Json to the desired type
    builder.Entity<Entity>().Property<Dictionary<string,string>>(x => x.ComplexConfiguration).HasConversion(
    v => JsonSerializer.Serialize(v, new JsonSerializerOptions() { IgnoreNullValues = true }),
    v => JsonSerializer.Deserialize<Dictionary<string, string>>(v, new JsonSerializerOptions() { IgnoreNullValues = true }));
    }
    }
    }

  3. Rebuild and run!

    After a rebuild, you should be good – if you had any migrations pending, they should now be successful (or you should get an error about a new property now!) and if you had trouble saving your entities, should be all good now!


All good! Let me know how it goes.

mm
0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments