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

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

 

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

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

view plainprint?

1. using (PubsDataContext pubs = new PubsDataContext())  
2. {  
3.     var authors = pubs.authors.Where(a => a.titleauthor.Count == 2)  
4.                               .Select(b => new  
5.                               {  
6.                                   b.au_id,  
7.                                   b.au_fname,  
8.                                   b.au_lname,  
9.                                   titleCount = b.titleauthor.Count  
10.                               });  
11.     form.dataGridView1.DataSource = authors;  
12. }  

            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を表示）

view plainprint?

1. SELECT [t0].[au_id], [t0].[au_fname], [t0].[au_lname], (  
2.     SELECT COUNT(*)  
3.     FROM [dbo].[titleauthor] AS [t2]  
4.     WHERE [t2].[au_id] = [t0].[au_id]  
5.     ) AS [titleCount]  
6. FROM [dbo].[authors] AS [t0]  
7. WHERE ((  
8.     SELECT COUNT(*)  
9.     FROM [dbo].[titleauthor] AS [t1]  
10.     WHERE [t1].[au_id] = [t0].[au_id]  
11.     )) = @p0  
12. -- @p0: Input Int (Size = -1; Prec = 0; Scale = 0) [2]  
13. -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1  

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が得意な方ではないのでこれは非常に助かる。

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

view plainprint?

1. using (PubsDataContext pubs = new PubsDataContext())  
2. {  
3.     var authors = from a in pubs.authors  
4.                   where a.titleauthor.Count == 2  
5.                   select new  
6.                   {  
7.                       a.au_id,  
8.                       a.au_fname,  
9.                       a.au_lname,  
10.                       titleCount = a.titleauthor.Count  
11.                   };  
12.   
13.     form.dataGridView1.DataSource = authors;  
14.     Console.Beep();  
15. }  

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