using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Objects;
using System.Data.Common;

namespace Projekt_ModelEncji_Zapytania
{
    class Program
    {
        static void Main(string[] args)
        {
            PobieranieDanychPrzezProcedure();
            Console.ReadKey();
        }

        static void PobieranieDanychPrzezProcedure()
        {
            using (var context = new AdventureWorksEntities())
            {
                var query = context.PobierzNProduktow(10);

                int licznik = 1;

                foreach (Product p in query)
                {
                    Console.WriteLine("{0}: Nazwa Produktu = {1}", licznik, p.Name);
                    licznik++;
                }
            }
        }


        static void UsunPodkategorieProduktu()
        {
            using (var context = new AdventureWorksEntities())
            {
                var query = from p in context.ProductSubcategory
                            where p.Name.Equals("Testowa podkategoria produktu - modyfikacja")
                            select p;
                foreach (ProductSubcategory ps in query)
                {
                    context.DeleteObject(ps);
                }

                context.SaveChanges();
            }
        }


        static void ModyfikujPodkategorieProduktu()
        {
            using (var context = new AdventureWorksEntities())
            {
                var query = from p in context.ProductSubcategory
                            where p.Name.Equals("Testowa podkategoria produktu")
                            select p;

                foreach (ProductSubcategory ps in query)
                {
                    ps.Name += " - modyfikacja";
                }
                context.SaveChanges();
            }
        }


        static void DodajPodkategorieProduktu()
        {
            using (var context = new AdventureWorksEntities())
            {
                ProductSubcategory productSubcategory = new ProductSubcategory();
                productSubcategory.Name = "Testowa podkategoria produktu";
                productSubcategory.ProductCategoryID = 1;
                context.AddToProductSubcategory(productSubcategory);

                context.SaveChanges();
            }
        }


        static void OdowlanieDoObiektowWRelacjiLeniweLadowanie()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                context.ContextOptions.LazyLoadingEnabled = true;
                var query = context.ProductModel;

