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

namespace LinqSqlEpisodeIIProVid
{
    class Program
    {
        static void Main(string[] args)
        {
            NorthwindDataClassesDataContext northwindContext = new NorthwindDataClassesDataContext();

            #region Query Products From the Database
            //var beveragesProducts = from bev in northwindContext.Products
            //                        where bev.Category.CategoryName == "Beverages"
            //                        select bev;
            //foreach (var item in beveragesProducts)
            //{
            //    Console.WriteLine(item.ProductName);
            //} 
            #endregion

            #region Update a Product in the Database
            //Product selectedProduct = northwindContext.Products.Single<Product>(p => p.ProductName == "Chai");
            //selectedProduct.UnitsInStock = 50;
            //northwindContext.SubmitChanges();
            //Console.WriteLine("Product updated."); 
            #endregion

            #region Insert a New Category and Two New Products into the Database
            //Category providProducts = new Category() { CategoryName = "ProVid Products" };

            //Product linq = new Product() { ProductName = "LINQ to SQL Training Course" };
            //Product ef = new Product() { ProductName = "Entity Framework Training Course" };

            //providProducts.Products.Add(linq);
            //providProducts.Products.Add(ef);

            //northwindContext.Categories.InsertOnSubmit(providProducts);
            //northwindContext.SubmitChanges();
            //Console.WriteLine("Products Added"); 
            #endregion

            #region Delete Products from the Database
            //var productToDelete = (from p in northwindContext.Products
            //                       where p.ProductName == "LINQ to SQL Training Course"
            //                       select p).Single<Product>();
            //northwindContext.Products.DeleteOnSubmit(productToDelete);
            //northwindContext.SubmitChanges();
            //Console.WriteLine("Product Deleted."); 
            #endregion

            #region Calling a Stored Procedure
            //var topProducts = northwindContext.Ten_Most_Expensive_Products();
            //foreach (var item in topProducts)
            //{
            //    Console.WriteLine(item.TenMostExpensiveProducts + "  : " + item.UnitPrice);
            //} 
            #endregion

            #region Retrieve Products with Server Side Paging
            //var paginProducts = (from p in northwindContext.Products
            //                     where p.Category.CategoryName.StartsWith("C")
            //                     select p).Skip(10).Take(5);
            //foreach (var item in paginProducts)
            //{
            //    Console.WriteLine(item.Category.CategoryName + " : " + item.ProductName);
            //} 
            #endregion

            var query = from p in northwindContext.Products
                        where p.CategoryID == 3
                        select p;
            //query = from p in northwindContext.Products
            //        where p.Category.CategoryName == "Beverages"
            //        select p;
            //query = from p in northwindContext.Products
            //        where p.Order_Details.Count > 5
            //        select p;
            Console.ReadKey();
        }
    }
}
