1月15日開催!現年収非公開で企業からスカウトをもらってみませんか?PR

転職ドラフトでリアルな市場価値を測る。レジュメをもとに、企業から年収とミッションが提示されます。

1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

C#Advent Calendar 2023

Day 22

EF Coreのクエリ式

Last updated at Posted at 2023-12-22

はじめに

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

コンテキストキーワード

EF Coreのクエリ式にはC#のコンテキストキーワードを使用します。selectfromjoingroupbyなど、コード内にまるでSQL文のような文字列が踊ることになります。こんな書き方、他の言語にあるでしょうか?技術屋オタクが趣味で作ったとしか思えない:sweat_smile:まったくもって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つのモデルのコレクション(TradesInvoicesInvoiceDetails)を使って操作をしていきます。

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ナビゲーションプロパティを持っているので、両者の1対多の関係をEF Coreが解釈できるためです(InvoiceInvoiceDetailとの関係も同様)。また、今回の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()を使って関連するInvoiceInvoiceDetailが存在しない場合にnullを返すことで、LEFT JOINを実現しています。

GROUP BYの表現

更に、GROUP BYを使ってSELECTするケースです。
TradeNoInoiceNoCategoryCodeをキーとしてグループ化しています。

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はグループ化のキーであるTradeNoInvoiceNoCategoryCodeの3つの値を持つ匿名型のオブジェクトです。このキーにアクセスするには、grouped.Key.TradeNogrouped.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);

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

さいごに

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

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

1
0
1

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up

Qiita Advent Calendar is held!

Qiita Advent Calendar is an article posting event where you post articles by filling a calendar 🎅

Some calendars come with gifts and some gifts are drawn from all calendars 👀

Please tie the article to your calendar and let's enjoy Christmas together!

1
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?