                foreach (ProductModel pm in query)
                {
                    Console.WriteLine("Nazwa modelu:{0}, Liczba produktów w relacji:{1}", pm.Name, pm.Product.Count);
                }
            }
        }


        static void OdowlanieDoObiektowWRelacji()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                var query = context.ProductModel;

                foreach (ProductModel pm in query)
                {
                    if (!pm.Product.IsLoaded)
                        pm.Product.Load();
                    Console.WriteLine("Nazwa modelu:{0}, Liczba produktów w relacji:{1}", pm.Name, pm.Product.Count);
                }
            }
        }

        
        //static void OdowlanieDoObiektowWRelacji()
        //{
        //    using (AdventureWorksEntities context = new AdventureWorksEntities())
        //    {
        //        //var query = context.ProductModel;
        //        var query = context.ProductModel.Include("Product");

        //        foreach (ProductModel pm in query)
        //        {

        //            Console.WriteLine("Nazwa modelu:{0}, Liczba produktów w relacji:{1}", pm.Name, pm.Product.Count);
        //        }
        //    }
        //}


        static void UsuwanieObiektuEncji()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                var query = from p in context.Product
                            where p.Name.Equals("ZmienionyProduktTestowy")
                            select p;
                Product produkt = query.FirstOrDefault();

                if (produkt != null)
                {
                    context.DeleteObject(produkt);
                    context.SaveChanges();
                }
            }
        }


        static void ModyfikacjaObiektuEncji()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                var query = from p in context.Product
                            where p.Name.Equals("TestProduct")
                            select p;
                Product produkt = query.FirstOrDefault();

                if (produkt != null)
                {
                    produkt.Name = "ZmienionyProduktTestowy";
                    context.SaveChanges();
                }
            }
        }


        static void TestTworzeniaNowegoObiektu()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                var query = from p in context.Product
                            where p.ProductNumber.Equals("AR5382")
                            select p;

                foreach (var p in query)
                {
                    Console.WriteLine("Nazwa produktu: {0}, Numer produktu:{1}", p.Name, p.ProductNumber);
                }
            }
        }


        static void TworzenieNowegoObiektuEncji()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                Product p = Product.CreateProduct(10000,
                                                    "TestProduct",
                                                    "AR5382",
                                                    false,
                                                    false,
                                                    1000,
                                                    750,
                                                    0,
                                                    0,
                                                    1,
                                                    DateTime.Now,
                                                    Guid.NewGuid(),
                                                    DateTime.Now
                                                    );

                context.AddToProduct(p);
                context.SaveChanges();
            }
        }


        static void ProsteZapytanieRownolegleTestWatkow()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                var query = from c in
                                (
                                from p in context.Product.AsParallel<Product>()
                                select new
                                {
                                    Produkt = p,
                                    WatekID = System.Threading.Thread.CurrentThread.ManagedThreadId
                                }
                                )
                            group c by c.WatekID into g
                            select new
                            {
                                Klucz = g.Key,
                                ProduktWatek = g
                            };
                foreach (var q in query)
                {
                    Console.WriteLine("ID Wątku:{0}, Ilość Produktów:{1}", q.Klucz, q.ProduktWatek.Count());
                    foreach (var p in q.ProduktWatek)
                    {
                        Console.WriteLine("\tNazwa Produktu:{0}", p.Produkt.Name);
                    }

                }

            }
        }


        static void ProsteZapytanieRownolegle()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                var query = from p in context.Product.AsParallel<Product>()
                            select p;

                query.ForAll<Product>(p => Console.WriteLine(p.Name));
            }
        }


        static void UzycieOperatoraOrderbyEntitySQL()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                string esql = "SELECT VALUE p FROM AdventureWorksEntities.Product as p " +
                               "ORDER BY p.Name DESC";
                ObjectQuery<Product> query = context.CreateQuery<Product>(esql);

                foreach (Product p in query)
                {
                    Console.WriteLine("Produkt:{0}", p.Name);
                }
            }
        }


        static void UzycieOperatoraOrderby()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                var query = from p in context.Product
                            orderby p.Name descending
                            select p;

                foreach (Product p in query)
                {
                    Console.WriteLine("Produkt:{0}", p.Name);
                }
            }
        }


        static void UzycieWłaściwościNawigacyjnychEntitySQL()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                string esql = "SELECT p.Name AS Produkt, p.ProductModel.Name AS Model FROM AdventureWorksEntities.Product as p " +
                               "WHERE p.ProductModel.Name = 'Classic Vest'";
                ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

                foreach (var item in query)
                {
                    Console.WriteLine("Produkt:{0}, Model:{1}", item["Produkt"], item["Model"]);
                }
            }
        }


        static void UzycieWłaściwościNawigacyjnych()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                var query = from p in context.Product
                            select new { Produkt = p.Name, Model = p.ProductModel.Name, Kategoria = p.ProductSubcategory.Name };

                foreach (var item in query)
                {
                    Console.WriteLine("Produkt:{0}, Model:{1}, Kategoria:{2}", item.Produkt, item.Model, item.Kategoria);
                }
            }
        }


        static void UzycieOperatoraGroupByEntitySQL()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                string esql = "SELECT p.Color AS Kolor, Count(p.Name) AS Ilosc FROM AdventureWorksEntities.Product AS p " +
                              "GROUP BY p.Color " +
                              "HAVING p.Color IS NOT NULL";
                ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

                foreach (var item in query)
                {
                    Console.WriteLine("Kolor:{0}, Ilosc:{1}", item["Kolor"], item["Ilosc"]);
                }
            }
        }


        static void UzycieOperatoraGroupBy()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                var query = from p in context.Product
                            group p by p.Color into c
                            where !c.Key.Equals(null)
                            select new { Kolor = c.Key, Ilosc = c.Count() };

                foreach (var item in query)
                {
                    Console.WriteLine("Kolor:{0}, Ilosc:{1}", item.Kolor, item.Ilosc);
                }
            }
        }



        static void UzycieOperatoraSelectEntitySQL()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                string esql = "SELECT p.Name AS Nazwa, p.Color AS Kolor FROM AdventureWorksEntities.Product AS p ";
                ObjectQuery<DbDataRecord> query = context.CreateQuery<DbDataRecord>(esql);

                foreach (var item in query)
                {
                    Console.WriteLine("Nazwa:{0}, Kolor:{1}", item["Nazwa"], item["Kolor"]);
                }
            }
        }


        static void UzycieOperatoraSelect()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                var query = from p in context.Product
                            select new { Nazwa = p.Name, Kolor = p.Color };

                foreach (var item in query)
                {
                    Console.WriteLine("Nazwa:{0}, Kolor:{1}", item.Nazwa, item.Kolor);
                }
            }
        }


        static void UzycieOperatoraWhereEntitySQL()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                string esql = "SELECT VALUE p FROM AdventureWorksEntities.Product AS p " +
                              "WHERE p.Color = 'Black' ";
                ObjectQuery<Product> query = context.CreateQuery<Product>(esql);

                foreach (Product p in query)
                {
                    Console.WriteLine("Nazwa:{0}, Kolor:{1}", p.Name, p.Color);
                }
            }
        }


        static void UzycieOperatoraWhere()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                var query = from p in context.Product
                            where p.Color.Equals("Black")
                            select p;

                foreach (Product p in query)
                {
                    Console.WriteLine("Nazwa:{0}, Kolor:{1}", p.Name, p.Color);
                }
            }
        }


        static void PierwszeZapytanieEntitySQL()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                string esql = "SELECT VALUE p FROM AdventureWorksEntities.Product AS p";
                ObjectQuery<Product> products = context.CreateQuery<Product>(esql);

                int licznik = 0;
                foreach (Product p in products)
                {
                    licznik++;
                    Console.WriteLine("Numer = {0}, Nazwa = {1}", licznik, p.Name);
                }
                Console.WriteLine("Znaleziono {0} produktów", licznik);
            }
        }


        static void PierwszeZapytanieLinq2Ent()
        {
            using (AdventureWorksEntities context = new AdventureWorksEntities())
            {
                ObjectSet<Product> products = context.Product;

                int licznik = 0;
                foreach (Product p in products)
                {
                    licznik++;
                    Console.WriteLine("Numer = {0}, Nazwa = {1}", licznik, p.Name);
                }
                Console.WriteLine("Znaleziono {0} produktów", licznik);
            }
        }
    }

}
