Task3-4

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

namespace EntityFrameworkHomework
{
    class Program
    {
        static void Main(string[] args)
        {
            ViewCustomersOrders();
            Console.WriteLine("\nWith Native:");
            ViewCustomersOrdersNativeSQL();
        }

        private static void ViewCustomersOrdersNativeSQL()
        {
            NorthwindEntities context = new NorthwindEntities();
            string nativeSqlQuery =
                "SELECT c.ContactName " +
                "FROM dbo.Customers c ,dbo.Orders o " +
                "WHERE o.CustomerID = c.CustomerID " +
                "AND o.ShipCountry='Canada' " +
                "AND o.OrderDate IN ('1/1/1997','1/1/1998')";

            var customers = context.ExecuteStoreQuery<string>(nativeSqlQuery);

            foreach (var cust in customers)
            {
                Console.WriteLine(cust.ToString());
            }
        }

        private static void ViewCustomersOrders()
        {
            NorthwindEntities context = new NorthwindEntities();
            DateTime yearBegin = new DateTime(1997, 1, 1);
            DateTime yearFinish = new DateTime(1997, 12, 31);
            var customers =
                (from o in context.Orders
                 from c in context.Customers
                where (o.OrderDate >= yearBegin && o.OrderDate <= yearFinish) &&
                       o.ShipCountry == "Canada" && o.CustomerID==c.CustomerID
                select c.ContactName).Distinct();

            foreach (var cust in customers)
            {
                Console.WriteLine(cust);
            }

            /* --With N+1 Query Solving
            var customers =
                (from o in context.Orders
                 where (o.OrderDate >= yearBegin && o.OrderDate <= yearFinish) &&
                        o.ShipCountry == "Canada"
                 select o.CustomerID).Distinct();

            foreach (var c in context.Customers.Include("Orders"))
            {
                foreach (var item in customers)
                {
                    if (item == c.CustomerID)
                    {
                        Console.WriteLine(c.ContactName);
                    }
                }
            }
             */
        }
    }
}