vaguely

和歌山に戻りました。ふらふらと色々なものに手を出す毎日。

Entity Framework Core で色々な SQL を投げてみる 1

はじめに

さて、 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 ゼロからはじめるデータベース操作 の六章ぐらいから。