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

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

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

https://qiita.com/jun1s/items/0090dd0c4f56e63bd6a0

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


```sql
SELECT NEXT VALUE FOR MySequence1
```

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

# 結論
## 方法1: `SqlQueryRaw<long>()`を用いる

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

https://learn.microsoft.com/ja-jp/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.sqlqueryraw?view=efcore-9.0

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

```c#
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を取得します。

https://learn.microsoft.com/ja-jp/dotnet/api/microsoft.entityframeworkcore.relationaldatabasefacadeextensions.executesqlrawasync?view=efcore-8.0

```c#
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`に保存されます。

```sql
 SELECT @NextValue = NEXT VALUE FOR MySequence1
 ```

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

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

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

`FromSql`を利用するには、まずその戻り値の型を定義する必要があります。
```c#
public class SqlQueryResult<TValue>
{
    public TValue Value { get; set; }
}
```

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

そして、これをエンティティとして`DbContext`に登録します。
```c#:MyDbContext
    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);

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

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

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

```c#
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が発行され、正常に実行されます。
```sql
SELECT NEXT VALUE FOR MySequence1 AS Value
```

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

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

# ハマったポイント
## `SqlQueryRaw`でシーケンスの値の取得を`First`や`Single`で取得するとエラー
方法1で紹介した`SqlQuery`ですが、「結果は1件だけだからな」と思って以下のように使おうとすると愕然とします。

```c#
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が発行されていることがわかります。

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

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

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

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

```c#
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が発行されていました。
```sql
SELECT NEXT VALUE FOR @p0 AS Value
```

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

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

これを回避するには、`FromSqlRaw`メソッドを利用すれば大丈夫です。但し、SQLインジェクションには十分に注意してください。
```c#
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`系メソッドを使うとエラーになる


# 参考記事
https://qiita.com/Nossa/items/94e5d15a9f05b3bc1b02

https://stackoverflow.com/questions/60382040/how-to-utilize-output-parameters-in-ef-core-3-0-using-executesqlinterpolatedasyn

https://learn.microsoft.com/ja-jp/ef/core/querying/sql-queries?tabs=sqlserver