はじめに

以前の投稿ではEntity Frameworkを用いた簡単なCRUDのやり方を紹介しました。
しかし、実際の業務ではデータベースのSQLを用いて行うようなJoinやGroupBy等の操作もEntityFrameworkを用いて行うことがあるはずです。
データベースやSQLについて勉強したことがある方は分かると思うのですが、SQL文でのこれらの操作のクエリの書き方とEntity FrameworkでのC#のLinqのAPIを使った書き方は大きく異なります。
実際それらを使おうとした際に調べるためにそれぞれで各所を探し回ってかなり辛かったので、ここにチートシートを作っておきます。
網羅的な内容ではありませんが、参考になれば幸いです。
また、網羅的なドキュメントとしてはMSDNのQuryableクラスのメソッドの項目があるので、こちらもご覧ください。
ここでは、その中のいくつかについて、実際に動く形でのコード例を示しています。

準備

今回はプロジェクトを作る際は.Net Frameworkで作っても.Net Coreで作っても構いません。
プロジェクトを作成できたら、まずはクエリを試すためのDBを用意します。
今回はユーザー登録をした人がコメントを書き込めるサービスをイメージして、ユーザーテーブルとメッセージテーブルを用意します。
ユーザー登録者は名前、携帯端末を持っているか、年齢を登録します。
また、メッセージテーブルのUserIdはUserテーブルのUserIdと結びついており、Joinすることで結合操作が行えるようにします。
今回は簡単化のため外部制約などは付けていません。

class User
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public string UserName { get; set; }
    public bool HasMobile { get; set; }
    public int Age { get; set; }
}
class Message
{
    public int Id { get; set; }
    public int UserId { get; set; }
    public string Comment { get; set; }
}
class Context : DbContext
{
    public DbSet<User> Users { get; set; }
    public DbSet<Message> Messages { get; set; }
}

次に、サンプルデータを挿入するクエリーを作成します。
これをProgram.csで最初に呼ぶことで開始時には常にこのデータが挿入された状態になります。

static class Queries
{
    static public void InitiateData()
    {
        using (var context = new Context())
        {
            context.Users.Add(new User());
            context.Messages.Add(のew Message());
            context.SaveChanges();
            context.Users.RemoveRange(context.Users.ToList());
            context.Messages.RemoveRange(context.Messages.ToList());
            var users = new List<User>
            {
                new User { UserId = 1, UserName = "A", HasMobile = true, Age = 24 },
                new User { UserId = 2, UserName = "B", HasMobile = true, Age = 25 },
                new User { UserId = 3, UserName = "C", HasMobile = true, Age = 24 },
                new User { UserId = 4, UserName = "D", HasMobile = false, Age = 37 },
                new User { UserId = 5, UserName = "E", HasMobile = true, Age = 25 },
                new User { UserId = 6, UserName = "F", HasMobile = false, Age = 57 },
                new User { UserId = 7, UserName = "G", HasMobile = false, Age = 24 }
            };

            var messages = new List<Message> {
            new Message{ UserId = 1, Comment = "good!" },
            new Message{ UserId = 2, Comment = "good!" },
            new Message{ UserId = 3, Comment = "good!" },
            new Message{ UserId = 1, Comment = "good!" },
            new Message{ UserId = 4, Comment = "good!" },
            new Message{ UserId = 5, Comment = "good!" }
            };
            context.Users.AddRange(users);
            context.Messages.AddRange(messages);
            context.SaveChanges();
        }
    }
}

これをProgram.csから呼ぶ例は以下です。
これ以降のコードも動作確認時にはProgram.csから呼ぶようにしておきましょう。

class Program
{
    static void Main(string[] args)
    {
        Queries.InitiateData();
    }
}

GROUP BYしてMAX取得

LinqではGROUP BYがSELECTより先に来ます。
また、Maxの引数にラムダ関数でどのプロパティの最大値をとるか指定する必要があります。
また、これ以降のコードを試す際はSystem.Linqを忘れずにusingしておいてください。

