EF Coreのクエリ式

はじめに
---
巷には、EF Core（Entity Framework Core）についてのリファレンスが少ないと感じます。そもそも数少ないEF Coreの記事の中で、**メソッド式**で書かれたものは見かけますが**クエリ式**は殆ど見かけないように思います。ここではEF Coreの**クエリ式**について、初心者である自分が試したことをまとめておきたいと思います。

コンテキストキーワード
---
EF Coreのクエリ式にはC#のコンテキストキーワードを使用します。`select`、`from`、`join`、`group`～`by`など、コード内にまるでSQL文のような文字列が踊ることになります。こんな書き方、他の言語にあるでしょうか？技術屋オタクが趣味で作ったとしか思えない:sweat_smile:まったくもってC#は面白い言語です。

https://learn.microsoft.com/ja-jp/dotnet/csharp/language-reference/keywords/

https://www.weblio.jp/content/%E3%82%B3%E3%83%B3%E3%83%86%E3%82%AD%E3%82%B9%E3%83%88%E3%82%AD%E3%83%BC%E3%83%AF%E3%83%BC%E3%83%89

モデルクラスとサンプルデータ
---
今回操作する対象のモデルクラスは以下の3つとなります。

```C#
using System.ComponentModel.DataAnnotations.Schema;

// 取引を記録するためのテーブル
[Table("Trade")]
internal class Trade
{
    public int Id { get; set; }
    public DateTime TradeDate { get; set; }
    public string TradeNo { get; set; }
    public List<Invoice> Invoices { get; set; }
}

// 取引の請求書を記録するためのテーブル
[Table("Invoice")]
internal class Invoice
{
    public int Id { get; set; }
    public DateTime InvoiceDate { get; set; }
    public string InvoiceNo { get; set; }
    public Trade Trade { get; set; }
    public List<InvoiceDetail> InvoiceDetails { get; set; }
}

// 請求書の商品明細を記録するための請求書の子テーブル
[Table("InvoiceDetail")]
internal class InvoiceDetail
{
    public int Id { get; set; }
    public string CategoryCode { get; set; }
    public string ItemName { get; set; }
    public int Quantity { get; set; }
    public decimal UnitPrice { get; set; }
    public int InvoiceId { get; set; }
    public Invoice Invoice { get; set; }
}
```
サンプルデータはSQLiteで作りました。EF CoreでSQLiteを操作するには、`Microsoft.EntityFrameworkCore.Sqlite`のインストールが必要です。
また、以下のツールはSQLiteの管理に便利です。

https://pupsqlite.softonic.jp/download

<details><summary>テーブル構造とサンプルデータは長いので折りたたみます。</summary>

以下がテーブル構造です。
### Tradeテーブル（取引）
| #  | カラム名   | データ型 | 主キー | 重複 | NULL | 
|----|------------|----------|--------|------|------|
| 1  | Id         | INTEGER  | ✓      | NG    | NG    |
| 2  | TradeDate  | DATETIME |        | OK    | OK    | 
| 3  | TradeNo    | TEXT     |        | OK    | OK   | 
### Invoiceテーブル（請求書）
| #  | カラム名   | データ型 | 主キー | 重複 | NULL | 
|----|------------|----------|--------|------|------|
| 1  | Id         | INTEGER  | ✓      | NG    | NG    |
| 2  | InvoiceDate| DATETIME |        | OK    | OK    | 
| 3  | InvoiceNo  | TEXT     |        | OK    | OK    | 
| 4  | TradeId    | INTEGER  |        | OK    | OK    |
### InvoiceDetailテーブル（請求書明細）
| #  | カラム名     | データ型 | 主キー | 重複 | NULL | 
|----|--------------|----------|--------|------|------|
| 1  | Id           | INTEGER  | ✓      | NG    | NG    |
| 2  | CategoryCode | TEXT     |        | OK    | OK    |
| 3  | ItemName     | TEXT     |        | OK    | OK    |
| 4  | Quantity     | INTEGER  |        | OK    | OK    |
| 5  | UnitPrice    | NUMERIC  |        | OK    | OK    |
| 6  | InvoiceId    | INTEGER  |        | OK    | OK    |


