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

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!