Untitled

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

namespace bai1
{
    class Program
    {
         public static NorthwindDataContext dbs = new NorthwindDataContext();
        public static void grouping (){
            //query
            
            var fullMaDia = from p in dbs.T_Dia_Phims select p.MaDia;
            Console.WriteLine("bang ma dia:");
            foreach (var p in fullMaDia)
            {
                Console.WriteLine(p);

            }
            Console.WriteLine("===============================================");
            var maDiaGroupBy = from p in dbs.T_Dia_Phims group p by p.T_Dia;
            Console.WriteLine("Group by theo ma dia:");
            foreach(var g in maDiaGroupBy)
            { 
                Console.WriteLine(g.Key.MaDia+"\t"+g.Key.TenDia);
                
            }
        }
        public static void aggregate()
        {
            var phimCounts =
                     from p in dbs.T_Phim_DienViens
                     group p by p.MaDienVien into p
                     select new
                     {
                         MaDV = p.Key,
                         soLuongPhimDong = p.Key.Count()
                     };
            foreach (var p in phimCounts)
            {
                Console.WriteLine(p);
            }
        }
        public static void conversion()
        {

            var DV = from p in dbs.T_DienViens select p;
            Console.WriteLine("bang dien vien:");
            foreach (var p in DV)
            {
                Console.WriteLine(p.MaDienVien + "\t" + p.TenDienVien);
            }
            Console.WriteLine("=======================================================");
            var sortNameDV =
                    from p in dbs.T_DienViens
                    orderby p.TenDienVien
                    select p;
            Console.WriteLine("sau khi sap xep theo bang chu cai:");
            foreach (var p in sortNameDV)
            {
                Console.WriteLine(p.MaDienVien+"\t"+p.TenDienVien);
            }
        }
        public static void ElementOperator()
        {
            var DV = from p in dbs.T_DienViens select p;
            Console.WriteLine("bang dien vien:");
            foreach (var p in DV)
            {
                Console.WriteLine(p.MaDienVien + "\t" + p.TenDienVien+"\t"+p.QuocTich);
            }
            Console.WriteLine("=======================================================");
            Console.WriteLine("quoc tich VN dau tien");
            var fulllVN =
                    from p in dbs.T_DienViens
                    where p.QuocTich == "Việt Nam"
                    select p;
            var VNfirst= fulllVN.FirstOrDefault(); //lấy phần tử có quốc tịch VN đầu tiên khi query
            Console.WriteLine(VNfirst.MaDienVien+"\t"+VNfirst.TenDienVien+"\t"+VNfirst.QuocTich);
            Console.WriteLine("=======================================================");
            Console.WriteLine("quoc tich my dau tien");
            var fulllMy =
                    from p in dbs.T_DienViens
                    where p.QuocTich == "Mỹ"
                    select p;
            var Myfirst = fulllMy.FirstOrDefault(); //lấy phần tử có quốc tịch Mỹ đầu tiên khi query
            Console.WriteLine(Myfirst.MaDienVien + "\t" + Myfirst.TenDienVien + "\t" + Myfirst.QuocTich);
        }
        public  static void joinOpeator()
        {
            var customJoin = from p in dbs.T_ThueDias
                              join g in dbs.T_KhachHangs on p.MaKhachHang equals g.MaKhachHang
                              select new { maKH = p.MaKhachHang, tenKH = g.TenKhachHang, diaChi = g.DiaChi, diaThue = p.MaDia };
            foreach (var p in customJoin)
            {
                Console.WriteLine(p);
            }

        }
        public static void PartitioningOpeator()
        {
            //lấy 3 record đầu tiên của bảng
            var ThreeThueDia = (from p in dbs.T_ThueDias select p).Take(3);
            foreach (var p in ThreeThueDia)
            {
                Console.WriteLine(p.MaDia+"\t"+p.MaKhachHang+"\t"+p.NgayThue+"\t"+p.NgayTra);
            }
            Console.WriteLine("======================================");
            //bỏ qua 4 record đầu bảng
            var Skip4ThueDia= (from p in dbs.T_ThueDias select p).Skip(4);
            foreach (var p in Skip4ThueDia)
            {
                Console.WriteLine(p.MaDia + "\t" + p.MaKhachHang + "\t" + p.NgayThue + "\t" + p.NgayTra);
            }
        }
        public static void ProjectionOperator()
        {
            var ThueDia = from p in dbs.T_ThueDias where p.MaKhachHang=="KH0002" select new { ngayThue=p.NgayThue,ngayTra=p.NgayTra };
            foreach (var p in ThueDia)
            {
                Console.WriteLine(p);
            }
        }
        public static void quantifiers()
        {
            //tìm diễn viên có vai chính
            var vaiChinh =
                    from p in dbs.T_Phim_DienViens
                    group p by p.MaDienVien into test
                    where test.All(p => p.VaiChinh == true)
                    select new { maDV = test.Key, soPhimlaDV_Chinh = test.Key.Count() };
            //mã diễn viên và số lượng phim mà diễn viên đó đóng vai chính
            foreach (var p in vaiChinh)
            {
                Console.WriteLine(p);
            }
        }
        public static void setOperator()
        {
            var maKH = from p in dbs.T_KhachHangs select p.MaKhachHang;
            var maKHThue = from p in dbs.T_ThueDias select p.MaKhachHang;
            var unique = maKH.Union(maKHThue);
            //lấy tất cả ở 2 bảng
            foreach (var p in unique)
            {
                Console.WriteLine(p);
            }
            //lấy phần tử chung 2 bảng
            Console.WriteLine("================================");
            var unique2 = maKH.Intersect(maKHThue);
            foreach (var p in unique2)
            {
                Console.WriteLine(p);
            }
           
        }
        public static void miscellaneous()
        {
            var diaID =
                from c in dbs.T_Dias
                select c.MaDia;
            var diaPhimID =
                from c in dbs.T_Dia_Phims
                select c.MaDia;
            var allID = diaID.Concat(diaPhimID);
            foreach (var p in allID)
            {
                Console.WriteLine(p);
            }
        }
        static void Main(string[] args)
        {
            //grouping();
            //aggregate();
            //conversion();
            //ElementOperator();
            //joinOpeator();
            //PartitioningOpeator();
            //ProjectionOperator();
            //quantifiers();
            //setOperator();
            miscellaneous();
            Console.ReadKey();
        }
    }
}