以下がサンプルデータになります。

### Tradeテーブル（取引）
| ROWID | Id | TradeDate  | TradeNo |
|-------|----|------------|---------|
| 0     | 0  | 2023-12-01 | ABC-001 |
| 1     | 1  | 2023-12-05 | ABC-002 |
| 2     | 2  | 2023-12-22 | ABC-003 |
### Invoiceテーブル（請求書）
| ROWID | Id | InvoiceDate | InvoiceNo      | TradeId |
|-------|----|-------------|----------------|---------|
| 1     | 1  | 2023-12-20  | IV-23-1220-02  | 2       |
| 2     | 2  | 2023-12-20  | IV-23-1220-01  | 1       |
### InoiceDetailテーブル（請求書明細）
| ROWID | Id | CategoryCode | ItemName | Quantity | UnitPrice | InvoiceId |
|-------|----|--------------|----------|----------|-----------|-----------|
| 1     | 1  | A            | Item01   | 100      | 1000      | 2         |
| 2     | 2  | A            | Item02   | 75       | 1050      | 2         |
| 3     | 3  | B            | Item03   | 90       | 900       | 2         |
| 4     | 4  | B            | Item04   | 110      | 1000      | 2         |
| 5     | 5  | A            | Item01   | 110      | 1000      | 1         |

</details>

以下はDB接続に必要な`DbContext`です。接続文字列は`.db`ファイルが実行ファイルと同じ場所にあればファイル名だけで済みますので非常に簡単です。

```C#
using Microsoft.EntityFrameworkCore;

internal class HogeDbContext : DbContext
{
    public DbSet<Invoice> Invoices { get; set; }
    public DbSet<InvoiceDetail> InvoiceDetails { get; set; }
    public DbSet<Trade> Trades { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    {
        // データベースへの接続文字列
        string connectionString = "Data Source=Hoge.db;";

        optionsBuilder.UseSqlite(connectionString);
    }
}
```
これで準備は整いました。
以下で主なSQL文に対応するEF Coreの書き方を記載していきます。

基本的な構文
---
以下はクエリ式でデータを取得する基本的な構文の例です。まずは`HogeDbContext`のインスタンスを生成します。そして、`HogeDbContext`
のメンバである3つのモデルのコレクション（`Trades`、`Invoices`、`InvoiceDetails`）を使って操作をしていきます。

```C#
using (var context = new HogeDbContext())
{
    // Tradeテーブルの全てのレコードを取得
    var query = from trade in context.Trades
                select trade;

    // 以下はクエリ結果の出力
    foreach (var trade in query)
    {
        Console.WriteLine(trade.Id + "\t" +
                          trade.TradeDate + "\t" +
                          trade.TradeNo);
    }
}

// 実際のところ、以下でいけるんですが、、、
// var query = context.Trades;
```
まずは`SELECT`の表現
---

以下、見た目はほとんどSQL文のようです。

```C#
var query = from trade in context.Trades
            select trade;
```
これは`Trade`クラスのコレクション（`IQueryable<Trade>`）が返り、更新追跡対象となります。
これだとテーブル内の全てのレコードを取得することになり、実際はここに絞り込みを加えることでしょう。絞り込みは`where`句を使います。
```C#
var query = from trade in context.Trades
            // where句を挿入するだけ
            where trade.TradeDate >= new DateTime(2023, 12, 10)
            
            select trade;
```
`where`句の後ろは**式ツリー**で、SQL文の`WHERE`検索条件のテキストに変換されます。


