// 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 : "******");
}
}