こんにちは。開発のY.Mです。

SqlKata という C# 向けの SQL クエリビルダーを使って開発をしているのですが、使用上で困った事がありました。
最終的には解決できたのですが、直面している問題に対する解決策を明示している記事は見付けられず、解決には結構な時間を要しました。
おそらく同じような問題に直面する方もおられると思いますので、解決策を Blog の記事として残しておきます。

 

意図通りのSQL文が生成されない

表題のとおり「SqlKata で生成する UPDATE SQL 文でカラムの値を加算したい」というのが当記事のテーマです。
まずは具体的に何をしたいのか、そこでどんな問題が起こったのかを説明しましょう。

仮に下記のような構成のテーブルがあるとします。データベースは SQL Server を想定しています。

 

テーブル名：CounterTable

カラム名	型
id	int
count	int
last_updated	datetime

 

このテーブルに対し、指定した id に一致するレコードの count を加算するのが目的です。ついでに last_updated も現在日時で更新します。
直接 SQL 文を書くなら下記のような形になるでしょう。

UPDATE
  CounterTable
SET
  count = count + 1
  , last_updated = GETDATE()
WHERE
  id = @id

この SQL 文を SqlKata で生成させるため下記のコードを書いてみました。

// UPDATEクエリを作成
var query = new SqlKata.Query("CounterTable")
    .AsUpdate(new Dictionary<string, object?>()
    {
        { "count", "count + 1" },
        { "last_updated", "GETDATE()" },
    })
    .Where("id", 1);

// クエリをコンパイルしてSQL文を生成(例では SQL Server)
var compiler = new SqlKata.Compilers.SqlServerCompiler();
var sql = compiler.Compile(query);

生成されるSQL文はこんな感じ(読みやすいように整形しています)。

UPDATE
  [CounterTable] 
SET
  [count] = @p0
  , [last_updated] = @p1 
WHERE
  [id] = @p2

この時点でなんだか嫌な予感がしてきました。
@p0 ～ @p2 はパラメータなので、実際に実行される SQL 文はこうなります。

UPDATE
  [CounterTable] 
SET
  [count] = 'count + 1'
  , [last_updated] = 'GETDATE()' 
WHERE
  [id] = 1

予感的中。
パラメータの値が文字列扱いとなって ‘ で囲われてしまい、意図したSQL文は生成されませんでした。

 

困った時のAI頼み

こういう時はAIを活用するのが先進的なITエンジニアの流儀でありますので、ここは GitHub Copilot 先生にサクッと解決して頂きましょう。

素早い回答ありがとうございます先生。
では早速コードをコピペして動かしてみ……

って動かんやんけ！

他の類似するライブラリを混ぜ込んできたのか、勝手にメンバを生やしてきました。生成AI名物、それっぽい回答をしてドヤるやつです。ネット上に SqlKata の情報が少ない事、公式サイトに解決策が明示されていない辺りが原因でしょう。

ありがとう先生、ここから先は自分の力で解決策を探す旅に出るよ……。

 

あぶない救世主 SqlKata.Expressions.UnsafeLiteral

詳細を省くが結論だけ言うと解決策は見付かりました。

パラメータの値に文字列ではなく SqlKata.Expressions.UnsafeLiteral のオブジェクトを指定します。
書き直したコードがこちら。

// UPDATEクエリを作成
var query = new SqlKata.Query("CounterTable")
    .AsUpdate(new Dictionary<string, object?>()
    {
        { "count", SqlKata.Expressions.UnsafeLiteral("count + 1") },
        { "last_updated", SqlKata.Expressions.UnsafeLiteral("GETDATE()") }
    })
    .Where("id", 1);

// クエリをコンパイルしてSQL文を生成(例では SQL Server)
var compiler = new SqlKata.Compilers.SqlServerCompiler();
var sql = compiler.Compile(query);

こちらで生成される SQL 文は下記の通りで、意図したものとなりました。

UPDATE
  [CounterTable]
SET
  [count] = count + 1
  , [last_updated] = GETDATE()
WHERE
  [id] = @p0

 

解決策は見付かりましたが、UnsafeLiteral (安全でないリテラル)の名前通り使い方には注意が必要です。
引数に記載した通りの内容を SQL 文に埋め込むため、変数を使用する場合は SQL インジェクション対策が必須になります。
UnsafeLiteral はどうしても使わざるを得ない局面でのみ限定的に使用するのがよいでしょう。

 

[余談] AIがんばれ超がんばれ

この記事を執筆している段階でAIは正答できませんでしたが、インターネット上に情報を流す事でAIが学習し、いずれ正答してくれる事を期待しています。