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

namespace EDM_Perf_3._5
{
    class Program
    {
        static void Main(string[] args)
        {
            NorthwindEFEntities context = new NorthwindEFEntities();

            // Avoid projecting all columns in nested queries
            var query = (from p in context.Products
                         orderby p.ProductName
                         select p.ProductName)
                 .Skip(2).Take(2);

            var l = query.ToList();

            // Avoiding Unnecessary IS NULL Checks in LINQ to Entities Queries
            var query2 = context.Products.Join(
                            context.Categories,
                            p => p.ProductID,
                            c => c.CategoryID,
                            (p, c) => new { p.ProductID, c.CategoryID, p.ProductName, c.CategoryName });

            var l2 = query2.ToList();

            // Additional join elimination:  Eliminate parent table in "Child Left Outer Join Parent"
            //string entitySQL = "SELECT p.Category.CategoryId FROM Products as p";
            var query2a = from j in context.CreateQuery<DbDataRecord>
                        ("SELECT p.Category.CategoryId FROM Products as p")
                    select j;
            var l2a = query2a.ToList();

            //Using IN instead of nested ORs
            //string entitySQL2 = "SELECT p FROM Products as p where p.ProductId in {1, 2, 3, 4, 5}";

            // Or write a BuildContainsList- type function
            // http://social.msdn.microsoft.com/Forums/en-US/adodotnetentityframework/thread/095745fe-dcf0-4142-b684-b7e4a1ab59f0
            var query3 = context.Products.Where(p => p.ProductID == 1 || p.ProductID == 2 || p.ProductID == 3 || p.ProductID == 4 || p.ProductID == 5);

            var l3 = query3.ToList();

            // Translating more of LINQ's GroupBy operator into GROUP BY
            var query4 = context.Products.Where(p => p.ProductID < 4)
            .GroupBy(p => p.ProductID, p => p.ProductID, (key, group) =>
            new { Key = key, Max = group.Max() });

            var l4 = query4.ToList();

            // Avoiding “Cast (1 as bit)”, using “1” instead
            var query5 = context.Categories.Select(c => new { c.CategoryID, count = c.Products.Count() });

            var l5 = query5.ToList();

            // Simplifying some queries that go against a subtype in a type hierarchy
            //var query = this.tph.Entities.OfType<Tph.Derived1>().GroupBy(d2 => d2).Select(p => p.Max(g => g.Id));

            // StartsWith doesn't use LIKE operator

            var query6a = (from p in context.Products
                           where p.ProductName.StartsWith("Car")
                           select p);

            var l6a = query6a.ToList();

            // This generates an IS NULL
            var query7 = context.Employees.Where(e => e.Region == null);

            var l7 = query7.ToList();


            // This does too, although ProductName is a non-nullable column 
            var query8 = context.Products.Where(p => p.ProductName == null);

            var l8 = query8.ToList();

        }
    }
}
