vaguely

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

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

Entity Framework Core

はじめに

SELECT 文の続きです。

今回も SQL ゼロからはじめるデータベース操作 を参考に、色々試してみますよ。

SELECT 2

コード

await _context.Book.Where(b => b.Name.StartsWith("Entity")).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE b."Name" LIKE 'Entity%'

[LIKE]中間一致で検索する

コード

await _context.Book.Where(b => b.Name.Contains("Framework")).ToListAsync();

発行される SQL

SELECT b."BookId", b."Name", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE STRPOS(b."Name", 'Framework') > 0

むむむ。発行したかった SQL は「 WHERE b."Genre" LIKE '%Entity%' 」だったのですが。。。

あくまで %QUERY% にこだわるなら、もう少し別の方法もあるかもしれませんね。

[参照]

コード

await _context.Book.Where(b => b.Name.EndsWith("Action")).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE RIGHT(b."Name", LENGTH('Action')) = 'Action'

むぅ。。。これも違う。

結果が正しいので良い。。。のかな？

正規表現で検索する

コード

System.Text.RegularExpressions.Regex regex1 = new System.Text.RegularExpressions.Regex("Action$");
await _context.Book.Where(b => regex1.IsMatch(b.Name)).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b

Where ごと消えた／(^o^)＼

この辺は発行された SQL の内容で確認するのが難しそうですねぇ。。。

warn: Microsoft.EntityFrameworkCore.Query[20500]
      The LINQ expression 'where __regex1_0.IsMatch([b].Name)' could not be translated and will be evaluated locally.

[BETWEEN] 価格帯で検索する

コード

await _context.Book.Where(b => b.Price >= 500m && b.Price <= 5000m).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE (b."Price" >= 500.0) AND (b."Price" <= 5000.0)

ですよね～、という結果ですが、どうやら Linq に BETWEEN 的なものはなさそうです。

[WHERE IN] 価格リストに合致するレコードを検索する

コード

await _context.Book.Where(b => new []{10m, 500m, 7000m}.Contains(b.Price)).ToListAsync();

発行される SQL

SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE b."Price" IN (10.0, 500.0, 7000.0)

なお、下記のように配列部分を変更しても同じ結果となりました。

var prices = new[] {10m, 500m, 7000m};
await _context.Book.Where(b => prices.Contains(b.Price)).ToListAsync();

[EXISTS] 価格が 5000 円以上のレコードが存在するか

コード

_context.Book.Any(b => b.Price >= 5000m);

発行される SQL

 SELECT CASE
          WHEN EXISTS (
              SELECT 1
              FROM "Book" AS b
              WHERE b."Price" >= 5000.0)
          THEN TRUE::bool ELSE FALSE::bool
      END

おっと。ここで CASE が登場しましたね。

THEN 以降は、 C# と SQL で型を合わせるためなのでしょうか。

[CASE] CASE 式を使う

コード

_context.Book.Select(b => (b.Price >= 5000m)? 1:0 ).ToList();

発行される SQL

SELECT CASE
          WHEN b."Price" >= 5000.0
          THEN 1 ELSE 0
      END
      FROM "Book" AS b

Linq には Case が無いようです。

そのため Entity Framework Core でも使えない。。。と思いきや、参考演算子にすると CASE 式に変換されれるようです。

なお上記サンプルの雑さは見ないことにしてくださいorz

[UNION ALL] 2 つのテーブルの検索結果を足し合わせる(重複あり)

準備

Book テーブルと同じレイアウトで Book2 というテーブルを作ります。

Book2
CREATE TABLE "Book2" (
"BookId" integer PRIMARY KEY,
"Genre" text,
"Name" text,
"Price" numeric,
"ReleaseDate" timestamp without time zone,
"Thumbnail" bytea)

ここにいくつかのレコードは Book と重複する内容で、雑にレコードを追加します。

C# 側でも、 Model クラスと Context クラスへの追加を行います。

Book2.cs
using System;

