◆LINQでリンクテーブルのデータ件数を抽出条件に使う

以下はPubsデータベースのauthorsテーブルとtitleautherテーブルの関係である。

 

authorsテーブルにぶら下がっているtitleauthorの件数をカウントすることによって、その著者が何冊の（何種類の）本を出版しているかが判る。

そこで２種類の本を出版している著者を抽出するには以下のようなLINQが記述できる。

            using (PubsDataContext pubs = new PubsDataContext())
            {
                var authors = pubs.authors.Where(a => a.titleauthor.Count == 2)
                                          .Select(b => new
                                          {
                                              b.au_id,
                                              b.au_fname,
                                              b.au_lname,
                                              titleCount = b.titleauthor.Count
                                          });
                form.dataGridView1.DataSource = authors;
            }

３行目でその抽出条件を指定している。
ここで、aはauthorsテーブルの１件（行）に対するオブジェクト。そしてそのリンク（リレーション）先であるtitleauthor（au_idの等しいtitleauthorレコードの集まり）はそのものズバリのtitleauthorプロパティで参照することができる。
なので、その件数としてCountプロパティが２件のものといった指定が可能になっている。

ここらへんの機能（便利さ）がLINQの売りの一つではなかろうか。
本来ならば以下の様なサブクエリーを使った比較的面倒なSQLが必要になる。
（実際に発行されているSQLを表示）

SELECT [t0].[au_id], [t0].[au_fname], [t0].[au_lname], (
    SELECT COUNT(*)
    FROM [dbo].[titleauthor] AS [t2]
    WHERE [t2].[au_id] = [t0].[au_id]
    ) AS [titleCount]
FROM [dbo].[authors] AS [t0]
WHERE ((
    SELECT COUNT(*)
    FROM [dbo].[titleauthor] AS [t1]
    WHERE [t1].[au_id] = [t0].[au_id]
    )) = @p0
-- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [2]
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1

私はSQLが得意な方ではないのでこれは非常に助かる。

参考までに埋め込みクエリー方式では以下のようになる。

            using (PubsDataContext pubs = new PubsDataContext())
            {
                var authors = from a in pubs.authors
                              where a.titleauthor.Count == 2
                              select new
                              {
                                  a.au_id,
                                  a.au_fname,
                                  a.au_lname,
                                  titleCount = a.titleauthor.Count
                              };

                form.dataGridView1.DataSource = authors;
                Console.Beep();
            }