はじめに
巷には、EF Core（Entity Framework Core）についてのリファレンスが少ないと感じます。そもそも数少ないEF Coreの記事の中で、メソッド式で書かれたものは見かけますがクエリ式は殆ど見かけないように思います。ここではEF Coreのクエリ式について、初心者である自分が試したことをまとめておきたいと思います。
コンテキストキーワード
EF Coreのクエリ式にはC#のコンテキストキーワードを使用します。select、from、join、group～byなど、コード内にまるでSQL文のような文字列が踊ることになります。こんな書き方、他の言語にあるでしょうか？技術屋オタクが趣味で作ったとしか思えない
まったくもってC#は面白い言語です。
モデルクラスとサンプルデータ
今回操作する対象のモデルクラスは以下の3つとなります。
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の管理に便利です。
テーブル構造とサンプルデータは長いので折りたたみます。
以下がテーブル構造です。
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 |
以下はDB接続に必要なDbContextです。接続文字列は.dbファイルが実行ファイルと同じ場所にあればファイル名だけで済みますので非常に簡単です。
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）を使って操作をしていきます。
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文のようです。
var query = from trade in context.Trades
select trade;
これはTradeクラスのコレクション（IQueryable<Trade>）が返り、更新追跡対象となります。
これだとテーブル内の全てのレコードを取得することになり、実際はここに絞り込みを加えることでしょう。絞り込みはwhere句を使います。
var query = from trade in context.Trades
// where句を挿入するだけ
where trade.TradeDate >= new DateTime(2023, 12, 10)
select trade;
where句の後ろは式ツリーで、SQL文のWHERE検索条件のテキストに変換されます。
また、以下のようにselect内でTrade型のtradeインスタンスを使ってプロパティを指定することで、読み取り専用の匿名型を射影することができます。この場合は更新追跡の対象にはなりません。
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）も含めて取得したい場合、以下のように書きます。
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内で生成した変数名を使います。
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だけでも表現できます。
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するケースです。
// 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
};
// 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をキーとしてグループ化しています。
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で簡単に書けます。
// グループごとの結果を取得
var groupedResults = query.ToList();
// 全体の合計を算出
var totalQuantitySum = groupedResults.Sum(g => g.TotalQuantity);
var totalAmountSum = groupedResults.Sum(g => g.TotalAmount);
UNIONの表現
こちらも手前の準備はクエリ式ですが、UNION自体はクエリ式がありませんのでメソッド式で表現します。
// 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#の優れた部分の一つだと、この記事を書いて改めて思いました。
