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

namespace LinqSqlEpisodeVIIIProVid
{
    class Program
    {
        static void Main(string[] args)
        {
            NorthwindDataContext northwind = new NorthwindDataContext();

            #region Using Custom SQL Queries with LINQ to SQL
            //var products = GetProductsCategory(1);

            //foreach (var item in products)
            //{
            //    Console.WriteLine(item.ProductName);
            //} 
            #endregion

            #region Custom SQL Expressions with Custom Classes
            //var productSummay = GetProductSummariesByCategory(1);
            //foreach (ProductSummary item in productSummay)
            //{
            //    Console.WriteLine(item.ProductID);
            //    Console.WriteLine(item.ProductName);
            //} 
            #endregion

            Product product = northwind.Products.Single(p => p.ProductName == "Entity Framework Training Course");
            northwind.Products.DeleteOnSubmit(product);
            northwind.SubmitChanges();
            Console.WriteLine("Deleted");
            Console.ReadKey();
            
        }

        private static IEnumerable<Product> GetProductsCategory(int catId)
        {
            NorthwindDataContext northwind = new NorthwindDataContext();
            return northwind.ExecuteQuery<Product>("select * from products where categoryid={0}", catId);
        }
        private static IEnumerable<ProductSummary> GetProductSummariesByCategory(int catId)
        {
            NorthwindDataContext northwind = new NorthwindDataContext();
            return northwind.ExecuteQuery<ProductSummary>(@"select ProductId, ProductName, UnitPrice 
                                                            from products where categoryId={0}", catId);
        }
        
    }
}
