static void WithoutInclude(AdsEntities db)
{
var adsAll = db.Ads;
foreach (var ad in adsAll)
{
Console.WriteLine(ad.Title);
Console.WriteLine(ad.AdStatus.Status);
Console.WriteLine(ad.Category); // Including .Name throws an exception
Console.WriteLine(ad.Town); // Including .Name throws an exception
Console.WriteLine(ad.AspNetUser.Name);
}
}
static void NonOptimized(AdsEntities db)
{
var ads = db.Ads
.ToList()
.Where(a => a.AdStatus.Status == "Published")
.Select(a => new
{
a.Title,
a.CategoryId,
a.TownId,
a.Date
})
.ToList()
.OrderBy(a => a.Date);
}
static void Main()
{
var context = new AdsEntities();
var ads = context.Ads
.Where(a => a.AdStatus.Status == "Published")
.Where(a => a.CategoryId != null)
.Where(a => a.TownId != null)
.Select(a => new
{
a.Title,
a.CategoryId,
a.TownId,
a.Date
})
.OrderBy(a => a.Date).ToList();
var stopwatch = new Stopwatch();
var avg = stopwatch.Elapsed;
for (int i = 0; i < 10; i++)
{
stopwatch.Start();
foreach (var ad in ads)
{
Console.WriteLine("{0} {1} {2}", ad.Title, ad.TownId, ad.CategoryId);
}
Console.WriteLine("To list : {0}", stopwatch.Elapsed);
Console.WriteLine();
avg += stopwatch.Elapsed;
stopwatch.Restart();
}
Console.WriteLine("To list avg: {0}", avg);
Console.WriteLine("To list avg: {0}", avg.Milliseconds);
Console.WriteLine("To list avg: {0}", avg.Milliseconds / 10);
stopwatch.Stop();
}
static void Main()
{
var db = new AdsEntities();
var sw = new Stopwatch();
// Problem 1**********************************************************************
// Without .Include
// WithoutInclude(db);
// With .Include
// WithInclude(db);
// Problem 2**********************************************************************
//db.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
//// Non-optimized
//sw.Start();
//NonOptimized(db);
//Console.WriteLine(sw.Elapsed);
//sw.Restart();
//// Optimized
//Optimized(db);
//Console.WriteLine(sw.Elapsed);
// Problem 3************************************************************************
//db.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
//// Everything
//sw.Start();
//SelectAll(db);
//Console.WriteLine(sw.Elapsed);
//sw.Restart();
//// Title only
//SelectTitleOnly(db);
//Console.WriteLine(sw.Elapsed);
}
static void SelectTitleOnly(AdsEntities db)
{
var ads = db.Ads
.Select(a => a.Title);
foreach (var ad in ads)
{
Console.Write(ad + " ");
}
}
static void SelectAll(AdsEntities db)
{
var ads = db.Ads;
foreach (var ad in ads)
{
Console.Write(ad.Title + " ");
}
}
public static void Main()
{
var context = new AdsEntities();
Stopwatch sw = new Stopwatch();
// Problem 1.Show Data from Related Tables
// Slow version /uncomment to test/:
var adsWithoutInclude = context.Ads;
foreach (var ad in adsWithoutInclude)
{
var title = ad.Title ?? "no title";
var status = ad.AdStatuses != null ? ad.AdStatuses.Status : "no status";
var category = ad.Categories != null ? ad.Categories.Name : "no category";
var town = ad.Towns != null ? ad.Towns.Name : "no town";
var user = ad.AspNetUsers != null ? ad.AspNetUsers.UserName : "******";
Console.WriteLine(
"Title: {0}, status: {1}, category: {2}, town: {3}, user: {4}",
title,
status,
category,
town,
user);
}
// Optimized version /uncomment to test/:
//var adsWithInclude = context.Ads
// .Include(a => a.AdStatuses)
// .Include(a => a.Categories)
// .Include(a => a.Towns)
// .Include(a => a.AspNetUsers);
//foreach (var ad in adsWithInclude)
//{
// var title = ad.Title ?? "no title";
// var status = ad.AdStatuses != null ? ad.AdStatuses.Status : "no status";
// var category = ad.Categories != null ? ad.Categories.Name : "no category";
// var town = ad.Towns != null ? ad.Towns.Name : "no town";
// var user = ad.AspNetUsers != null ? ad.AspNetUsers.UserName : "******";
// Console.WriteLine(
// "Title: {0}, status: {1}, category: {2}, town: {3}, user: {4}",
// title,
// status,
// category,
// town,
// user);
//}
// Problem 2.Play with ToList()
// Slow version /uncomment to test/:
//context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
//sw.Start();
//var adsToListNonOptimized = context.Ads
// .ToList()
// .OrderBy(a => a.Date)
// .Where(a => a.Categories != null && a.AdStatuses.Status == "Published")
// .Select(a => new
// {
// a.Title,
// Category = a.Categories != null ? a.Categories.Name : "no category",
// Town = a.Towns.Name
// })
// .ToList();
//Console.WriteLine(sw.Elapsed);
//// Optimized version /uncomment to test/:
//sw.Restart();
//var adsToListOptimized = context.Ads
// .OrderBy(a => a.Date)
// .Where(a => a.Categories != null && a.AdStatuses.Status == "Published")
// .Select(a => new
// {
// a.Title,
// Category = a.Categories != null ? a.Categories.Name : "no category",
// Town = a.Towns.Name
// })
// .ToList();
//Console.WriteLine(sw.Elapsed);
// Problem 3.Select Everything vs. Select Certain Columns
// slow version /uncomment to test/:
// context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
// sw.Start();
// var adsSelectStar = context.Ads;
// foreach (var ad in adsSelectStar)
// {
// Console.WriteLine(ad.Title);
// }
// Console.WriteLine(sw.Elapsed);
// sw.Restart();
// var adsSelectTitle = context.Ads.Select(a => a.Title);
// foreach (var title in adsSelectTitle)
// {
// Console.WriteLine(title);
// }
// Console.WriteLine(sw.Elapsed);
}