static public void GroupByAndMax()
{
    using (var context = new Context())
    {
        var group = context.Users.GroupBy(x => x.HasMobile)
            .Select(x => new { x.Key, MaxAge = x.Max(x => x.Age) }).ToList();
        Console.WriteLine("携帯端末を持っている人もっていない人それぞれの最大年齢は");
        group.ForEach(x => Console.WriteLine($"HasMobile:{x.Key}, MaxAge:{x.MaxAge}"));
    }
}

GROUP BYしてCOUNT取得

MAXの場合とほぼ同様ですが、Countには引数が必要ありません。
static public void GroupByAndCount()
{
    using (var context = new Context())
    {
        var group = context.Users.GroupBy(x => x.Age)
            .Select(x => new { x.Key, Count = x.Count() }).ToList();
        Console.WriteLine("年齢ごとのユーザー数は");
        group.ForEach(x => Console.WriteLine($"年齢:{x.Key}歳, Count:{x.Count}"));
    }
}

DISTINCT

LinqではDistinctはSelectの後に来ます。

static public void Distinct()
{
    using (var context = new Context())
    {
        var distinctAge = context.Users
            .Select(x => x.Age)
            .Distinct().ToList();
        Console.WriteLine("重複排除したユーザー年齢は");
        distinctAge.ForEach(x => Console.WriteLine($"{x}歳"));
    }
}

INNER JOIN

Join時は第1引数にJoin先テーブル、第2引数にラムダ式で左側の接続列、第3引数にはラムダ式で右側の接続列、第4引数にラムダ式で作成する新しい匿名クラスの形式を指定してやります。

static public void InnerJoin()
{
    using (var context = new Context())
    {
        var list = context.Users.Join(context.Messages,
            users => users.UserId,
            messages => messages.UserId,
        (user, message) => new
        {
            message.Comment,
            user.UserName
        }).ToList();
        Console.WriteLine("コメントとその発信元ユーザー名は");
        list.ForEach(x => Console.WriteLine($"Comment:{x.Comment}, UserName:{x.UserName}"));
    }
}

LEFT OUTER JOIN

LEFT OUTER JOINをする際は、JoinではなくGroupJoinというものを使います。
また、RIGHT側に対応する項目がなくても出力を行うためにmessageに対してDefaultIfEmptyを使用します。

static public void LeftOuterJoin()
{
    using (var context = new Context())
    {
        var list = context.Users.GroupJoin(context.Messages,
            user => user.UserId,
            message => message.UserId,
            (user, message) => new { user, message })
        .SelectMany(um => um.message.DefaultIfEmpty(), (u, m) => new
        {
            u.user.UserName,
            m.Comment
        }).ToList();
        Console.WriteLine("投稿のないユーザーも含めてコメントとのJoin結果");
        list.ForEach(x => Console.WriteLine($"UserId:{x.UserName}, Comment:{x.Comment}"));
    }
}

複数条件のWhere

ANDは&&に代わります。

static public void MultiWhere()
{
    using (var context = new Context())
    {
        var users = context.Users
            .Where(x => x.HasMobile == true && x.Age < 40).ToList();
        Console.WriteLine("携帯端末を持っていて年齢が40歳未満のユーザーは");
        users.ForEach(x => Console.WriteLine($"UserId:{x.UserId}, Age:{x.Age}"));
    }
}

おまけのちょっとした解説

UserテーブルとMessageテーブルの両方でUserIdとは別にIdというプロパティが設定されています。
これは、この名前のプロパティを付けておくことで自動的にそのプロパティを主キーにして、またデータ挿入時に自動で採番してくれるものです。
これらのInitiateDataをProgram.csから２回以上呼んだ後にSSMSなどでデータの中身を確認すると、Idの値は1からではない連続した値になっているはずです。2回以上このコードを実行していると、InitiateDataコード上で毎回リセットのため既存のデータの削除動作が行われ、採番が最後に採番した次の値から始まるためです。