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

はじめに

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

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

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

SELECT NEXT VALUE FOR MySequence1

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

結論

方法1: SqlQueryRaw<long>()を用いる

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

単純なスカラー型の値を返すだけなら結果クラスの定義も不要なので、これはまさにシーケンスの取得にピッタリです。

var results
    = await dbContext.Database
    .SqlQueryRaw<long>("SELECT NEXT VALUE FOR MySequence1")
    .ToArrayAsync();
var nextValue = results[0];

ポイントとしては、結果が必ず1件だけと分かっているにも関わらず、SingleやFirst系メソッドを使ってはいけません。使うとエラーになります（後述）。

方法2: ExecuteSqlRawAsyncを用いる

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を利用しています。

方法3: FromSqlを用いる

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

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は、取得した結果が既存のエンティティと一致する場合に利用した方が良いでしょう。

ハマったポイント

SqlQueryRawでシーケンスの値の取得をFirstやSingleで取得するとエラー

方法1で紹介したSqlQueryですが、「結果は1件だけだからな」と思って以下のように使おうとすると愕然とします。

long result = await db.Database
    .SqlQueryRaw<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]

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

SingleAsyncでクエリを実行することで、こういう弊害があるんですね。方法1のように、ToArrayAsyncやToListAsyncを利用しましょう。

渡す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でシーケンス値を取得したい場合には、SqlQueryRawやExecuteSqlRawAsyncを用いる
• FromSqlを使うこともできるがちょっと面倒
• SqlQueryRawでシーケンス値の取得にSingleやFirst系メソッドを使うとエラーになる

参考記事