また、以下のように`select`内で`Trade`型の`trade`インスタンスを使ってプロパティを指定することで、読み取り専用の匿名型を射影することができます。この場合は更新追跡の対象にはなりません。
```C#
var query = from trade in context.Trades
            where trade.TradeDate >= new DateTime(2023, 12, 10)
            select new
            {
                trade.Id,
                trade.TradeDate,
                trade.TradeNo
            };
```

INNER JOINの表現
---
SQLで`INNER JOIN`を使って`SELECT`するケース、例えば、取引（Trade）のIdが1のものに関わる請求書（Invoice）の内容をその明細（InvocieDetail）も含めて取得したい場合、以下のように書きます。
```C#
 var query = from trade in context.Trades
             join invoice in context.Invoices on trade.Id equals invoice.Trade.Id
             join detail in context.InvoiceDetails on invoice.Id equals detail.InvoiceId
             where trade.Id == 1
             select new
             {
                 TradeNo = trade.TradeNo,
                 InvoiceNo = invoice.InvoiceNo,
                 Detail = new
                 {
                     CategoryCode = detail.CategoryCode,
                     ItemName = detail.ItemName,
                     Quantity = detail.Quantity,
                     UnitPrice = detail.UnitPrice,
                     Amount = detail.Quantity * detail.UnitPrice
                 }
             };
```
SQLを理解していれば、この書き方も理解できると思います。並び順が異なるだけで、ほとんどSQL文と変わりありません。
射影されたデータを取得するには、`select`内で生成した変数名を使います。

```C#
foreach (var result in query)
{
    Console.WriteLine($"Trade No: {result.TradeNo}, " +
        $"Invoice No: {result.InvoiceNo}, Category: {result.Detail.CategoryCode}, " +
        $"Item: {result.Detail.ItemName}, Quantity: {result.Detail.Quantity}, " +
        $"Unit Price: {result.Detail.UnitPrice}, Amount: {result.Detail.Amount}");
}
```

また、`join`を使わずに`from`だけでも表現できます。

```C#
var query = from trade in context.Trades
            where trade.Id == 1
            from invoice in trade.Invoices
            from detail in invoice.InvoiceDetails
            select new
            {
                TradeNo = trade.TradeNo,
                InvoiceNo = invoice.InvoiceNo,
                CategoryCode = detail.CategoryCode,
                ItemName = detail.ItemName,
                Quantity = detail.Quantity,
                UnitPrice = detail.UnitPrice,
                Amount = detail.Quantity * detail.UnitPrice
            };
```
これは、`Trade`クラスが`Invoice`クラス型のリストである`Invoices`ナビゲーションプロパティを持ち、対する`Invoice`クラスは`Trade`クラス型の`Trade`ナビゲーションプロパティを持っているので、両者の１対多の関係をEF Coreが解釈できるためです（`Invoice`と`InvoiceDetail`との関係も同様）。また、今回の`select`内で生成する変数は請求書明細の項目をネストさせずに直接変数に代入しています。

LEFT JOINの表現
---
次に、`LEFT JOIN`を使って`SELECT`するケースです。

```C#
// joinを使う場合
var query = from trade in context.Trades
            where trade.Id == 1
            join invoice in context.Invoices on trade.Id equals invoice.Trade.Id into tradeInvoices
            from invoice in tradeInvoices.DefaultIfEmpty()
            join detail in context.InvoiceDetails on invoice.Id equals detail.InvoiceId into invoiceDetails
            from detail in invoiceDetails.DefaultIfEmpty()
            select new
            {
                TradeNo = trade.TradeNo,
                InvoiceNo = invoice != null ? invoice.InvoiceNo : "N/A",
                CategoryCode = detail != null ? detail.CategoryCode : "N/A",
                ItemName = detail != null ? detail.ItemName : "N/A",
                Quantity = detail != null ? (int?)detail.Quantity : null,
                UnitPrice = detail != null ? (decimal?)detail.UnitPrice : null,
                Amount = detail != null ? (decimal?)(detail.Quantity * detail.UnitPrice) : null
            };
```

