EX11 - 2

using System;
using System.Collections.Generic;

using System.Linq;
using System.Text;

namespace Ex11
{
    class Program
    {
        static void Main(string[] args)
        {
            //1. select, from 기본 구문
            // QueryTest01();

            //2. slelct 구문
            //QueryTest02();

            //3. where 구문
            //QueryTest03();

            //4. orderby 구문
            //QueryTest04();

            //5. groupby 구문
            //QueryTest05();
            //QueryTest05_1();

            //6. into 구문 : Having 구문 역할
            //QueryTest06();

            //7. join 구문
            //QueryTest07();
       
            //8. let statement
            QueryTest08();

        }

        private static void QueryTest08()
        {
            NorthwindDataContext db = new NorthwindDataContext();
//            var results = from c in db.Categories
  //                        join p in db.Product;
            var results = from p in db.Product.AsEnumerable()
                          // 조회 데이타를 let 에 담는다(임시저장소)
                          let productName = p.ProductName.Split(' ') 
                          from pn in productName //
                          let pu = pn.ToUpper() //전부 대문자로 바꿔fk
                          where pu == "BOSTON" // 저장된 let 으로도 검색 가능
                          select new
                          {
                              productName = p.ProductName,
                              ProductID = p.ProductID
                          };

            foreach (var result in results)
            {
                Console.WriteLine(result.ProductID + " : " +
                    result.productName);
            }

        }

        private static void QueryTest07()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var results = from c in db.Categories
                          join p in db.Product
                          //JOIN의 조건검사는 관계연산자 사용 X
                          // on c.CategoryID == p.CategoryID   X
                          on c.CategoryID equals p.CategoryID
                          select new
                          {
                              p.ProductName,
                              c.CategoryName
                          };
            foreach (var result in results)
            {
                Console.WriteLine(result.ProductName + " / " +
                    result.CategoryName);
            }
        }

        private static void QueryTest06()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var result = from c in db.Customer
                         group c by c.Country into temp
                         where temp.Count() > 5
                         select temp;
            foreach (var grp in result)
            {
                Console.WriteLine(grp.Key);
                foreach (var data in grp)
                {
                    Console.WriteLine("\t[{0}][{1}]",
                        data.CustomerID, data.CompanyName);
                }
            }

        }

        private static void QueryTest05_1()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var result = from c in db.Customer
                         group c by c.CompanyName.StartsWith("A");

            foreach (var grp in result)
            {
                Console.WriteLine(grp.Key);
                foreach (var data in grp)
                {
                    Console.WriteLine("\t[{0}][{1}]",
                        data.CustomerID, data.CompanyName);
                }
            }
        }

        private static void QueryTest05()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var result = from c in db.Customer
                            group c by c.Country;
            foreach (var grp in result)
            {
                Console.WriteLine(grp.Key);
                foreach (var data in grp)
                {
                    Console.WriteLine("\t[{0}][{1}]",
                        data.CustomerID, data.CompanyName);
                }
            }
        }

        private static void QueryTest04()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var customers = from c in db.Customer
                            //orderby c.ContactName //기본 ascendinf 정렬
                                orderby
                                    c.ContactName descending, // 정렬 dec
                                    c.CompanyName
                            select c;
            foreach (Customer c in customers)
            {
                Console.WriteLine(
                    "[{0}][{1}][{2}]",
                    c.CustomerID, c.CompanyName, c.ContactName);
            }

        }

        private static void QueryTest03()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            var customers = from c in db.Customer
                           //필터링
                           where
                                (c.CompanyName.StartsWith("A") ||
                                c.CompanyName.StartsWith("F")) &&
                                c.ContactName.StartsWith("A")
                           select c;
            foreach (Customer c in customers)
            {
                Console.WriteLine(
                    "[{0}][{1}][{2}]",
                    c.CustomerID, c.CompanyName, c.ContactName);
            }

        }
        private static void QueryTest01()
        {
            NorthwindDataContext db = new NorthwindDataContext();
            //1. From & Select
            //1_1
            //List<Customer> customers = new List<Customer>();
            //foreach (Customer customer in db.Customer) 아래의 from 역할
            //{
            //    customers.Add(customer); // 아래의 select 역할
            //}

            //1_2
            var customers = from c in db.Customer
                            select c;

            foreach (Customer c in customers)
            {
                Console.WriteLine(
                    "[{0}][{1}][{2}]",
                    c.CustomerID, c.CompanyName, c.ContactName);
            }
        }
        private static void QueryTest02()
        {
            NorthwindDataContext db = new NorthwindDataContext();

            var customers = from c in db.Customer
                            //select c
                            //select new { c.CustomerID }; //1
                            select new
                            {
                                c.CustomerID, c.CompanyName
                            };//2

            foreach (var obj in customers)
            {
                //Console.WriteLine(obj.CustomerID); //1
                Console.WriteLine("[{0}][{1}]", obj.CustomerID, obj.CompanyName);
            }
        }

       
          
    }
}