DapperLib / DapperLib/Dapper

How to handle null values with JSON function in SQL statements: Function json(unknown) does not exist

Open
#1,846 3 comments 1 reaction 0 assignees View on GitHub

Nobody has claimed this yet.

Dominant language
C#
Stars
18.4k
Forks
3.7k
Avg merge
5h 8m
Merged PRs (30d)
1

Description

Is it possible to avoid issues with null values from dynamic parameters when using the JSON function in SQL queries? The problem is that if a null value is given to a query (See example below as a rough, untested example), an error occurs Npgsql.PostgresException: "42883: Function json(unknown) does not exist

The following code is an example. The parameter areAttributesNull and the string replace is added to avoid the issue, but this is not really a desired (and practicable) solution. Maybe, there is already a more simple way to handle the data properly without the need to string-replace SQL statements, but I didn't find any other solution yet...

using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Dapper;
					
public class Program
{
    public async Task<int> UpdateNodes(List<Node> nodes, bool areAttributesNull = false)
    {
        if (nodes is null || nodes.Count == 0)
            return 0;
        // Not relevant, get a connection to the database somehow.
        await using var connection =  await this.GetDatabaseConnection();

        var sqlParams = nodes.Select(node => new DynamicParameters(new
        {
            id = node.Id,
            description = node.Description,
            jsonattributes = node.JsonAttributes
        }));

        var sql = @"UPDATE node SET (id, description, jsonattributes) = 
            (@id, @description, JSON(@jsonattributes)) WHERE id = @id;";
		
        if (areAttributesNull)
        {
            sql = sql.Replace(", jsonattributes", "");
            sql = sql.Replace(", JSON(@jsonattributes)", "");
        }

        return await connection.ExecuteAsync(sql, sqlParams);
    }

    public async Task<NpgsqlConnection> GetDatabaseConnection()
    {
        var connection = new NpgsqlConnection("SomeConnectionString");
        await connection.NpgsqlConnection.OpenAsync();
        return connection;
    }
}

public class Node
{
    public string Id { get; set; }
	
    public string Description { get; set; }
	
    public string? JsonAttributes { get; set; }
}

EDIT: For reference, this is no database issue. E.g. SELECT JSON(null), * FROM node; would work properly. Must be something with the mapping.

Contributor guide

No contributing guide indexed for this repository

First steps

  1. Read the whole issue, then the project's contributing guide.
  2. Comment on the issue to say you are picking it up — it saves two people doing the same work.
  3. Fork the repository and make your change on a branch.
  4. Open a pull request that references the issue number.

Research direction

No repository file or test is named. Start by reproducing the C# UpdateNodes example with a null jsonattributes parameter and the SQL JSON(@jsonattributes) expression, then trace how Dapper maps that parameter. Done means the null case has defined behavior without string replacement and is covered by a regression test.

Written by the indexing model from the issue text.

Assessment

Tech stack
csharp, postgresql, sql
Domain
database
Issue type
Bug
Difficulty
4/5
Estimated time
3-5 days
Activity status
Stale
Clarity
Needs clarification
Newbie friendliness
25/100

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.