private static string nonOptimizedLoop(Stopwatch sw, int i)
{
var ctx = new AdsEntities();
var b = ctx.Ads.Count();
//string txt = "CHECKPOINT;GO;DBCC DROPCLEANBUFFERS;GO;DBCC FREEPROCCACHE;GO;";
//using (var connection = new SqlConnection("Data Source=.;Initial Catalog=Ads;Integrated Security=True"))//also not working
//{
// using (var command = connection.CreateCommand())
// {
// connection.Open();
// command.CommandText = txt;
// }
//}
ctx.Database.ExecuteSqlCommand(@"CHECKPOINT");
ctx.Database.ExecuteSqlCommand(@"DBCC DROPCLEANBUFFERS");
ctx.Database.ExecuteSqlCommand(@"DBCC FREEPROCCACHE");
sw.Restart();
var allAds = ctx.Ads.ToList()
.Where(a => a.AdStatus.Status == "Published")
.Select(a => new
{
a.Title,
a.Category,
a.Town,
a.Date
}).ToList()
.OrderBy(a => a.Date);
var time = sw.ElapsedMilliseconds;
Console.WriteLine("Non-optimized Run{0}: {1}", i + 1, time);
return time.ToString();
}
private static void ShowAds()
{
var db = new AdsEntities();
var ad = db.Ads.ToList();
var starTime = DateTime.Now;
var ads = db.Ads
.ToList()
.Where(a => a.AdStatus.Status == "Published")
.Select(a => new
{
a.Title,
a.Category,
a.Town,
a.Date
})
.ToList()
.OrderBy(a => a.Date);
var endTime = DateTime.Now;
Console.WriteLine(endTime - starTime);
Console.WriteLine(ads.Count());
}
static void Main()
{
var db = new AdsEntities();
var start = db.Ads.Count();
var stopwatch = new Stopwatch();
TimeSpan average = new TimeSpan(0);
StreamWriter file = new StreamWriter("result.txt");
for (int i = 0; i < 10; i++)
{
stopwatch.Start();
foreach (var ad in db.Ads)
{
Console.WriteLine(ad.Title);
}
stopwatch.Stop();
average += stopwatch.Elapsed;
file.WriteLine(stopwatch.Elapsed);
stopwatch.Reset();
}
file.WriteLine(average.TotalSeconds / 10);
file.Close();
}
public static void TakeAddsWithInclude(AdsEntities context)
{
var ads = context.Ads
.Include(a => a.AdStatus)
.Include(a => a.Category)
.Include(a => a.Town)
.Include(a => a.AspNetUser);
foreach (var ad in ads)
{
Console.WriteLine(ad.Title);
Console.WriteLine("Status: " + ad.AdStatus.Status);
Console.WriteLine("Category: ");
if (ad.CategoryId != null)
{
Console.Write(ad.Category.Name);
}
Console.WriteLine("Town: ");
if (ad.TownId != null)
{
Console.WriteLine(ad.Town.Name);
}
Console.WriteLine("User: " + ad.AspNetUser.Name);
Console.WriteLine();
}
}
static void Main()
{
var context = new AdsEntities();
var c = context.Ads.Count();
var sw = new Stopwatch();
sw.Start();
var allAddsStaightforward = context.Ads;
PrintAdInfo(context, allAddsStaightforward);
Console.WriteLine("\nTime for execution: " + sw.Elapsed);
Console.WriteLine("\n\n");
sw.Restart();
var allAddsPartialWithInclude = context.Ads
.Include(a => a.AdStatus)
.Include(a => a.Category)
.Include(a => a.Town)
.Include(a => a.AspNetUser);
PrintAdInfo(context, allAddsPartialWithInclude);
Console.WriteLine("\nTime for execution: " + sw.Elapsed);
sw.Stop();
}
private static string optimizedLoop(Stopwatch sw, int i)
{
var ctx = new AdsEntities();
var b = ctx.Ads.Count();
ctx.Database.ExecuteSqlCommand(@"CHECKPOINT");
ctx.Database.ExecuteSqlCommand(@"DBCC DROPCLEANBUFFERS");
ctx.Database.ExecuteSqlCommand(@"DBCC FREEPROCCACHE");
sw.Restart();
var allAdsOptimized = ctx.Ads.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.Select(a => new
{
a.Title,
a.Category,
a.Town
}).ToList();
sw.Stop();
var time = sw.ElapsedMilliseconds;
Console.WriteLine("Optimized Run{0}: {1}", i + 1, time);
Console.WriteLine();
return(time.ToString());
}
private static string nonOptimizedLoop(Stopwatch sw, int i)
{
var ctx = new AdsEntities();
var b = ctx.Ads.Count();
//string txt = "CHECKPOINT;GO;DBCC DROPCLEANBUFFERS;GO;DBCC FREEPROCCACHE;GO;";
//using (var connection = new SqlConnection("Data Source=.;Initial Catalog=Ads;Integrated Security=True"))//also not working
//{
// using (var command = connection.CreateCommand())
// {
// connection.Open();
// command.CommandText = txt;
// }
//}
ctx.Database.ExecuteSqlCommand(@"CHECKPOINT");
ctx.Database.ExecuteSqlCommand(@"DBCC DROPCLEANBUFFERS");
ctx.Database.ExecuteSqlCommand(@"DBCC FREEPROCCACHE");
sw.Restart();
var allAds = ctx.Ads.ToList()
.Where(a => a.AdStatus.Status == "Published")
.Select(a => new
{
a.Title,
a.Category,
a.Town,
a.Date
}).ToList()
.OrderBy(a => a.Date);
var time = sw.ElapsedMilliseconds;
Console.WriteLine("Non-optimized Run{0}: {1}", i + 1, time);
return(time.ToString());
}
static void Main()
{
var sw = new Stopwatch();
var ctx = new AdsEntities();
ctx.Database.ExecuteSqlCommand(@"CHECKPOINT");
ctx.Database.ExecuteSqlCommand(@"DBCC DROPCLEANBUFFERS");
ctx.Database.ExecuteSqlCommand(@"DBCC FREEPROCCACHE");
sw.Start();
string outputNonOptimized, outputOptimized;
outputNonOptimized = outputOptimized = "";
for (int i = 0; i < 10; i++)
{
outputNonOptimized += string.IsNullOrEmpty(outputNonOptimized) ? nonOptimizedLoop(sw, i) : " " + nonOptimizedLoop(sw, i);
outputOptimized += outputOptimized == "" ? optimizedLoop(sw, i) : " " + optimizedLoop(sw, i);
}
outputNonOptimized += " " + Regex.Split(outputNonOptimized, @"\s+").Select(int.Parse).Average();
outputOptimized += " " + Regex.Split(outputOptimized, @"\s+").Select(int.Parse).Average();
Console.WriteLine(outputNonOptimized);
Console.WriteLine(outputOptimized);
}
static void Main()
{
var db = new AdsEntities();
var start = db.Ads.Count();
var stopwatch = new Stopwatch();
TimeSpan average = new TimeSpan(0);
StreamWriter file = new StreamWriter("result.txt");
for (int i = 0; i < 10; i++)
{
stopwatch.Start();
var ads = db.Ads
.Include(a => a.Category)
.Include(a => a.Town)
.Include(a => a.AdStatus)
.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.Select(a => new
{
a.Title,
Category = a.Category,
Town = a.Town
});
stopwatch.Stop();
average += stopwatch.Elapsed;
Console.WriteLine(stopwatch.Elapsed);
file.WriteLine(stopwatch.Elapsed);
}
Console.WriteLine("Average: " + average.TotalSeconds / 10);
file.WriteLine(average.TotalSeconds / 10);
file.Close();
}
static void Main()
{
var context = new AdsEntities();
//TakeAddsWithoutInclude(context);
TakeAddsWithInclude(context);
Console.ReadLine();
}
public static List<Ad> WithInclude(AdsEntities db)
{
return
db.Ads.Include(a => a.AdStatus)
.Include(a => a.AspNetUser)
.Include(a => a.Category)
.Include(a => a.Town)
.ToList();
}
static void Main()
{
var context = new AdsEntities();
context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
var sw = new Stopwatch();
Console.WriteLine(context.Ads.Any());
sw.Start();
// Messy query
var ads = 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("Millisecond with a messy query: " + sw.ElapsedMilliseconds + "ms");
sw.Restart();
var adsImproved = 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("Millisecond with a proper query: " + sw.ElapsedMilliseconds + "ms");
// TEST RESULTS:
//+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+---------+
//| | Run 1 | Run 2 | Run 3 | Run 4 | Run 5 | Run 6 | Run 7 | Run 8 | Run 9 | Run 10 | Average |
//+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+---------+
//| Non-optimized | 237 | 245 | 243 | 247 | 256 | 237 | 236 | 266 | 236 | 237 | 244ms |
//+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+---------+
//| Optimized | 123 | 122 | 125 | 128 | 121 | 123 | 123 | 121 | 122 | 123 | 123ms |
//+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+---------+
// Improvement - Almost 2 (1.98) times faster.
}
public static void Main(string[] args)
{
#region Problem 1. Show Data from Related Tables
using (var db = new AdsEntities())
{
ConsolePrintAds(DataFromRelatedTables.WithoutInclude(db));
}
using (var db = new AdsEntities())
{
ConsolePrintAds(DataFromRelatedTables.WithInclude(db));
}
#endregion
#region Problem 2. Play with ToList()
using (var db = new AdsEntities())
{
var adsSlow = db.Ads.ToList()
.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.ToList()
.Select(a => new { a.Title, a.Category, a.Town });
}
using (var db = new AdsEntities())
{
var adsSlow = db.Ads
.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.Select(a => new { a.Title, a.Category, a.Town })
.ToList();
}
#endregion
#region Problem 3. Select Everything vs. Select Certain Columns
using (var db = new AdsEntities())
{
var adsTitle = db.Ads.Select(a => new { a.Title }).ToList();
}
using (var db = new AdsEntities())
{
var adsTitle = db.Ads.ToList();
}
#endregion
}
public static void Main()
{
var db = new AdsEntities();
using (db)
{
var ads = db.Ads;
SlowWayShowData(ads);
//FastWayShowData(ads);
}
}
private static void PrintAdInfo(AdsEntities ctx, IQueryable<Ad> ads)
{
foreach (var ad in ads)
{
Console.WriteLine("Ad's title: {0}, Status: {1}, Category: {2}, Town: {3}, Created by: {4}",
ad.Title,
ad.AdStatus.Status,
ad.Category == null ? "<<Unknown Category>>" : ad.Category.Name,
ad.Town == null ? "<<No Data for Town>>" : ad.Town.Name,
ad.AspNetUser.Name);
}
//not all of the Ads's table columns are required. So we need to chech whether we have an existing connection between the tables, before we try to join them
}
private static void PrintAdInfo(AdsEntities ctx, IQueryable <Ad> ads)
{
foreach (var ad in ads)
{
Console.WriteLine("Ad's title: {0}, Status: {1}, Category: {2}, Town: {3}, Created by: {4}",
ad.Title,
ad.AdStatus.Status,
ad.Category == null ? "<<Unknown Category>>" : ad.Category.Name,
ad.Town == null ? "<<No Data for Town>>" : ad.Town.Name,
ad.AspNetUser.Name);
}
//not all of the Ads's table columns are required. So we need to chech whether we have an existing connection between the tables, before we try to join them
}
static void Main()
{
// Variant 1. queryCount = 30
var db = new AdsEntities();
var ad = db.Ads.ToList();
var starTime = DateTime.Now;
var ads = db.Ads
.ToList()
.Where(a => a.AdStatus.Status == "Published")
.Select(a => new
{
a.Title,
a.Category,
a.Town,
a.Date
})
.ToList()
.OrderBy(a => a.Date);
var endTime = DateTime.Now;
Console.WriteLine(endTime - starTime);
Console.WriteLine(ads.Count());
// Variant 2. queryCount = 1
starTime = DateTime.Now;
var ads2 = db.Ads
.Where(a => a.AdStatus.Status == "Published")
//.OrderBy(a => a.Date)
.Select(a => new
{
a.Title,
a.Category,
a.Town
})
.ToList();
endTime = DateTime.Now;
Console.WriteLine(endTime - starTime);
Console.WriteLine(ads2.Count());
}
static void PrintAdsWithNPlusOneQueryProblem()
{
Console.WriteLine("List employees (with N+1 query problem):");
var ads = new List <string>();
var startTime = DateTime.Now;
var context = new AdsEntities();
foreach (var ad in context.Ads)
{
ads.Add(String.Format("Add: Title = {0}; Status = {1}; Town = {2}; User = {3}",
ad.Title, ad.AdStatus.Status, ad.Category.Name, ad.Town.Name, ad.AspNetUser.Name));
}
Console.WriteLine("Time elapsed: {0}", DateTime.Now - startTime);
Console.WriteLine(String.Join("\n", ads.Take(5)));
Console.WriteLine("...");
Console.WriteLine();
}
static void Main()
{
var context = new AdsEntities();
var stopwatch = new Stopwatch();
Console.WriteLine(context.Ads.Any());
stopwatch.Start();
var allAdsNoInclude = context.Ads.ToList();
Console.WriteLine(stopwatch.ElapsedMilliseconds);
//foreach (var ad in allAdsNoInclude)
//{
// Console.WriteLine("Ad Title: {0}, Ad Status: {1}, Ad Category: {2}, Ad Town: {3}, Ad User: {4}",
// ad.Title, ad.AdStatus.Status, (ad.Category == null ? "no category" : ad.Category.Name), (ad.Town == null ? "no town" : ad.Town.Name), ad.AspNetUser.Name);
//}
stopwatch.Restart();
var allAdsInclude = context.Ads
.Include("Category")
.Include("Town")
.Include("AspNetUser")
.Include("AdStatus")
.ToList();
Console.WriteLine(stopwatch.ElapsedMilliseconds);
//foreach (var ad in allAdsInclude)
//{
// Console.WriteLine("Ad Title: {0}, Ad Status: {1}, Ad Category: {2}, Ad Town: {3}, Ad User: {4}",
// ad.Title, ad.AdStatus.Status, (ad.Category == null ? "no category" : ad.Category.Name), (ad.Town == null ? "no town" : ad.Town.Name), ad.AspNetUser.Name);
//}
// TESTS RESULTS:
//+--------------------------+-----------------+-------------------+
//| | No Include(...) | With Include(...) |
//+--------------------------+-----------------+-------------------+
//| Number of SQL Statements | 29 | 1 |
//+--------------------------+-----------------+-------------------+
//| Milliseconds to complete | 123 | 164 |
//+--------------------------+-----------------+-------------------+
}
private static void SelectEverythingFromAds()
{
var context = new AdsEntities();
DateTime start = DateTime.Now;
var ads = context.Ads.ToList();
DateTime end = DateTime.Now;
Console.WriteLine(new string('_', 52));
Console.WriteLine("|{0,-50}|", "Ad title");
Console.WriteLine(new string('-', 52));
foreach (var ad in ads)
{
Console.WriteLine("|{0,-50}|", ad.Title);
}
Console.WriteLine(new string('-', 52));
Console.WriteLine("Time to query: {0}\n", end - start);
}
private static string optimizedLoop(Stopwatch sw, int i)
{
var ctx = new AdsEntities();
ctx.Database.ExecuteSqlCommand(@"CHECKPOINT");
ctx.Database.ExecuteSqlCommand(@"DBCC DROPCLEANBUFFERS");
ctx.Database.ExecuteSqlCommand(@"DBCC FREEPROCCACHE");
var b = ctx.Ads.Count();
sw.Restart();
var allAdsOpti = ctx.Ads.Select(a => a.Title);
foreach (var ad in allAdsOpti)
{
Console.WriteLine(ad);
}
var time = sw.ElapsedMilliseconds;
Console.WriteLine("Non-optimized Run{0}: {1}", i + 1, time);
return(time.ToString());
}
public static void Main()
{
var db = new AdsEntities();
using (db)
{
var ads = db.Ads;
var timer = new Stopwatch();
timer.Restart();
SlowVersion(ads);
Console.WriteLine("Elapsed time: {0}", timer.Elapsed);
timer.Restart();
FastVersion(ads);
Console.WriteLine("Elapsed time: {0}", timer.Elapsed);
timer.Stop();
}
}
private static string optimizedLoop(Stopwatch sw, int i)
{
var ctx = new AdsEntities();
var b = ctx.Ads.Count();
ctx.Database.ExecuteSqlCommand(@"CHECKPOINT");
ctx.Database.ExecuteSqlCommand(@"DBCC DROPCLEANBUFFERS");
ctx.Database.ExecuteSqlCommand(@"DBCC FREEPROCCACHE");
sw.Restart();
var allAdsOptimized = ctx.Ads.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.Select(a => new
{
a.Title,
a.Category,
a.Town
}).ToList();
sw.Stop();
var time = sw.ElapsedMilliseconds;
Console.WriteLine("Optimized Run{0}: {1}", i+1, time);
Console.WriteLine();
return time.ToString();
}
static void Main()
{
var context = new AdsEntities();
var sw = new Stopwatch();
Console.WriteLine(context.Ads.Any());
sw.Start();
var ads = context.Ads.ToList();
Console.WriteLine(sw.ElapsedMilliseconds);
sw.Restart();
var adsTitle = context.Ads.Select(a => a.Title).ToList();
Console.WriteLine(sw.ElapsedMilliseconds);
//foreach (var ad in ads)
//{
// Console.WriteLine(ad.Title);
//}
//foreach (var title in adsTitle)
//{
// Console.WriteLine(title);
//}
// TEST RESULTS:
//+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+---------+
//| | Run 1 | Run 2 | Run 3 | Run 4 | Run 5 | Run 6 | Run 7 | Run 8 | Run 9 | Run 10 | Average |
//+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+---------+
//| Non-optimized | 120 | 120 | 120 | 120 | 123 | 123 | 120 | 123 | 124 | 122 | 122ms |
//+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+---------+
//| Optimized | 10 | 10 | 9 | 10 | 9 | 9 | 9 | 9 | 12 | 9 | 10ms |
//+---------------+-------+-------+-------+-------+-------+-------+-------+-------+-------+--------+---------+
// Takeaway: Nearly 12 times faster for such a simple query (queries are cached)
}
private static void SelectAllAdsToListOptimized()
{
var context = new AdsEntities();
var ads = context.Ads
.Where(a => a.AdStatus.Status.Equals("Published"))
.OrderBy(a => a.Date)
.Select(a => new { a.Title, a.Category, a.Town, a.Date })
.ToList();
Console.WriteLine(new string('_', 102));
Console.WriteLine("|{0,-40}|{1,-16}|{2,-15}|{3,-26}|", "Ad title", "Category", "Town", "Date");
Console.WriteLine(new string('-', 102));
foreach (var ad in ads)
{
var categoryName = ad.Category == null ? "no category" : ad.Category.Name;
var townName = ad.Town == null ? "no town" : ad.Town.Name;
Console.WriteLine("|{0,-40}|{1,-16}|{2,-15}|{3,-26}|", ad.Title, categoryName, townName, ad.Date);
}
Console.WriteLine(new string('-', 102));
}
static void Main()
{
var context = new AdsEntities();
var c = context.Ads.Count();
var sw = new Stopwatch();
sw.Start();
var allAddsStaightforward = context.Ads;
PrintAdInfo(context, allAddsStaightforward);
Console.WriteLine("\nTime for execution: " + sw.Elapsed);
Console.WriteLine("\n\n");
sw.Restart();
var allAddsPartialWithInclude = context.Ads
.Include(a => a.AdStatus)
.Include(a => a.Category)
.Include(a => a.Town)
.Include(a => a.AspNetUser);
PrintAdInfo(context, allAddsPartialWithInclude);
Console.WriteLine("\nTime for execution: " + sw.Elapsed);
sw.Stop();
}
static void Main()
{
var context = new AdsEntities();
//Problem 1. Show Data from Related Tables
//Without INCLUDE
//var ads = context.Ads;
//foreach (var ad in ads)
//{
// if (ad.TownId == null || ad.CategoryId == null)
// {
// continue;
// }
// else
// {
// Console.WriteLine("{0} {1} {2} {3} {4}",
// ad.Title,
// ad.AdStatus.Status,
// ad.Category.Name,
// ad.Town.Name,
// ad.AspNetUser.Name
// );
// }
//}
//Using INCLUDE
//var ads = context.Ads
// .Include(ad => ad.Category)
// .Include(ad => ad.AdStatus)
// .Include(ad => ad.Town)
// .Include(ad => ad.AspNetUser);
//foreach (var ad in ads)
//{
// if (ad.TownId == null || ad.CategoryId == null)
// {
// continue;
// }
// else
// {
// Console.WriteLine("{0} {1} {2} {3} {4}",
// ad.Title,
// ad.AdStatus.Status,
// ad.Category.Name,
// ad.Town.Name,
// ad.AspNetUser.Name
// );
// }
//}
//Problem 2. Play with ToList()
//Non-optimized
//context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
//var stopwatch = System.Diagnostics.Stopwatch.StartNew();
//var ads = context.Ads
// .ToList()
// .Where(ad=>ad.AdStatus.Status=="Published")
// .Select(ad => new
//{
// ad.Title,
// ad.Date,
// Category = ad.Category.Name,
// Town = ad.Town.Name
//}).ToList().OrderBy(ad => ad.Date);
//stopwatch.Stop();
//Console.WriteLine("Time elapsed: {0}",
// stopwatch.Elapsed);
//Optimized
//context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
//var stopwatch = System.Diagnostics.Stopwatch.StartNew();
//var ads = context.Ads
// .Where(ad=>ad.AdStatus.Status=="Published").OrderBy(ad => ad.Date)
// .Select(ad => new
//{
// ad.Title,
// ad.Date,
// Category = ad.Category.Name,
// Town = ad.Town.Name
//}).ToList();
//stopwatch.Stop();
//Console.WriteLine("Time elapsed: {0}",
// stopwatch.Elapsed);
//Problem 3. Select Everything vs. Select Certain Columns
//Non-optimized
//context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
//var stopwatch = System.Diagnostics.Stopwatch.StartNew();
//var ads = context.Ads;
//foreach (var ad in ads)
//{
// Console.WriteLine(ad.Title);
//}
//stopwatch.Stop();
//Console.WriteLine("Time elapsed: {0}",
// stopwatch.Elapsed);
//Optimized
//context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS;");
//var stopwatch = System.Diagnostics.Stopwatch.StartNew();
//var ads = context.Ads.Select(ad => ad.Title);
//foreach (var ad in ads)
//{
// Console.WriteLine(ad);
//}
//stopwatch.Stop();
//Console.WriteLine("Time elapsed: {0}",
// stopwatch.Elapsed);
}
static void Main()
{
var context = new AdsEntities();
context.Database.ExecuteSqlCommand("CHECKPOINT; DBCC DROPCLEANBUFFERS");
//Problem 1
//foreach (var ad in context.Ads
//.Include(ad => ad.AdStatus)
//.Include(ad => ad.Category)
//.Include(ad => ad.Town)
//.Include(ad => ad.AspNetUser))
//{
// System.Console.WriteLine("{0} {1} {2} {3} {4}",
// ad.Title,
// ad.AdStatus.Status,
// (ad.Category != null) ? ad.Category.Name : "",
// (ad.Town != null) ? ad.Town.Name : "",
// ad.AspNetUser.Name);
//}
//Problem 2
var stop = new Stopwatch();
stop.Start();
var allAdsRaw = context.Ads
.ToList()
.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.Select(ad => new
{
ad.Title,
ad.Category,
ad.Town
})
.ToList();
stop.Stop();
System.Console.WriteLine("Raw: {0}", stop.Elapsed);
stop.Reset();
stop.Start();
var allAdsOptimised = context.Ads
.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.Select(ad => new
{
ad.Title,
ad.Category,
ad.Town
})
.ToList();
stop.Stop();
stop.Reset();
System.Console.WriteLine("Optimised: {0}", stop.Elapsed);
//Problem 3
}
public static List<Ad> WithoutInclude(AdsEntities db)
{
return db.Ads.ToList();
}
static void Main()
{
var contex = new AdsEntities();
//Problem 1.Show Data from Related Tables
foreach (var ad in contex.Ads)
{
Console.WriteLine("Title: {0}, status: {1}, category: {2}, town: {3}, user: {4}",
ad.Title,
ad.AdStatus.Status,
ad.CategoryId != null ? ad.Category.Name : "None",
ad.TownId != null ? ad.Town.Name : "None",
ad.AspNetUser.Name);
}
foreach (var ad in contex.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.Status,
ad.CategoryId != null ? ad.Category.Name : "None",
ad.TownId != null ? ad.Town.Name : "None",
ad.AspNetUser.Name);
}
//Problem 2.Play with ToList()
Stopwatch sw = new Stopwatch();
sw.Start();
for (int i = 0; i < 10; i++)
{
var ads2 = contex.Ads
.ToList()
.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.Select(a => new
{
Title = a.Title,
Category = a.CategoryId != null ? a.Category.Name : "None",
Town = a.TownId != null ? a.Town.Name : "None"
})
.ToList();
Console.WriteLine(sw.Elapsed);
sw.Restart();
}
for (int i = 0; i < 10; i++)
{
var ads3 = contex.Ads
.Where(a => a.AdStatus.Status == "Published")
.OrderBy(a => a.Date)
.Select(a => new
{
Title = a.Title,
Category = a.CategoryId != null ? a.Category.Name : "None",
Town = a.TownId != null ? a.Town.Name : "None"
})
.ToList();
Console.WriteLine(sw.Elapsed);
sw.Restart();
}
//Problem 3.Select Everything vs. Select Certain Columns
for (int i = 0; i < 10; i++)
{
foreach (var ad in contex.Ads)
{
Console.WriteLine(ad.Title);
}
Console.WriteLine(sw.Elapsed);
sw.Restart();
}
var ads5 = contex.Ads.Select(a => a.Title);
for (int i = 0; i < 10; i++)
{
foreach (var ad in ads5)
{
Console.WriteLine(ad);
}
Console.WriteLine(sw.Elapsed);
sw.Restart();
}
}
