SQL Serverでプログラム中のDictionaryを使用して、SQL文でUPDATE ~ SELECTしたい

Question

SQL Serverでプログラム中のDictionaryを使用して、SQL文でUPDATE ~ SELECTしたいです。

利用環境 SQL Server 2019 Express

1つのSQLコマンドで下記の①②③を実行するようなイメージです。
① 仮テーブル作成
② @tempにプログラム中のDictionaryを追加したい　{ { 1: 1}, { 2: 2} } … @tempのIdとOrderに該当
③ @tempの情報でUPDATE ~ SELECT

どなたかご教授をお願いします。

 /* ① */
DECLARE @temp TABLE (
    [Id] int NOT NULL,
	[Order] int NOT NULL
)

 /* ② やりかたがわかっていない。Insertするだけ？ */


 /* ③ 下記の例はエラーするかも */
UPDATE [dbo].[M_Tests]
SET [Order] = B.[Order]
FROM [dbo].[M_Tests] AS A
    LEFT OUTER JOIN (
        SELECT [Id], ROW_NUMBER() OVER (
            ORDER BY [Order] ASC
        ) AS 'Order'
    FROM @temp
) AS B
ON A.[Id] = B.[Id]

public async Task<int> SortAsync(IReadOnlyDictionary<long, long> dictionaries) {
    string sql = @"SQL文をここに書く"

    await this._context.Database.ExecuteSqlRawAsync(sql, null); // ←　キーとかなら第2引数以降にわたせる

    return;
}

追記

DataTableのみ作成してみました。

// DataTableのみ作成してみました。
DataTable dataTable = new DataTable("temp");
dataTable.Columns.Add("id", typeof(long));
dataTable.Columns.Add("order", typeof(long));

foreach (var dictionary in dictionaries) {
    DataRow dataRow = dataTable.NewRow();
    dataRow["id"] = dictionary.Key;
    dataRow["order"] = dictionary.Value;
}

Answer 1

1つのSQLコマンドで下記の①②③を実行するようなイメージです。

SQLのコマンド自体が分かれているので不可能です。
ExecuteSqlRawAsyncに渡すパラメータとしてのSQLがセンテンス(;)で区切られて実行可能なら同じようにSQLを記述すれば良いだけだと思います。

若しくは、①②③をセットにしたストアドを作成し、それを呼び出すとか。

Comments

[退会済みユーザー]

コメントありがとうございます。
1つのSQL文というのは間違いでした。
言われているとおり;で区切れば問題ないようです。

調べてみたところ　INSERT　で追加できるようですが、INSERTがボトルネックなので　INSERTにDataTableを使う方法があるみたいです。

EntityFrameWorkでsqlParamaterを使うみたいではありますが。

[sazi]

「プログラム中のDictionaryの内容をM_Testsに更新した上で結果を取得したい」という事でしょうか？
であるなら、
１．プログラム中のDictionaryの内容をM_Testsに更新
２．M_Testsの内容を取得
に分けて考えて、２は特に問題ないでしょうから、１の実装について考えれば良いと思います。

[退会済みユーザー]

1の実装についてSqlParameterが使えないか考えています。
SqlParameterでDataTableを使えると、一時テーブルを作成する手間がなく、そのままテーブルとして利用できるようです。

EFでSqlParameterが使えるのかはわかっていなく調べているところですが

var param = new SqlParameter("@Temp", dataTable) { TypeName = "dbo.Temp", SqlDbType = SqlDbType.Structured };
await this._context.Database.ExecuteSqlRawAsync(sql, param);

await this._context.Database.ExecuteSqlRawAsync(sql, param);　の　paramが　{1}として取り出せるはずですが、この行でエラーが出ています。

[sazi]

> SqlParameterでDataTableを使えると
単に配列のパラメータとして渡されるだけでは無いのでしょうか？
また、その後のUPDATE文でどのように利用するつもりですか？

@Tempにinsert into values()で追加でも良いのでは？

[退会済みユーザー]

単に配列のパラメーターとしてわたしたいです。
それをUPDATE ~ SELECTのテーブルとして使用したいです。

@Tempにinsert into values()で追加でも良いとは思っています。

[sazi]

> UPDATE ~ SELECTのテーブルとして使用したいです。
配列のパラメータで受け取るとupdateが面倒だと思うので、@tempにinsertした方が良いかと思います。

[退会済みユーザー]

@tempにinsertする方法で、プログラムでSQL文を作成して解決できました。
ありがとうございました。

生のSQL文の方は下記のようにしました。

DECLARE @temp TABLE ( [Id] int NOT NULL, [Order] int NOT NULL)
INSERT INTO @temp ([Id], [Order]) VALUES (1, 3)
INSERT INTO @temp ([Id], [Order]) VALUES (2, 1)
INSERT INTO @temp ([Id], [Order]) VALUES (3, 2)

UPDATE [Test].[dbo].[M_Tests]
SET [Order] = B.[Order]
FROM [Test].[dbo].[M_Tests] AS A
LEFT OUTER JOIN (
SELECT [Id], ROW_NUMBER() OVER (
ORDER BY [Order] ASC
) AS 'Order'
FROM @temp AS B
) AS B
ON A.[Id] = B.[Id]
WHERE A.[Id] = 1 OR A.[Id] = 2 OR A.[Id] = 3　// ←テストのため3件にしぼってます。

Updateをかける時にIdが存在するかはチェックをかけないとエラーするのでそれは追加したほうがよいかもしれません。

※全件あることは前提ですが。
※DataTableを使おうとしていた時、ソースがきたなくなってしまったのもありますが、
シンプルに一時テーブルに追加したほうがわかりやすいし、レスポンスのネックもあまり感じませんでした。

[退会済みユーザー]

WHEREでNULLチェックをして対応しました。

DECLARE @temp TABLE ( [Id] int NOT NULL, [Order] int NOT NULL)
INSERT INTO @temp ([Id], [Order]) VALUES (1, 3)
INSERT INTO @temp ([Id], [Order]) VALUES (2, 1)
INSERT INTO @temp ([Id], [Order]) VALUES (3, 2)

UPDATE [Test].[dbo].[M_Tests]
SET [Order] = B.[Order]
FROM [Test].[dbo].[M_Tests] AS A
LEFT OUTER JOIN (
SELECT [Id], ROW_NUMBER() OVER (
ORDER BY [Order] ASC
) AS 'Order'
FROM @temp AS B
) AS B
ON A.[Id] = B.[Id]
WHERE B.[Id] IS NOT NULL