private static void GetAdsWhereStatusPublishedOptimized(AdsEntities context)
        {
            var ads = context.Ads
                .Where(a => a.AdStatus.Status == "Published")
                .Select(a => new
                {
                    Title = a.Title,
                    Category = a.Category == null ? "No category" : a.Category.Name,
                    Town = a.Town == null ? "No town" : a.Town.Name,
                    PublishDate = a.Date
                })
                .OrderBy(a => a.PublishDate);

            foreach (var ad in ads)
            {
                Console.WriteLine("Title: {0}, Category: {1}, Town: {2}, Publish Date: {3}",
                    ad.Title, ad.Category, ad.Town, ad.PublishDate);
            }
        }
        static void Main(string[] args)
        {
            var context = new AdsEntities();

            //  Problem 1.	Show Data from Related Tables

            //  Using Entity Framework write a SQL query to select all ads from the database and later print their title,
            //  status, category, town and user. Do not use Include(…) for the relationships of the Ads.
            //  Check how many SQL commands are executed with the SQL ExpressProfiler (or a similar tool).

            /*
            foreach (var ad in context.Ads)
            {
                Console.WriteLine("Title: {0}, Status: {1}, Category: {2}, Town: {3}, User: {4}",
                    ad.Title,
                    ad.AdStatus == null ? "No status": ad.AdStatus.Status,
                    ad.Category == null ? "No category":ad.Category.Name,
                    ad.Town == null ? "No town":ad.Town.Name,
                    ad.AspNetUser.Name);
            }
            */

            //  27 commands executed

            //  Add Include(…) to select statuses, categories, towns and users along with all ads.
            //  Compare the number of executed SQL statements and the performance before and after adding Include(…).

            /*
            foreach (var ad in context.Ads
                .Include(a => a.AdStatus)
                .Include(a => a.Category)
                .Include(a => a.Town)
                .Include(a => a.AspNetUser))
            {
                Console.WriteLine("Title: {0}, Status: {1}, Category: {2}, Town: {3}, User: {4}",
                    ad.Title,
                    ad.AdStatus == null ? "No status" : ad.AdStatus.Status,
                    ad.Category == null ? "No category" : ad.Category.Name,
                    ad.Town == null ? "No town" : ad.Town.Name,
                    ad.AspNetUser.Name);
            }
            */

            // 1 command executed.

            //---------------------------------------------------------------------------------------------------//

            //  Problem 2.	Play with ToList()

            //  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 sw = new Stopwatch();
            //  sw.Start();
            //  not optimize version
            /*
            GetAdsWhereStatusPublishedWithToListNonOptimized(context);
            Console.WriteLine(sw.Elapsed);
            */

            //  sw.Restart();
            //  optimize version
            /*
            GetAdsWhereStatusPublishedOptimized(context);
            Console.WriteLine(sw.Elapsed);
            */

            //  Problem 3.	Select Everything vs. Select Certain Columns

            var sw = new Stopwatch();
            sw.Start();
            SelectEverythingOfAds(context);
            Console.WriteLine(sw.Elapsed);

            sw.Restart();
            SelectTitleOfAds(context);
            Console.WriteLine(sw.Elapsed);
        }
        private static void SelectTitleOfAds(AdsEntities context)
        {
            var ads = context.Ads
                .Select(a => a.Title)
                .ToList();

            foreach (var ad in ads)
            {
                Console.WriteLine(ad);
            }
            Console.WriteLine();
        }
        private static void SelectEverythingOfAds(AdsEntities context)
        {
            var ads = context.Ads.ToList();

            foreach (var ad in ads)
            {
                Console.WriteLine(ad.Title);
            }
            Console.WriteLine();
        }
Esempio n. 5
0
        static void Main(string[] args)
        {
            // --------------Problem 1 ---------------

            var context = new AdsEntities();
            //var ads = context.Ads;

            //foreach (var ad in ads)
            //{
            //    Console.WriteLine("Title: {0}", ad.Title);
            //    Console.WriteLine("Status: {0}", ad.AdStatus.Status);
            //    Console.WriteLine("Category: {0}", ad.Category != null ? ad.Category.Name : "not specified");
            //    Console.WriteLine("Town: {0}", ad.Town != null ? ad.Town.Name : "not specified");
            //    Console.WriteLine("User: {0}", ad.AspNetUser.UserName);
            //    Console.WriteLine();
            //}

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

            //foreach (var ad in adsWithInclude)
            //{
            //    Console.WriteLine("Title: {0}", ad.Title);
            //    Console.WriteLine("Status: {0}", ad.AdStatus.Status);
            //    Console.WriteLine("Category: {0}", ad.Category != null ? ad.Category.Name : "not specified");
            //    Console.WriteLine("Town: {0}", ad.Town != null ? ad.Town.Name : "not specified");
            //    Console.WriteLine("User: {0}", ad.AspNetUser.UserName);
            //    Console.WriteLine();
            //}

            //+--------------------------+---------------+-----------------+
            //|                          | No Include(…) | With Include(…) |
            //+--------------------------+---------------+-----------------+
            //| Number of SQL statements |             28|                1|
            //+--------------------------+---------------+-----------------+


            // --------------Problem 2 ---------------


            var sw = new Stopwatch();

            //context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE");
            //sw.Start();
            //var adsSlow =
            //    context.Ads.ToList()
            //        .Where(a => a.AdStatus.Status == "Published")
            //        .Select(a => new { Title = a.Title, Category = a.Category, Town = a.Town, Date = a.Date })
            //        .ToList()
            //        .OrderBy(a => a.Date);

            //Console.WriteLine(sw.ElapsedMilliseconds);

            //context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE");
            //sw.Restart();
            //var adsFast =
            //    context.Ads
            //        .Where(a => a.AdStatus.Status == "Published")
            //        .Select(a => new { Title = a.Title, Category = a.Category, Town = a.Town, Date = a.Date })
            //        .OrderBy(a => a.Date).ToList();

            //Console.WriteLine(sw.ElapsedMilliseconds);


            // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+
            // |                    | Run 1 | Run 2 | Run 3 | Run 4 | Run 5 | Run 6 | Run 7 | Run 8 | Run 9 | Run 10 | Average Time |
            // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+
            // | Non-optimized (ms) |  191  | 195  |  248  |  163  |  197  |  230  | 164   |  177  |   175 |  183   |       192.3   |
            // | Optimized (ms)     |   77  |  71  |   118 |  78   |   81  |   96  |  78   |   83  |   78  |    83  |        84.3   |
            // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+


            // --------------Problem 3 ---------------


            sw.Restart();
            context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE");
            var notOptimized = context.Ads;

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

            Console.WriteLine();
            Console.WriteLine(sw.ElapsedMilliseconds);

            sw.Restart();
            context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS DBCC FREEPROCCACHE");
            var optimized = context.Ads.Select(a => a.Title);

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

            Console.WriteLine();
            Console.WriteLine(sw.ElapsedMilliseconds);


            // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+
            // |                    | Run 1 | Run 2 | Run 3 | Run 4 | Run 5 | Run 6 | Run 7 | Run 8 | Run 9 | Run 10 | Average Time |
            // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+
            // | Non-optimized (ms) | 386   | 379   |  502  |  491  |   516 |  348  |  418  |  330  |  490  |   423  |  428.3       |
            // | Optimized (ms)     |  91   |  121  |  105  | 103   |  78   |    85 |   67  |   78  |  94   |     73 |    89.5      |
            // +--------------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+--------------+
        }