Using JSON data types with .NET EF Core

Since v7, Entity Framework has provided support for JSON data types in the database, it means we can map JSON to classes and query JSON fields in our Linq queries.
However PostgreSQL has been supporting column type: json since more than a decade, they even have another variant, column type: jsonb.

Here we will not go into details about the difference between json and jsonb: just keep in mind that json is stored as is in string format preserving the fields order and white spaces, but jsonb is stored in binary and it also allows indexing on json fields.

Let's create a small .Net Web API project using PostgreSQL at the backend with some json and jsonb types.
Before starting, take a look at the project dependencies:

First of all, we will create a table in our PostgreSQL db, it will have two special fields, of type json and jsonb.

CREATE TABLE IF NOT EXISTS public."user"
(
    "createdAt" timestamp without time zone NOT NULL DEFAULT now(),
    fullname character varying COLLATE pg_catalog."default",
    email character varying COLLATE pg_catalog."default",
    address json,
    addressb jsonb,
    id bigint NOT NULL GENERATED ALWAYS AS IDENTITY ( INCREMENT 1 START 1 MINVALUE 1 MAXVALUE 9223372036854775807 CACHE 1 )
)

TABLESPACE pg_default;

ALTER TABLE IF EXISTS public."user"
    OWNER to postgres;

Now define the EF model class with sub elements (JSON objects).

public class User : BaseEntity
{
    public string fullname { get; set; }
    public string email { get; set; }
    public Address address { get; set; }
    public Address addressb { get; set; }     
}
public class Address
{
    public string street { get; set; }
    public string city { get; set; }
    public string zipcode { get; set; }
}

For database operations, we will use inheritance to avoid repeated code, basic CRUD operations will be done in a parent service class (GenericService) using parent model class and an interface.
(You can find the base model class/interface in the project folder)

public class DBContext : DbContext
{
    public DbSet<User> user  { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder.UseNpgsql("Connection_String");

}//class

Let's create a new class for database operations, inheriting the parent class.

public class GenericService<Entity> where Entity : class, IBaseEntity
{
    private readonly DBContext _context;

    public GenericService(DBContext dbContext)
    {
        _context = dbContext;
    }

    //Delete the entity by id
    public async Task<bool> Delete(long id)
    {
        try
        {
             var entity = await GetById(id);
             _context.Set<Entity>().Remove(entity);
             var deleteCount = await _context.SaveChangesAsync();

             return deleteCount > 0 ? true : false;
        }
        catch (Exception ex)
        { 
             throw ex; 
        }
    }
}

UserService class will have the methods called by the Controller like GET, PUT, DELETE etc.

public class UserService : GenericService<User>
{
    private readonly DBContext dbcontext;

    public UserService(DBContext dbcontext) : base(dbcontext)
    {
        this.dbcontext = dbcontext;
    }

    public async Task<bool> DeleteUserById(long userId)
    {
        try
        {
            return await Delete(userId);
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }
}

You will notice that we have not done anything specific for the JSON types in the context class yet. We will define the JSON mapping in OnModelCreating method of the context.

modelBuilder.Entity<User>().OwnsOne(o => o.address, sa =>
{
    sa.ToJson(); // Maps address to a JSON column
});

modelBuilder.Entity<User>().OwnsOne(o => o.addressb, sa =>
{
    sa.ToJson(); // Maps addressb to a JSON column
});

This will allow us to query json fields e.g. zipcode in the address object in our EF code like this:

dbcontext.user.Where(x => x.addressb.zipcode == zip)

We will also follow the same nested object pattern to poplate User object to update or create record in DB.

User? newUser = new();

newUser.addressb = new Address
{
    street = street,
    city = city,
    zipcode = zip
};

newUser.address = new Address
{
    street = street,
    city = city,
    zipcode = zip
};
newUser.email = email;
newUser.fullname = fullname;
newUser.createdAt = DateTime.Now.ToUniversalTime();

await Update(newUser); //Generic method in Parent class

Dependency Injection

We have to define our Context and Service class in Program.cs

builder.Services.AddTransient<DBContext>();
builder.Services.AddTransient<UserService>();

I have added some endpoints to the controller for basic CRUD operations on the User table.

Before closing, here are some observations made while buidling the projest:

• The entity / db table must have a primary key.
• In some cases, you can also use annotations with type, then you may not need to add .OwnsOne(..) in the Context.

[Column(TypeName = "jsonb")]
public string address { get; set; }

That is it, I hope it will help someone. The project can be downloaded from here.

Bye!