namespace EfCoreNpgsqlSample.Models
{
    public class Book2
    {
        [Key]
        public int BookId { get; set; }
        public string Name { get; set; }
        public DateTime ReleaseDate { get; set; }
        public string Genre { get; set; }
        public decimal Price { get; set; }
        public byte[] Thumbnail { get; set; }
    }
}
  • BookId に [Key] がついているのは、 CREATE TABLE 時に BookId を PRIMARY KEY として登録したためです(外すとエラーになります)。
EfCoreNpgsqlSampleContext.cs
using Microsoft.EntityFrameworkCore;

namespace EfCoreNpgsqlSample.Models
{
    public class EfCoreNpgsqlSampleContext: DbContext
    {
        public EfCoreNpgsqlSampleContext(DbContextOptions< EfCoreNpgsqlSampleContext> options)
            : base(options)
        {            
        }  
        public DbSet< Book> Book { get; set; }

        public DbSet< Book2> Book2 { get; set; }

    }
}

で、 Book 、 Book2 への検索結果を足し合わせたい、というのが目標です。

SQL はこちら。

SELECT "BookId", "Genre", "Name", "Price", "ReleaseDate"
FROM "Book"
UNION ALL
SELECT "BookId", "Genre", "Name", "Price", "ReleaseDate"
FROM "Book2"

SQL はこれで良いのですが、 C# では Book と Book2 が別のクラスとして扱われるので、これをそのまま実行することができません。

Book2 の結果を受け取った後、 Book に変換したあと Concat で結合します。

コード

IQueryable< Book> books1 = _context.Book
    .Where(b => b.Name != null);
IQueryable< Book> books2 = _context.Book2
    .Where(b => b.Name != null)
    .Select(b => new Book
    {
        BookId = b.BookId,
        Name = b.Name,
        Genre = b.Genre,
        Price = b.Price,
        ReleaseDate = b.ReleaseDate
    });
await books1.Concat(books2).ToListAsync();

Book テーブルと Book2 テーブルに対してそれぞれ SELECT を実行しているため、発行される SQL も 2 つに分かれています。

発行される SQL

