EF Coreでシーケンスの次の値を取得する（ExecuteSqlRawAsync, FromSql）

はじめに

EF Coreでシーケンスの次の値（NEXT VALUE関数の呼び出し）を取得する方法を示します。
ユーザー定義関数を実行するようなケースにも応用できると思います。
環境はSQL Serverとなります。

以前、下記記事において、レコードのINSERT時の自動採番方法については記しました

上記の方法が使えないパターンもあり、その場合には下記のSQLを実行してシーケンスの次の値を直接取得する必要があります。

SELECT NEXT VALUE FOR MySequence1

しかし、EF Coreを使っていると、DbContext を使って生SQLを実行するのは何かと手間がかかります。
今回もいろいろとハマったポイントがあったので、記しておきます。

結論

方法1: ExecuteSqlRawAsyncを用いる

EF Coreでシーケンスの次の値を取得したいだけならこの方法で良いと思います

ExecuteSqlRawAsyncを使い、OUTPUTパラメータを使ってシーケンスのNEXT VALUEを取得します。

var nextValueParam = new Microsoft.Data.SqlClient.SqlParameter
{
    ParameterName = "@NextValue",
    SqlDbType = System.Data.SqlDbType.BigInt,
    Direction = System.Data.ParameterDirection.Output
};

// SQL Serverのシーケンスから次の値を取得するSQL
var sql = "SELECT @NextValue = NEXT VALUE FOR MySequence1";
// SQL文を実行し、次の値を取得する
await db.Database.ExecuteSqlRawAsync(sql, nextValueParam);
// 次の値を取得
var nextValue = (long)nextValueParam.Value;

SqlParameterをDirection = System.Data.ParameterDirection.Output とし、出力パラメータとして受け取るようにしています。

実行すると、以下のようなSQLが発行され、正常に実行され、結果がnextValueParamに保存されます。

 SELECT @NextValue = NEXT VALUE FOR MySequence1

なお、似た名前のExecuteSqlAsyncもありますが、こちらはパラメータとして補完文字列のみを受け付けます。指定した補完文字列は自動的にDbParameter付きのコマンドとして分解されて実行されます。よって、SQLインジェクションに強いコーディングが可能となります。

今回はOutputパラメータを明示的に指定する必要があるため、ExecuteSqlRawAsyncを利用しています。

方法2: FromSqlを用いる

※シーケンスを利用する場合は方法1の方が良いと思います。

FromSqlを利用するには、まずその戻り値の型を定義する必要があります。

public class SqlQueryResult<TValue>
{
    public TValue Value { get; set; }
}

シーケンス専用の型を作るのももったいないので、上記のようにジェネリクスで定義して、いろんなSQL呼び出しで利用できるようにしておきましょう。

そして、これをエンティティとしてDbContextに登録します。

MyDbContext

    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

        // 整数型で結果を受けるSQLの実行用エンティティを登録
        builder.Entity<SqlQueryResult<long>>().HasNoKey();
        
        ...
    }

HasNoKey()メソッドは、対象のエンティティがキーを持たず、EF Coreのトラッキング対象からも外される事を指定します。SqlQueryResult<T>はただの結果データですから、キーもなくトラッキングする必要もありません。

これで、FromSqlを利用する準備が整いました。
以下のように利用します。

var result = await db
    .Set<SqlQueryResult<long>>()
    .FromSql($"SELECT NEXT VALUE FOR MySequence1 AS Value")
    .SingleAsync();
Console.WriteLine(result.Value);

上記のコードにはいくつかのポイントがあります。

一つ目は、FromSqlの引数は補完文字列でなければならない、という点です。これはExecuteSqlAsyncで説明した通りです。FromSqlRawメソッドを代わりに用いれば、直接SQL文字列を引数に渡すことができます（DbParameterのリストと共に）が、SQLインジェクションには十分気を付ける必要があります。

二つ目は、SELECT句の戻り値にAS Valueと名前を付けていることです。このValueは、SqlQueryResult<T>のプロパティ名と一致させる必要があります。

実行すると、次のSQLが発行され、正常に実行されます。

SELECT NEXT VALUE FOR MySequence1 AS Value

これらの手順は大変面倒なので、シーケンスの結果を取得したいだけの場合にはExecuteSqlAsyncを用いた方が楽だと思いますが、FromSqlの結果はIQueryrableなので、遅延評価が可能です。

FromSqlは、取得した結果が既存のエンティティと一致する場合に利用した方が良いでしょう。

ハマったポイント

SqlQueryはシーケンスの取得には使えない

MyDbContext.Databaseプロパティで取得できるDatabaseFacade型の拡張メソッドに、SqlQueryがあります。これはエンティティに関連づかない結果を返す任意のSQLを実行し、結果をモデルクラスにマッピングしてくれる便利なメソッドです。

単純なスカラー型の値を返すだけなら結果クラスの定義も不要なので、「これはまさにシーケンスの取得にピッタリ！」と思って使うとエラーになって愕然とします。

long result = await db.Database
    .SqlQuery<long>($"SELECT NEXT VALUE FOR MySequence1 AS Value")
    .SingleAsync();

実行すると次のエラーが出ます。

SqlException: NEXT VALUE FOR function is not allowed in check constraints, default objects, computed columns, views, user-defined functions, user-defined aggregates, user-defined table types, sub-queries, common table expressions, derived tables or return statements.

ログを確認してみると、次のようなSQLが発行されていることがわかります。

SELECT TOP(2) [t].[Value]
FROM (
  SELECT NEXT VALUE FOR MySequence1 AS Value
) AS [t]

つまり、シーケンスはサブクエリ内では用いることができないのに、SqlQueryがクエリをサブクエリとして処理してしまう為、エラーになってしまっているようです。

これは正直面倒な気がするので、今後改善されないかなぁと思っています。

なお、AS Valueは付けても付けなくても同じでした（SqlQueryにおいて、内部的にValueという名前のフィールドは特別扱いされる事があるので何かうまくならないかと思いましたが期待外れでした）。

渡すSQLを補完文字列にするとエラー

FromSqlメソッドに渡すSQLの「シーケンス名」部分を可変にしようとして、次のように書いたらエラーになりました。

var sequenceName = "MySequence1";
var result = await db
    .Set<SqlQueryResult<long>>()
    .FromSql($"SELECT NEXT VALUE FOR {sequenceName} AS Value")
    .SingleAsync();

SqlException: Incorrect syntax near '@p0'.

ログを見ると、次のようなSQLが発行されていました。

SELECT NEXT VALUE FOR @p0 AS Value

つまり、この記事でも触れているように、FromSqlメソッドは引数の補完文字列中にある{sequenceName}をクエリパラメータだと認識して、自動的にDbParameterとして構築する為、発行されるSQLも上記のようになってしまうのです。

NEXT VALUE FOR ～のシーケンス名はクエリパラメータにすることはできない為、SQL実行時にエラーとなっていた、というわけでした。

これを回避するには、FromSqlRawメソッドを利用すれば大丈夫です。但し、SQLインジェクションには十分に注意してください。

var sequenceName = "MySequence1";
var result = await db
    .Set<SqlQueryResult<long>>()
    .FromSqlRaw($"SELECT NEXT VALUE FOR {sequenceName} AS Value")
    .SingleAsync();

まとめ

• EF Coreでシーケンス値を取得したい場合には、ExecuteSqlRawAsyncを用いる
• FromSqlを使うこともできるがちょっと面倒
• SqlQueryを使うとエラーになる為使えない

参考記事