EX12

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace Ex12
{
    class Program
    {
        private static NorthwindDataContext db = new NorthwindDataContext();
        static void Main(string[] args)
        {
            //1 Query Exprssion -> Query Method
            //QueryTest01();

            //2 Query Exprssion -> Query Method
            //QueryTest02();

            //3.
            //QueryTest03();

            //4.
            //QueryTest04();

            //5.
            //QueryTest05();

            //6.
            QueryTest06();
        }

        private static void QueryTest06()
        {
            //이름순으로 정렬된 고객목록에서 6 - 10번째 고객 조회
            var customers = db.Customers.OrderBy(c => c.CompanyName)
                            .Skip(5).Take(5);
            foreach (var customer in customers)
            {
                Console.WriteLine("[{0}], [{1}], [{2}]",
                    customer.CustomerID,
                    customer.CompanyName,
                    customer.ContactName);

            }
        }

        private static void QueryTest05()
        {
            //집계함수
            decimal? sum = db.Products.Sum(p => p.UnitPrice); //decimal? 은 null을 허용
            decimal? average = db.Products.Average(p => p.UnitPrice);
            int count = db.Products.Count();

            Console.WriteLine("{0}, {1}, {2}", sum, count, average);
        }

        private static void QueryTest04()
        {
            var results = db.Customers.Select(c => c.CompanyName)
                .Concat(db.Products.Select(p => p.ProductName)); // 두개의 결과를 합쳐서 사용

            foreach( var result in results)
                Console.WriteLine(result);

        }

        private static void QueryTest03()
        {
            var results = db.Customers
                .Select(c => c.Country)
                .Distinct(); //중복되는 데이터를 제거하고 반환
        }

        private static void QueryTest02()
        {
            //var results = db.Products
            //    .Where(p => p.UnitPrice >= 50)
            //    .GroupBy(p => p.CategoryID, p => p)// (조건, 대상)
            //    .Select(p => new { category = p.Key, data = p })
            //    .OrderBy(p => p.category);
            var results = db.Products
                .GroupBy(p => p.Categories.CategoryName, p => p);
            foreach (var result in results)
            {
                Console.WriteLine(result.Key);

                foreach (var data in result)
                {
                    Console.WriteLine("{0}.{1}", data.ProductID, data.ProductName);
                }
            }
        }

        private static void QueryTest01()
        {
            //var results = from c in db.Customers
            //              where c.ContactName.StartsWith("A")
            //              orderby c.ContactName descending
            //              select new
            //              {
            //                  c.CustomerID, c.CompanyName,
            //                  c.ContactName
            //              };
            var results = db.Customers
                .Where(c => c.ContactName.StartsWith("A"))
                .OrderByDescending(c => c.ContactName)
                .Select(c => new { c.CustomerID, c.CompanyName, c.ContactName });
            foreach (var result in results)
            {

                Console.WriteLine("[{0}][{1}][{2}]",
                    result.CustomerID, result.CompanyName,
                    result.ContactName);

            }
        }
    }
}