◆LINQでのソート処理（OrderBy,ThenBy)

LINQでのソート処理はOrderByメソッドで行うことができる。
ソート条件が２つ以上ある時は、２つめ以降にはThenByメソッドを使うらしい。（OrderByをつなげて指定することもできるがその場合は指定する順番が逆になるとの事）
降順の時はそれぞれOrderByDescending、ThenByDescendingとなる。

view plainprint?

1. //Sort処理  
2. public void linqOrderBy(Form1 form)  
3. {  
4.     using (PubsDataContext pubs = new PubsDataContext())  
5.     {  
6.         var employees = pubs.employee.OrderBy(e => e.job_lvl)  
7.                                      .ThenBy(e => e.lname)  
8.                                      .Select(e => new  
9.                                         {  
10.                                             e.job_lvl,  
11.                                             e.lname,  
12.                                             e.fname,  
13.                                             e.emp_id  
14.                                         });  
15.         form.dataGridView1.DataSource = employees;  
16.     }  
17. }  
18.   
19. //Sort処理(job_lvlの降順）  
20. public void linqOrderByDescending(Form1 form)  
21. {  
22.     using (PubsDataContext pubs = new PubsDataContext())  
23.     {  
24.         var employees = pubs.employee.OrderByDescending(e => e.job_lvl)  
25.                                      .ThenBy(e => e.lname)  
26.                                      .Select(e => new  
27.                                      {  
28.                                          e.job_lvl,  
29.                                          e.lname,  
30.                                          e.fname,  
31.                                          e.emp_id  
32.                                      });  
33.         form.dataGridView1.DataSource = employees;  
34.     }  
35. }  

        //Sort処理
        public void linqOrderBy(Form1 form)
        {
            using (PubsDataContext pubs = new PubsDataContext())
            {
                var employees = pubs.employee.OrderBy(e => e.job_lvl)
                                             .ThenBy(e => e.lname)
                                             .Select(e => new
                                                {
                                                    e.job_lvl,
                                                    e.lname,
                                                    e.fname,
                                                    e.emp_id
                                                });
                form.dataGridView1.DataSource = employees;
            }
        }

        //Sort処理(job_lvlの降順）
        public void linqOrderByDescending(Form1 form)
        {
            using (PubsDataContext pubs = new PubsDataContext())
            {
                var employees = pubs.employee.OrderByDescending(e => e.job_lvl)
                                             .ThenBy(e => e.lname)
                                             .Select(e => new
                                             {
                                                 e.job_lvl,
                                                 e.lname,
                                                 e.fname,
                                                 e.emp_id
                                             });
                form.dataGridView1.DataSource = employees;
            }
        }

埋め込みクエリー方式では、

view plainprint?

1. //Sort  
2. public void linqOrderBy(Form1 form)  
3. {  
4.     using (PubsDataContext pubs = new PubsDataContext())  
5.     {  
6.         var employees = from e in pubs.employee  
7.                         orderby e.job_lvl , e.lname  
8.                         select new  
9.                                     {  
10.                                         e.job_lvl,  
11.                                         e.lname,  
12.                                         e.fname,  
13.                                         e.emp_id  
14.                                     };  
15.         form.dataGridView1.DataSource = employees;  
16.         Console.Beep();  
17.     }  
18. }  
19.   
20. //Sort(job_lvl降順）  
21. public void linqOrderByDescending(Form1 form)  
22. {  
23.     using (PubsDataContext pubs = new PubsDataContext())  
24.     {  
25.         var employees = from e in pubs.employee  
26.                         orderby e.job_lvl descending, e.lname  
27.                         select new  
28.                         {  
29.                             e.job_lvl,  
30.                             e.lname,  
31.                             e.fname,  
32.                             e.emp_id  
33.                         };  
34.         form.dataGridView1.DataSource = employees;  
35.         Console.Beep();  
36.     }  
37. }  

        //Sort
        public void linqOrderBy(Form1 form)
        {
            using (PubsDataContext pubs = new PubsDataContext())
            {
                var employees = from e in pubs.employee
                                orderby e.job_lvl , e.lname
                                select new
                                            {
                                                e.job_lvl,
                                                e.lname,
                                                e.fname,
                                                e.emp_id
                                            };
                form.dataGridView1.DataSource = employees;
                Console.Beep();
            }
        }

        //Sort(job_lvl降順）
        public void linqOrderByDescending(Form1 form)
        {
            using (PubsDataContext pubs = new PubsDataContext())
            {
                var employees = from e in pubs.employee
                                orderby e.job_lvl descending, e.lname
                                select new
                                {
                                    e.job_lvl,
                                    e.lname,
                                    e.fname,
                                    e.emp_id
                                };
                form.dataGridView1.DataSource = employees;
                Console.Beep();
            }
        }