```C#
// fromを使う場合
var query = from trade in context.Trades
            where trade.Id == 1
            from invoice in trade.Invoices.DefaultIfEmpty()
            from detail in invoice.InvoiceDetails.DefaultIfEmpty()
            select new
            {
                TradeNo = trade.TradeNo,
                InvoiceNo = (invoice != null) ? invoice.InvoiceNo : "N/A",
                CategoryCode = (detail != null) ? detail.CategoryCode : "N/A",
                ItemName = (detail != null) ? detail.ItemName : "N/A",
                Quantity = (detail != null) ? (int?)detail.Quantity : null,
                UnitPrice = (detail != null) ? (decimal?)detail.UnitPrice : null,
                Amount = (detail != null) ? (decimal?)(detail.Quantity * detail.UnitPrice) : null
            };
```
いずれも、`DefaultIfEmpty()`を使って関連する`Invoice`と`InvoiceDetail`が存在しない場合に`null`を返すことで、`LEFT JOIN`を実現しています。

GROUP BYの表現
---
更に、`GROUP BY`を使って`SELECT`するケースです。
`TradeNo`、`InoiceNo`、`CategoryCode`をキーとしてグループ化しています。
```C#
var query = from trade in context.Trades
            where trade.Id == 1
            from invoice in trade.Invoices
            from detail in invoice.InvoiceDetails
            group detail by new { trade.TradeNo, invoice.InvoiceNo, detail.CategoryCode } into grouped
            select new
            {
                TradeNo = grouped.Key.TradeNo,
                InvoiceNo = grouped.Key.InvoiceNo,
                CategoryCode = grouped.Key.CategoryCode,
                TotalQuantity = grouped.Sum(g => g.Quantity),
                TotalAmount = grouped.Sum(g => g.Quantity * g.UnitPrice)
            };
```

`into`句で出力される`grouped`はグループ化された項目のコレクションとなります。`select`内の`grouped.Key`はグループ化のキーである`TradeNo`、`InvoiceNo`、`CategoryCode`の3つの値を持つ匿名型のオブジェクトです。このキーにアクセスするには、`grouped.Key.TradeNo`や`grouped.Key.InvoiceNo`といった形で参照します。

GROUP BYの結果の合計を算出
---
クエリ式ではありませんが、グループ化後の数量と金額の合計を算出するには、LINQで簡単に書けます。

```C#
// グループごとの結果を取得
var groupedResults = query.ToList();

// 全体の合計を算出
var totalQuantitySum = groupedResults.Sum(g => g.TotalQuantity);
var totalAmountSum = groupedResults.Sum(g => g.TotalAmount);
```

UNIONの表現
---
こちらも手前の準備はクエリ式ですが、`UNION`自体はクエリ式がありませんのでメソッド式で表現します。

```C#
// 2つの同じ構造の匿名型
var query1 = from trade in context.Trades
             where trade.Id == 1
             select new
             {
                Id = trade.Id,
                TradeNo = trade.TradeNo
             };
var query2 = from trade in context.Trades
             where trade.Id == 2
             select new
             {
                Id = trade.Id,
                TradeNo = trade.TradeNo
             };

// UNIONの場合（重複の除外）
var unionQuery = query1.Union(query2);
// UNION ALLの場合（ありのまま）
var unionAll = query1.Concat(query2);

```
`query1`と`query2`で選択される列が一致している必要があることは、SQLで`UNION`を書くときと一緒です。

さいごに
---
以上、EF Coreのクエリ式を簡単にまとめてみました。SQLが書ければ容易に理解でき、何も難しいことはありません。
可読性の良いクエリ式は、C#の優れた部分の一つだと、この記事を書いて改めて思いました。

:laughing::santa_tone1::laughing::santa_tone1::laughing::santa_tone1: