// Note: I have referenced the AdsEntities from the previous project
    // instead of creating another one in this project.
    static void Main()
    {
        /*
         * Using Entity Framework select all ads from the database,
         * then invoke ToList(), then filter the categories whose status is Published;
         * then select the ad title, category and town, then invoke ToList()
         * again and finally order the ads by publish date.
         * Rewrite the same query in a more optimized way and compare the performance.
         */

        var context = new AdsEntities();

        Stopwatch sw = new Stopwatch();
        sw.Start();
        for (int count = 0; count < 10; count++)
        {
            var ads = context.Ads
            .Select(a => a)
            .ToList()
            .Where(a => a.AdStatus != null && a.AdStatus.Status == "Published")
            .Select(a => new
            {
                Title = a.Title,
                Category = a.Category != null ? a.Category.Name : "(null)",
                Town = a.Town != null ? a.Town.Name : "(null)",
                PublishDate = a.Date
            })
            .ToList()
            .OrderBy(a => a.PublishDate);
        }

        // Unoptimised: 5 seconds
        Console.WriteLine("Unoptimised: {0}", sw.Elapsed);
        sw.Restart();

        for (int count = 0; count < 10; count++)
        {
            var ads = context.Ads
            .Where(a => a.AdStatus != null && a.AdStatus.Status == "Published")
            .Select(a => new
            {
                Title = a.Title,
                Category = a.Category != null ? a.Category.Name : "(null)",
                Town = a.Town != null ? a.Town.Name : "(null)",
                PublishDate = a.Date
            })
            .OrderBy(a => a.PublishDate)
            .ToList();
        }

        // Optimised: 0.5 seconds
        Console.WriteLine("Optimised: {0}", sw.Elapsed);
        sw.Stop();
    }
    // Note: I have referenced the AdsEntities from the previous project
    // instead of creating another one in this project.
    static void Main()
    {
        /*
         * Write a program to compare the execution speed between these two scenarios:
         *      Select everything from the Ads table and print only the ad title.
         *      Select the ad title from Ads table and print it.
         */

        var context = new AdsEntities();
        Stopwatch sw = new Stopwatch();

        sw.Start();
        for (int count = 0; count < 10; count++)
        {
            var ads = context.Ads.Select(a => a);

            foreach (var ad in ads)
            {
                Console.WriteLine(ad.Title);
            }
        }

        TimeSpan unoptimisedTime = sw.Elapsed;
        sw.Restart();

        for (int count = 0; count < 10; count++)
        {
            var ads = context.Ads.Select(a => a.Title);

            foreach (var adTitle in ads)
            {
                Console.WriteLine(adTitle);
            }
        }

        TimeSpan optimisedTime = sw.Elapsed;
        sw.Stop();

        /*
         * Unoptimised: 5.5 seconds
         * Optimised: 0.2 seconds
         */

        Console.WriteLine();
        Console.WriteLine("Unoptimised: {0}", unoptimisedTime);
        Console.WriteLine("Optimised: {0}", optimisedTime);
    }
    static void Main()
    {
        var context = new AdsEntities();

        var ads = context.Ads.Select(a => a);

        // Without Include. - Around ~15 queries
        foreach (var ad in ads)
        {
            Console.WriteLine(" --- Title: {0} - Status: {1} - Category: {2}",
                ad.Title,
                ad.AdStatus != null ? ad.AdStatus.Status : "(null)" ,
                ad.Category != null ? ad.Category.Name : "(null)");

            Console.WriteLine("  -- Town: {0} - Author: {1}",
                ad.Town != null ? ad.Town.Name : "(null)",
                ad.AspNetUser != null ? ad.AspNetUser.UserName : "******");
        }

        var adsWithInclude = context.Ads
            .Select(a => a)
            .Include(a => a.Town)
            .Include(a => a.AdStatus)
            .Include(a => a.Category)
            .Include(a => a.AspNetUser);

        // With Include. Only 1 query
        foreach (var ad in adsWithInclude)
        {
            Console.WriteLine(" --- Title: {0} - Status: {1} - Category: {2}",
                ad.Title,
                ad.AdStatus != null ? ad.AdStatus.Status : "(null)",
                ad.Category != null ? ad.Category.Name : "(null)");

            Console.WriteLine("  -- Town: {0} - Author: {1}",
                ad.Town != null ? ad.Town.Name : "(null)",
                ad.AspNetUser != null ? ad.AspNetUser.UserName : "******");
        }
    }