Sql => Linq 変換 対応表 in C#

あのSQLをLINQで表現するときにどう書くんだっけ？の自分用備忘録です。

# Where
```C#:
var lists = Library.Books
            .Where(it => it.PublishedYear == 2020)             // ・・・1⃣
            .Where(it => it.Price > 990 && it.Price < 4000)    // ・・・2⃣
            .Where(it => it.Title.Contains($"C#"));            // ・・・3⃣
```
:::note warn
**メソッドチェーンの順番について**
メソッドチェーンは「上から順番に」処理される。
つまり、効率的に対象を絞り込むことができる条件をメソッドチェーンの上位に書くべき。
上記の例では条件1⃣が最も効率がよく、条件3⃣が最も絞り込む効率が悪い。
:::

# Where OR
```C#:
var lists = Library.Books
            .Where(it => it.PublishedYear == 2020 || it.PublishedYear == 2022);
```

# Where in (a, b,  ・・・)
```C#:
int[] searchYears = new int[] { 2019, 2021 };
var lists = Library.Books
            .Where(it => searchYears.Contains(it.PublishedYear));
```
# Where like 'keyword%' 前方一致
```C#:
var lists = Library.Books
            .Where(it => it.Title.StartsWith("keyword"));
```

# Where like '%keyword%' 中間一致
```C#:
var lists = Library.Books
            .Where(it => it.Title.Contains("keyword"));
```
# Where 大文字と小文字を無視して比較（完全一致）
```C#:
var lists = Library.Books
            .Where(it => String.Equals(it.Title, "keyword"
                , StringComparison.CurrentCultureIgnoreCase));
```
# Where 大文字と小文字を無視して比較（中間一致）
```C#:
var lists = Library.Books
            .Where(it => it.Title.ToLower().Contains("keyword") );
```
:::note warn
**StringComparisonについて**
中間一致に関しても完全一致と同様に、
```C#:
Contains(Char, StringComparison.CurrentCultureIgnoreCase)
```
が利用できればよりスマートに処理できるはずです。
ですが、LINQ to SQLではCultureInfoを利用したContainsメソッドがサポートされていません。
[Microsoft Learn unsupported systemstring static methods](https://learn.microsoft.com/en-us/dotnet/framework/data/adonet/sql/linq/system-string-methods?redirectedfrom=MSDN)
:::

# Select a, b, ・・・ ※特定列のみ
```C#:
var lists = Library.Books
            .Select(it => new {
                it.Title,
                it.Price,
            });
```
# Select ROW_NUMBER() ※取得結果に行番号を追加
```C#:
var lists = Library.Books
            .Select((it, i) => new {
                Index = i,
                Title = it.Title,
                Price = it.Price,
            });
```
:::note warn
**オリジンについて**
SQL ServerのROW_NUMBER()は1オリジン。
LinqのIndexは0オリジンなので注意。
:::

# Order By
```C#:
var lists = Library.Books
            .OrderBy(it => it.PublishedYear)
            .ThenBy(it => it.Price);
```
# Order By desc
```C#:
var lists = Library.Books
            .OrderByDescending(it => it.PublishedYear)
            .ThenByDescending(it => it.Price);
```
# 先頭5件
```C#:
var lists = Library.Books
            .Take(5);
```
# 11件目から5件
```C#:
var lists = Library.Books
            .Skip(10)
            .Take(5);
```
# 最後5件
```C#:
var lists = Library.Books
            .Reverse()
            .Take(5);
```
# Group By + Count()
```C#:
var lists = Library.Books
            .GroupBy(it => new {
                KeyPublishedYear = it.PublishedYear,
                KeyCategoryId = it.CategoryId, 
            })
            .Select(g => new {
                SelectedPublishedYear = g.Key.KeyPublishedYear,
                SelectedCategoryId = g.Key.KeyCategoryId,
                Count = g.Count()
            });
```
# Group By + max()
```C#:
var lists = Library.Books
            .GroupBy(it => new { 
                it.PublishedYear, 
                it.CategoryId
            })
            .Select(g => new {
                PublishedYear = g.Key.PublishedYear,
                CategoryId = g.Key.CategoryId,
                MaxPrice = g.Max(m => m.Price),
            });
```
# Distinct
```C#:
var lists = Library.Books
            .Select(it => new {
                PublishedYear = it.PublishedYear,
                CategoryId = it.CategoryId,
            })
            .Distinct();
```
# Join (テーブル結合)
```C#:
var lists = Library.Books
            .Join(
                Library.Categories,         // 結合するサブテーブル
                book => book.CategoryId,    // メインテーブル側の結合キー
                category => category.Id,    // サブテーブル側の結合キー
                (book, category) => new     // 結合した結果の欲しいサブセット
                {
                    Title = book.Title,
                    Price = book.Price,
                    PublishedYear = book.PublishedYear,
                    Category = category.Name,
                }
            );
```
# Join ※ 匿名クラスではなく特定のクラスで受ける
```C#:
public class CategoryNamedBook {
    public string Title { get; set; }
    public int Price { get; set; }
    public int PublishedYear { get; set; }
    public string Category { get; set; }
}

List<CategoryNamedBook> lists = Library.Books
            .Join(
                Library.Categories,
                book => book.CategoryId,
                category => category.Id,
                (book, category) => new CategoryNamedBook // 目的のクラスを指定する
                {
                    Title = book.Title,
                    Price = book.Price,
                    PublishedYear = book.PublishedYear,
                    Category = category.Name,
                }
            );
```
# 副問合せ（平均価格より高い本をすべて）
```C#:
var lists = Library.Books
            .Where(it => it.Price >
                Library.Books.Average(a => a.Price)
            );
```
:::note warn
ここではLinqの効率を考えず、副問合せの書き方のサンプルを提示しています。
Whereの中でサブクエリ
    Library.Books.Average(a => a.Price)
を、実行していますが、Booksの行数回サブクエリが呼び出され非効率とご指摘を頂きました。

上記の例で効率を考えれば事前に平均価格を求め、
    .Where(it => it.Price > AveragePrice);
のようにしたほうが効率的です。
:::

# 相関副問合せ（出版年ごとに平均価格より高い本をすべて）
```C#:
var lists = Library.Books
            .Where(it => it.Price >
                Library.Books
                    .Where(a => a.PublishedYear == it.PublishedYear)
                    .Average(a => a.Price)
            );
```

# UNION
```C#:
var lists = Library.Books.Union(Library.CSharpBooks);
```
# UNION ALL
```C#:
var lists = Library.Books.Concat(Library.CSharpBooks);
```
# Intersect
```C#:
var lists = Library.Books.Intersect(Library.CSharpBooks);
```
# Minus
```C#:
var lists = Library.Books.Except(Library.CSharpBooks);
```

# UNION など集合演算するためのクラス
```C#:
public class Book : IEnumerable<Book>
{

    public string Title { get; set; }
    public int Price { get; set; }
    public int CategoryId { get; set; }
    public int PublishedYear { get; set; }

    // 集合演算用に比較できる必要がある
    public override bool Equals(object other)
    {
        if (!(other is Book))
            return false;

        var otherBook = (Book)other;
        if (this.Title != otherBook.Title)
            return false;
        if (this.Price != otherBook.Price)
            return false;
        if (this.CategoryId != otherBook.CategoryId)
            return false;
        if (this.PublishedYear != otherBook.PublishedYear)
            return false;

        return true;
    }

    public override int GetHashCode() => 
        (Title, Price, CategoryId, PublishedYear).GetHashCode();

    public IEnumerator<Book> GetEnumerator() => throw new NotImplementedException();
    IEnumerator IEnumerable.GetEnumerator() => throw new NotImplementedException();
}
```