EF Core 3.1 × Postgres × 実際に出力されるSQLの検証を始めてみた

この記事の目的

この記事は、
EF Coreで実際に実行されるSQLの確認をしてみること
を目的としています。

本題

準備

０ベースで準備するのに結構手間がかかったので、どうやって準備したかメモしておきます。

1. Porjectの準備

新規で作成したコンソールアプリケーションのプロジェクトに、NugetでEF CoreのPostgresをインストールします。
www.npgsql.org
Npgsql.EntityFrameworkCore.PostgreSQL

2. DBの準備

DBはDocker Composeを使って、Dockerの中に構築しました。

• Docker-composrで起動する

www.kinakomotitti.net

• Postgresのログ出力設定を変更する

↑のYAMLのCommandに渡すオプションを変更したらできました。

command: postgres -c log_destination=stderr -c log_statement=all -c log_connections=on -c log_disconnections=on

参考）qiita.com

• Sample Tableの作成

いい感じのテーブル定義があったのでこちらを利用させて頂きました。
www.postgresqltutorial.com

• Logを見る

docker のコマンドに、Tailオプションがあるので、それをつかって、Logの監視をします。これをすることで、コンソールアプリを実行したとき、そのまま出力されたログを確認することができます。
docs.docker.jp

シンプルなCRUD

Insert

まずは、Insertについて確認しました。
登録するテーブルの情報はこんな感じです↓。

Accountテーブル

一部省略していますが、AccountテーブルにSampleデータを登録するコードは以下のものを利用しました。

context.Account.Add(new Account()
{
   Username = $"Sample0",
   Password = "Sample",
   Email ="0Sample@email.com"
});

この時、出力されたSQLは・・・

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED
INSERT INTO account (created_on, email, last_login, password, sex_id, username)
        VALUES ($1, $2, $3, $4, $5, $6)
        RETURNING user_id
parameters: 
       $1 = '0001-01-01 00:00:00', 
       $2 = 'Sample.User@email.com', 
       $3 = NULL, 
       $4 = 'Sample', 
       $5 = NULL, 
       $6 = 'Sample_User'
COMMIT

ポイント

• トランザクション制御されている！

既定では、データベース プロバイダーがトランザクションをサポートしている場合は、SaveChanges() への 1 回の呼び出しに含まれるすべての変更がトランザクションに適用されます。 いずれかの変更が失敗した場合、トランザクションはロールバックされ、変更は、データベースにまったく適用されません。
docs.microsoft.com

• すべての列が明示的に登録される。ただし、ID列のように、Serial型の場合は、明示的に登録されない。
• Returning 句がついている・・・ついているのに、Returningされたuser_idは、SaveChangesメソッドの戻り値として取得することができない。※戻ってくるのは、今回のクエリで影響を受けた行数。Serial（シーケンス）を使った列の自動採番された値を取得したい場合の解決方法は、前にまとめた気がするので、そっちも見てみて下さい。参考）www.kinakomotitti.net

Select

次に、Read（SELECT）を試してみました。SELECTは、JoinとかWHERE句とかいろいろな検証をしてみたいですが、基礎的なところから順番に・・・ということで、簡単なSELECTを実行しました。

Linqで検索処理を記述するとき、クエリ式を使った書き方と、メソッドチェーンで繋いでいく書き方の２つの方法があります。自分は、クエリ式のほうをよく使います（見た目がSQLっぽくてわかりやすいからです。）が、検証のため、2パターンの実行を試してみました。

//メソッド
context.Account.Select(row => row.Email).ToList();

//クエリ式
(from account in context.Account select account.Email).ToList();

出力結果は・・・

--２パターンとも、同じ結果でした。
SELECT a.user_id, 
            a.created_on, 
            a.email, 
            a.last_login, 
            a.password, 
            a.username
        FROM account AS a

Joinした場合についても一例だけ検証しました。

 //メソッド
context.Account.Join(
       context.AccountRole,
       p => p.UserId,
       t => t.UserId,
       (p, t) => new
       {
              UserName = p.Username,
              GrantDate = t.GrantDate
       }).ToList();

//クエリ式
(from account in context.Account
       join accountRole in context.AccountRole on account.UserId equals accountRole.UserId
       select new
       {
              UserName  = account.Username,
              GrantDate = accountRole.GrantDate
        }
).ToList();

出力結果は・・・

//こちらも同じ出力結果になりました。
SELECT a.username AS "UserName", 
            a0.grant_date AS "GrantDate"
FROM account AS a
INNER JOIN account_role AS a0 ON a.user_id = a0.user_id

ポイント

• テーブルに別名がつけられている・・・a, b, c, となるかと思いきや、Joinの場合は、a, a0…となるようです。それがどうしたということでもないですが・・・
• クエリ式でも、メソッド式でも、同じSQLが生成されました。コード的には、圧倒的にクエリ式が見やすいと感じます。特にJoinの構文とか・・・

Update

次はUpdateです。

//Update対象の行を適当に決めます。
var updateUser = context.Account.First();

//E-mailだけ更新します。
updateUser.Email = "Kinakomotitti@email.com";

//成功時には、resultに１が入ります。。。
var result = context.SaveChanges();

出力結果は・・・

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED
UPDATE account SET email = $1
        WHERE user_id = $2
parameters: 
         $1 = 'Kinakomotitti@email.com', 
         $2 = '5'
COMMIT

そんなかんじですね。

Delete

最後はDeleteです。

//削除対象のユーザーをサクッと決ます。
var deleteUser = context.Account.First();
//削除します。
context.Remove(deleteUser);
//以下同文。
var result = context.SaveChanges();

出力結果は・・・

BEGIN TRANSACTION ISOLATION LEVEL READ COMMITTED
DELETE FROM account
        WHERE user_id = $1
parameters: $1 = '6'
COMMIT

でした！

続く。。。

その他リファレンス

qiita.com
docs.microsoft.com