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

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

Where

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⃣

メソッドチェーンの順番について
メソッドチェーンは「上から順番に」処理される。
つまり、効率的に対象を絞り込むことができる条件をメソッドチェーンの上位に書くべき。
上記の例では条件1⃣が最も効率がよく、条件3⃣が最も絞り込む効率が悪い。

Where OR

var lists = Library.Books
            .Where(it => it.PublishedYear == 2020 || it.PublishedYear == 2022);

Where in (a, b, ・・・)

int[] searchYears = new int[] { 2019, 2021 };
var lists = Library.Books
            .Where(it => searchYears.Contains(it.PublishedYear));

Where like 'keyword%' 前方一致

var lists = Library.Books
            .Where(it => it.Title.StartsWith("keyword"));

Where like '%keyword%' 中間一致

var lists = Library.Books
            .Where(it => it.Title.Contains("keyword"));

Where 大文字と小文字を無視して比較（完全一致）

var lists = Library.Books
            .Where(it => String.Equals(it.Title, "keyword"
                , StringComparison.CurrentCultureIgnoreCase));

Where 大文字と小文字を無視して比較（中間一致）

var lists = Library.Books
            .Where(it => it.Title.ToLower().Contains("keyword") );

StringComparisonについて
中間一致に関しても完全一致と同様に、

Contains(Char, StringComparison.CurrentCultureIgnoreCase)

が利用できればよりスマートに処理できるはずです。
ですが、LINQ to SQLではCultureInfoを利用したContainsメソッドがサポートされていません。
Microsoft Learn unsupported systemstring static methods

Select a, b, ・・・ ※特定列のみ

var lists = Library.Books
            .Select(it => new {
                it.Title,
                it.Price,
            });

Select ROW_NUMBER() ※取得結果に行番号を追加

var lists = Library.Books
            .Select((it, i) => new {
                Index = i,
                Title = it.Title,
                Price = it.Price,
            });

オリジンについて
SQL ServerのROW_NUMBER()は1オリジン。
LinqのIndexは0オリジンなので注意。

Order By

var lists = Library.Books
            .OrderBy(it => it.PublishedYear)
            .ThenBy(it => it.Price);

Order By desc

var lists = Library.Books
            .OrderByDescending(it => it.PublishedYear)
            .ThenByDescending(it => it.Price);

先頭5件

var lists = Library.Books
            .Take(5);

11件目から5件

var lists = Library.Books
            .Skip(10)
            .Take(5);

最後5件

var lists = Library.Books
            .Reverse()
            .Take(5);

Group By + Count()

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()

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

var lists = Library.Books
            .Select(it => new {
                PublishedYear = it.PublishedYear,
                CategoryId = it.CategoryId,
            })
            .Distinct();

Join (テーブル結合)

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 ※ 匿名クラスではなく特定のクラスで受ける

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,
                }
            );

副問合せ（平均価格より高い本をすべて）

var lists = Library.Books
            .Where(it => it.Price >
                Library.Books.Average(a => a.Price)
            );

ここではLinqの効率を考えず、副問合せの書き方のサンプルを提示しています。
Whereの中でサブクエリ
Library.Books.Average(a => a.Price)
を、実行していますが、Booksの行数回サブクエリが呼び出され非効率とご指摘を頂きました。

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

相関副問合せ（出版年ごとに平均価格より高い本をすべて）

var lists = Library.Books
            .Where(it => it.Price >
                Library.Books
                    .Where(a => a.PublishedYear == it.PublishedYear)
                    .Average(a => a.Price)
            );

UNION

var lists = Library.Books.Union(Library.CSharpBooks);

UNION ALL

var lists = Library.Books.Concat(Library.CSharpBooks);

Intersect

var lists = Library.Books.Intersect(Library.CSharpBooks);

Minus

var lists = Library.Books.Except(Library.CSharpBooks);

UNION など集合演算するためのクラス

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();
}