info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (7ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT b."BookId", b."Genre", b."Name", b."Price", b."ReleaseDate", b."Thumbnail"
      FROM "Book" AS b
      WHERE b."Name" IS NOT NULL
info: Microsoft.EntityFrameworkCore.Database.Command[20101]
      Executed DbCommand (3ms) [Parameters=[], CommandType='Text', CommandTimeout='30']
      SELECT b0."BookId", b0."Name", b0."Genre", b0."Price", b0."ReleaseDate"
      FROM "Book2" AS b0
      WHERE b0."Name" IS NOT NULL

[UNION] 2 つのテーブルの検索結果を足し合わせる(重複なし)

Concat で結合するだけだと、重複するデータがある場合もそのまま出力されます。

重複するデータを取り除く方法として Distinct があります。

が、そのままだと Book テーブルと Book2 テーブルに重複があっても別物として扱われます。

ということで、 Book クラスの Equals をオーバーライドします。

Book.cs

using System;
using System.ComponentModel.DataAnnotations;

namespace EfCoreNpgsqlSample.Models
{
    public class Book
    {
        [Key]
        public int BookId { get; set; }
        public string Name { get; set; }
        [DataType(DataType.Date)]
        public DateTime ReleaseDate { get; set; }
        public string Genre { get; set; }
        public decimal Price { get; set; }
        public byte[] Thumbnail { get; set; }

        public override bool Equals(object obj)
        {
            var compareBook = obj as Book;
            if (compareBook == null)
            {
                return false;
            }
            return compareBook.BookId == BookId;
        }
        public override int GetHashCode()
        {
            return BookId;
        }
    }
}

GetHashCode で、戻り値が static じゃないよ！と警告が出たりしていますが、今回は見なかったことに。。。(良い子はマネしない)

また、今回は BookId のみで比較していますが、実際の用途的には Name や Genre で比較したほうが良さそうです。

なお SQL の UNION では「 SELECT "BookId", "Genre", "Name", "Price", "ReleaseDate" ～」なら BookId 、 Genre 、 Name 、 Price 、 ReleaseDate の全部が一致しているかどうかを見ているようです。

あとは　Distinct で重複データが取り除けます。

await books1.Concat(books2).Distinct().ToListAsync();

[INTERSECT] 2 つのテーブルの共通部分を取り出す

Linq にもそのままズバリ Intersect が存在するため、これを使えば実現できます。

コード

await books1.Intersect(books2).ToListAsync();

先ほどと同じく SQL には反映されないため、発行された SQL は省略します。

[EXCEPT] 2 つのテーブルで共通しないレコードのみを取り出す

こちらも Except が存在するため楽に実現できますね。

コード

await books1.Except(books2).ToListAsync();

注意点？としては、上記の例では Book テーブルの持つレコードがベースとなるため、 Book1 にのみ共通しないレコードが存在する場合、 0 件で返ってきます。

[INNER JOIN] 複数のテーブルを参照する(内部結合)

準備

Author テーブルを作り、 Book テーブルに AuthorId 列を追加します。

Author
CREATE TABLE "Author" (
"AuthorId" integer PRIMARY KEY,
"Name" text)

Book

ALTER TABLE "Book" 
ADD COLUMN "AuthorId" integer

C# 側でも、 Model クラスと Context クラスへの追加を行います。

Author.cs
using System.ComponentModel.DataAnnotations;

namespace EfCoreNpgsqlSample.Models
{
    public class Author
    {
        [Key]
        public int AuthorId { get; set; }
        public string Name { get; set; }
    }
}
Book.cs
using System;
using System.ComponentModel.DataAnnotations;

namespace EfCoreNpgsqlSample.Models
{
    public class Book
    {
        [Key]
        public int BookId { get; set; }

        public int AuthorId { get; set; }
        ～省略～
    }
}
EfCoreNpgsqlSampleContext.cs
using Microsoft.EntityFrameworkCore;

namespace EfCoreNpgsqlSample.Models
{
    public class EfCoreNpgsqlSampleContext: DbContext
    {
        public EfCoreNpgsqlSampleContext(DbContextOptions options)
            : base(options)
        {
            
        }
        
        public DbSet< Book> Book { get; set; }
        public DbSet< Book2> Book2 { get; set; }
        public DbSet< Author> Author { get; set; }
    }
}

Linq には Join が用意されているため、これを使います。

コード

// 戻り値の型は IQueryable< anonymous type>
from book in _context.Book
        join author in _context.Author
            on book.AuthorId equals author.AuthorId
        select new
        {
            BookId = book.BookId,
            AuthorId = author.AuthorId,
            BookName = book.Name,
            AuthorName = author.Name,
        };

発行される SQL

SELECT book."BookId", author."AuthorId", book."Name" AS "BookName", author."Name" AS "AuthorName"
      FROM "Book" AS book
      INNER JOIN "Author" AS author ON book."AuthorId" = author."AuthorId"

無名クラスとして結合した値を受け取ることができるため、一時的に使うだけならこの形が便利ですね。

なお唐突にクエリ式になったのは、 Join についてはこっちの方が理解しやすそうだったためです。

[LEFT OUTER JOIN] 複数のテーブルを参照する(外部結合)

※ 2019/02/26 23:30 更新

Book テーブルに Author テーブルに存在しない AuthorId を設定した場合もエラーが起きないよう修正しました。

コード

var results = from book in _context.Book
                join author in _context.Author
                    on book.AuthorId equals author.AuthorId into gj
                from ba in gj.DefaultIfEmpty()
                select new
                {
                    BookId = book.BookId,
                    AuthorId = book.AuthorId,
                    BookName = book.Name,
                    AuthorName = (ba == null)? "": ba.Name,
                };

発行される SQL

SELECT book."BookId", book."AuthorId", book."Name" AS "BookName", CASE
          WHEN author."AuthorId" IS NULL
          THEN '' ELSE author."Name"
      END AS "AuthorName"
      FROM "Book" AS book
      LEFT JOIN "Author" AS author ON book."AuthorId" = author."AuthorId"

SQL に反映されていない。。。

が、これで Book テーブルにあって、 Author テーブルに登録されていない AuthorId を持つレコードが結果に出力されるようになりました。

またまた長くなってきたので切ります。