Entity Framework Core PostgreSQL JSONB (single field update)

Question

When using Entity Framework Core with PostgreSQL, I can modify a single field in a JSON document without retrieving the entire document, thanks to PostgreSQL's support for JSON and JSONB types. However, Entity Framework Core does not seem to provide built-in methods to directly manipulate JSON fields in this way. Instead, I can only execute raw SQL commands that utilize PostgreSQL's JSON functions such as jsonb_set.

using (var context = new MyDbContext())
{
    var sql = "UPDATE your_table SET json_column = jsonb_set(json_column, '{yourField}', '\"newValue\"') WHERE id = @id";
    var id = yourEntityId;

    context.Database.ExecuteSqlRaw(sql, new Npgsql.NpgsqlParameter("@id", id));
}

Is this still the case for the recently launched .NET 9? I would like to use only LINQ queries instead of Raw SQL, but I can't find any more information. Any alternatives?

Answer 1

You can use ExecuteUpdateAsync to execute an UPDATE statement directly without loading any data.

using var context = new MyDbContext();

await context.your_table
    .Where(t => t.id = yourEntityId)
    .ExecuteUpdateAsync(setters => setters
         .SetProperty(t => t.JsonColumn, t => MyDbFunctions.JsonbSet(t.JsonColumn, new[]{ "yourField" }, @"""newValue"""))
    );

You would need to also define JsonbSet as a DbFunction. Something like:

public static string JsonbSet(JsonElement target, string[] path, string newValue, bool createIfMissing = true) =>
    throw new NotImplementedException();

modelBuilder.HasDbFunction(
    typeof(MyDbFunctions).GetMethod(nameof(MyDbFunctions.JsonbSet)),
    b =>
    {
        b.HasName("jsonb_set"),
         .HasParameter("target", p => p.PropagatesNullability().HasStoreType("jsonb"))
         .HasParameter("path", p => p.PropagatesNullability().HasStoreType("text[]"))
         .HasParameter("newValue", p => p.PropagatesNullability().HasStoreType("jsonb"))
    });