Entity Framework Core で色々な SQL を投げてみる 1
- はじめに
- メモ
- SELECT
- 全件取得
- カラムを指定して検索
- [LIMIT] 条件に合致するレコードを一件だけ取得する
- [LIMIT] 条件に合致するレコードを三件(二件以上)取得する
- [DISTINCT] ジャンルが重複するデータを除く
- [演算子] SELECT で演算子を使う
- [演算子] WHERE で演算子を使う
- [COUNT] レコード数をカウントする
- [COUNT] ジャンルが NULL でないレコード数をカウントする
- [AVG] 価格の平均を求める
- [MAX] 価格の最大値を求める
- [MIN] 価格の最小値を求める
- [GROUP BY???] ジャンルでグループ分けする
- [HAVING] グループ化したレコードをフィルタリングする
- [ORDER BY] レコードをリリース日でソートする(昇順)
- [ORDER BY] レコードをリリース日でソートする(降順)
はじめに
- ASP.NET Core のプロジェクトに EntityFrameworkCore で Model を追加してみた話
- Entity Framework Core のスキャフォールド・マイグレーションで生成されたものを見たい
- 【ASP.NET Core】【Entity Framework Core】PostgreSQL に接続してみる
さて、 PostgreSQL に接続できたことだし、 DbContext を追うぞ~と思ったのですが、あまりに知識が雑すぎたためどこから手を付けていいかもわからない状態に。。。orz
引き続き追いかけてはいくのですが、まずは下記の書籍を参考に、 Entity Framework Core で色々な SQL 文を発行してみたいと思います。
まずは SELECT から。
メモ
とその前に小ネタをメモっておきます。
Model クラスとテーブルに差異がある場合
- Model クラスに DB のテーブルに無い値(プロパティ)がある場合 → そのプロパティを使っている、使っていないにかかわらず Exception が発生します。
- DB のテーブルには存在するが、 Model クラスにプロパティが無い値(カラム)がある場合 → 問題なし。ただ値が取れないだけ。
また、 テーブルのカラム名と Model クラスのプロパティの名前が異なる場合、 [ Column("")] で指定することができます。
SELECT
全件取得
Book テーブルのデータを全件取得します。
コード
await _context.Book.ToListAsync();
発行される SQL
SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail" FROM "Book" AS b
コンソールに出力されるのが便利ですね。
カラムを指定して検索
カラム BookId のみを指定して取得してみます。
コード
await _context.Book.Select(b => b.BookId).ToListAsync();
発行される SQL
SELECT b."BookId" FROM "Book" AS b
[WHERE] 価格が 3000 円以上のレコードを取得する
コード
await _context.Book.Where(b => b.Price >= 3000).ToListAsync();
発行される SQL
SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail" FROM "Book" AS b WHERE b."Price" >= 3000.0
[LIMIT] 条件に合致するレコードを一件だけ取得する
コード
_context.Book.FirstOrDefault(b => b.Price >= 3000);
発行される SQL
SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail" FROM "Book" AS b WHERE b."Price" >= 3000.0 LIMIT 1
[LIMIT] 条件に合致するレコードを三件(二件以上)取得する
コード
await _context.Book.Where(b => b.Price >= 3000).Take(3).ToListAsync();
発行される SQL
SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail" FROM "Book" AS b WHERE b."Price" >= 3000.0 LIMIT @__p_0
結果としては FirstOrDefault と同じように LIMIT が使われるのですが、パラメーターが不思議な値になっています。
(なお Take の引数を変えてもコンソール出力の内容は変わりませんでした)
コンソール出力後に置き換えられるのでしょうか。
※なお TakeLast はサポート外らしく、使用すると Exception が発生します。
[DISTINCT] ジャンルが重複するデータを除く
コード
await _context.Book.Select(b => b.Genre).Distinct().ToListAsync();
発行される SQL
SELECT DISTINCT b."Genre" FROM "Book" AS b
[演算子] SELECT で演算子を使う
コード
await _context.Book.Select(b => b.Price * 1.08m).ToListAsync();
発行される SQL
SELECT b."Price" * 1.08 FROM "Book" AS b
[演算子] WHERE で演算子を使う
コード
await _context.Book.Where(b => (b.Price * 1.08m) >= 3000.0m).ToListAsync();
発行される SQL
SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail" FROM "Book" AS b WHERE (b."Price" * 1.08) >= 3000.0
[COUNT] レコード数をカウントする
コード
await _context.Book.CountAsync();
発行される SQL
SELECT COUNT(*)::INT FROM "Book" AS b
[COUNT] ジャンルが NULL でないレコード数をカウントする
コード
await _context.Book.CountAsync(b => b.Genre != null);
発行される SQL
SELECT COUNT(*)::INT FROM "Book" AS b WHERE b."Genre" IS NOT NULL
「 SELECT COUNT("Genre") FROM "Book" 」とできると、同様のことが WHERE 無しでできるのですが、少なくとも CountAsync() を使う場合、「 COUNT(*)::INT 」となってしまうようです。
[SUM] 価格の合計を求める
コード
await _context.Book.SumAsync(b => b.Price);
発行される SQL
SELECT SUM(b."Price") FROM "Book" AS b
[AVG] 価格の平均を求める
コード
await _context.Book.AverageAsync(b => b.Price);
発行される SQL
SELECT AVG(b."Price") FROM "Book" AS b
[MAX] 価格の最大値を求める
コード
await _context.Book.MaxAsync(b => b.Price);
発行される SQL
SELECT MAX(b."Price") FROM "Book" AS b
[MIN] 価格の最小値を求める
コード
await _context.Book.MinAsync(b => b.Price);
発行される SQL
SELECT MIN(b."Price") FROM "Book" AS b
[GROUP BY???] ジャンルでグループ分けする
コード
await _context.Book.GroupBy(b => b.Genre).ToListAsync();
発行される SQL
SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail" FROM "Book" AS b ORDER BY b."Genre"
ORDER BY ???
発行したかったのは
SELECT "Genre" FROM "Book" GROUP BY "Genre"
です。
結果としてはどちらもジャンルでグループ分けできているようなのですが。。。
なお、当然ながら発行された SQL の内容を PgAdmin で実行すると、ジャンルでソートされた結果が表示されるだけです。
と思ったら、コンソールに下記の警告が出力されていました。
warn: Microsoft.EntityFrameworkCore.Query[20500] The LINQ expression 'GroupBy([b].Genre, [b])' could not be translated and will be evaluated locally.
どうやら、上記の SQL が発行される前に C# 側で何か処理を行っているようです。
同じようなことを Issue で挙げている方もいたようですが。。。
https://github.com/aspnet/EntityFrameworkCore/issues/9964
これについては、次回以降でもう少し見てみたいと思います(挫折しなければ)。
[HAVING] グループ化したレコードをフィルタリングする
コード
await _context.Book.GroupBy(b => b.Genre) .Where(b => string.IsNullOrEmpty(b.Key) == false) .ToListAsync();
発行される SQL
SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail" FROM "Book" AS b ORDER BY b."Genre"
あれ?発行される SQL がさっきと変わってない(´・ω・`)
結局 GroupBy がそのまま変換されないため、そこに対する Where も変換はされないようです(結果には反映されますが)。
ちなみに Where についても警告が出力されています。
warn: Microsoft.EntityFrameworkCore.Query[20500] The LINQ expression 'GroupBy([b].Genre, [b])' could not be translated and will be evaluated locally. warn: Microsoft.EntityFrameworkCore.Query[20500] The LINQ expression 'where (IsNullOrEmpty([b].Key) == False)' could not be translated and will be evaluated locally.
[参照]
[ORDER BY] レコードをリリース日でソートする(昇順)
コード
await _context.Book.OrderBy(b => b.ReleaseDate).ToListAsync();
発行される SQL
SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail" FROM "Book" AS b ORDER BY b."ReleaseDate"
GroupBy でも(なぜか) ORDER BY が発行されていたので違いがよくわからない感じになっていますが(苦笑)、割とそのまま書けますね。
[ORDER BY] レコードをリリース日でソートする(降順)
コード
await _context.Book.OrderByDescending(b => b.ReleaseDate).ToListAsync();
発行される SQL
SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail" FROM "Book" AS b ORDER BY b."ReleaseDate" DESC
長くなってきたのでいったん切ります。
次回は SQL ゼロからはじめるデータベース操作 の六章ぐらいから。