また、ソート条件には計算結果なども指定できるので以下のようなソートも可能だ。

view plainprint?

1. //名前の長さでソート  
2. public void linqOrderByNameLength(Form1 form)  
3. {  
4.     using (PubsDataContext pubs = new PubsDataContext())  
5.     {  
6.         var employees = pubs.employee.OrderBy(e => e.lname.Length + e.fname.Length)  
7.             .Select(e => new  
8.             {  
9.                 name = e.lname + e.fname,  
10.                 e.job_id  
11.             });  
12.         form.dataGridView1.DataSource = employees;     
13.     }  
14. }  
15.   
16.   
17. //job毎の社員数でソート  
18. public void linqOrderByJobsEmployeeCount(Form1 form)  
19. {  
20.     using (PubsDataContext pubs = new PubsDataContext())  
21.     {  
22.         pubs.Log = Console.Out;  
23.         var jobs = pubs.jobs.OrderBy(j => j.employee.Count)  
24.                         .Select(j => new  
25.                         {  
26.                             employeeCount = j.employee.Count,  
27.                             j.job_id,  
28.                             j.job_desc  
29.                         });  
30.         form.dataGridView1.DataSource = jobs;  
31.   
32.     }  
33. }  

        //名前の長さでソート
        public void linqOrderByNameLength(Form1 form)
        {
            using (PubsDataContext pubs = new PubsDataContext())
            {
                var employees = pubs.employee.OrderBy(e => e.lname.Length + e.fname.Length)
                    .Select(e => new
                    {
                        name = e.lname + e.fname,
                        e.job_id
                    });
                form.dataGridView1.DataSource = employees;   
            }
        }


        //job毎の社員数でソート
        public void linqOrderByJobsEmployeeCount(Form1 form)
        {
            using (PubsDataContext pubs = new PubsDataContext())
            {
                pubs.Log = Console.Out;
                var jobs = pubs.jobs.OrderBy(j => j.employee.Count)
                                .Select(j => new
                                {
                                    employeeCount = j.employee.Count,
                                    j.job_id,
                                    j.job_desc
                                });
                form.dataGridView1.DataSource = jobs;

            }
        }

job毎の社員数でソートの場合は以下のようなSQLが発行されているようなので件数が増えた場合に性能的にどうなのか検証が必要かもしれない。

view plainprint?

1. SELECT (  
2.     SELECT COUNT(*)  
3.     FROM [dbo].[employee] AS [t2]  
4.     WHERE [t2].[job_id] = [t0].[job_id]  
5.     ) AS [employeeCount], [t0].[job_id], [t0].[job_desc]  
6. FROM [dbo].[jobs] AS [t0]  
7. ORDER BY (  
8.     SELECT COUNT(*)  
9.     FROM [dbo].[employee] AS [t1]  
10.     WHERE [t1].[job_id] = [t0].[job_id]  
11.     )  
12. -- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1  

SELECT (
    SELECT COUNT(*)
    FROM [dbo].[employee] AS [t2]
    WHERE [t2].[job_id] = [t0].[job_id]
    ) AS [employeeCount], [t0].[job_id], [t0].[job_desc]
FROM [dbo].[jobs] AS [t0]
ORDER BY (
    SELECT COUNT(*)
    FROM [dbo].[employee] AS [t1]
    WHERE [t1].[job_id] = [t0].[job_id]
    )
-- Context: SqlProvider(Sql2008) Model: AttributedMetaModel Build: 4.0.30319.1