static void Main()
{
TelerikAcademyEntities context = new TelerikAcademyEntities();
// Low performance
// SQL statements are executed each time we invoke ToList();
var allEmployeesUsingToList = context.Employees.ToList()
.Select(e => e.Address).ToList()
.Select(e => e.Town).ToList()
.Where(t => t.Name == "Sofia");
// Better performance
// SQL statement is executed only when we call 'foreach'
var allEmployeesWithoutToList = context.Employees
.Select(e => e.Address)
.Select(e => e.Town)
.Where(t => t.Name == "Sofia");
using (context)
{
// 646 SQL statements are executed
foreach (var employee in allEmployeesUsingToList)
{
Console.WriteLine(employee.Name);
}
// 1 SQL statement is executed
foreach (var employee in allEmployeesWithoutToList)
{
Console.WriteLine(employee.Name);
}
}
}
static void Main()
{
TelerikAcademyEntities context = new TelerikAcademyEntities();
using (context)
{
// Low performance if not using .Include(...);
// 339 SQL statements are executed
foreach (var employee in context.Employees)
{
Console.WriteLine("Employee name: {0} {1}; ", employee.FirstName, employee.LastName);
Console.Write("Department: {0}; ", employee.Department.Name);
Console.Write("Town: {0}", employee.Address.Town.Name);
Console.WriteLine();
Console.WriteLine();
}
// Better performance when using .Include();
// Only 1 SQL statement is executed
foreach (var employee in context.Employees.Include("Department").Include("Address.Town"))
{
Console.WriteLine("Employee name: {0} {1}; ", employee.FirstName, employee.LastName);
Console.Write("Department: {0}; ", employee.Department.Name);
Console.Write("Town: {0}", employee.Address.Town.Name);
Console.WriteLine();
Console.WriteLine();
}
}
}
/* 2. Using Entity Framework write a query that selects all employees from
* the Telerik Academy database, then invokes ToList(), then selects their
* addresses, then invokes ToList(), then selects their towns, then invokes
* ToList() and finally checks whether the town is "Sofia". Rewrite the same
* in more optimized way and compare the performance.*/
static void Main(string[] args)
{
var dbCon = new TelerikAcademyEntities();
using (dbCon)
{
////Requests made: 645
var query = dbCon.Employees.ToList()
.Select(a => a.Address).ToList()
.Select(t => t.Town).ToList()
.Where(x => x.Name == "Sofia");
foreach (var e in query)
{
Console.WriteLine(e.Name);
}
//Requests made: 1
var newQuery = dbCon.Employees
.Select(a => a.Address)
.Select(t => t.Town)
.Where(x => x.Name == "Sofia");
foreach (var e in newQuery)
{
Console.WriteLine(e.Name);
}
}
}
public static void Main()
{
Stopwatch timer = new Stopwatch();
// Task 1
databaseConnection = new TelerikAcademyEntities();
using (databaseConnection)
{
timer.Restart();
PrintEmployeesClean();
Console.WriteLine("\nThe query for all employees WITHOUT include took: {0}.\nCheck screens in the solution folder for queries count!", timer.Elapsed);
timer.Restart();
PrintEmployeesIncluded();
Console.WriteLine("\nThe query for all employees WITH include took: {0}.\nCheck screens in the solution folder for queries count!", timer.Elapsed);
}
// Task 2
databaseConnection = new TelerikAcademyEntities();
using (databaseConnection)
{
timer.Restart();
GetEmployeesFromSofiaUnoptimized();
Console.WriteLine("\nTo list operations took: {0} with N queries.", timer.Elapsed);
timer.Restart();
GetEmployeesFromSofiaOptimizedOne();
Console.WriteLine("\nOptimized #1 query took: {0} with 1 querie.", timer.Elapsed);
timer.Restart();
GetEmployeesFromSofiaOptimizedTwo();
Console.WriteLine("\nOptimized #2 query took: {0} with 1 querie.", timer.Elapsed);
}
}
private static void GetSofiaMatches_ManyToList()
{
TelerikAcademyEntities context = new TelerikAcademyEntities();
var employees = context.Employees.ToList().
Select(ad => ad.Address).ToList().
Select(t => t.Town).ToList().
Where(t => t.Name == "Sofia");
Console.WriteLine("There are {0} matches", employees.Count());
}
private static void PrintEmployeesInfo_IncludeMethod()
{
TelerikAcademyEntities context = new TelerikAcademyEntities();
using (context)
{
foreach (var employee in context.Employees.Include("Department").Include("Address.Town"))
{
Console.WriteLine("{0} - works at - {1} - from - {2}", employee.FirstName + " " + employee.LastName,
employee.Department.Name, employee.Address.Town.Name);
}
}
}
private static void FastQuery(TelerikAcademyEntities context)
{
using (context)
{
List<Town> towns = context.Employees
.Select(x => x.Address)
.Select(x => x.Town)
.Where(x => x.Name == "Sofia")
.ToList();
foreach (var town in towns)
{
Console.WriteLine("Name: {0}", town.Name);
}
}
}
static void Main()
{
TelerikAcademyEntities context = new TelerikAcademyEntities();
using (context)
{
var employees = context.Employees.Select(e => e).ToList();
var addresses = employees.Select(e =>
new
{
Name = e.FirstName + " " + e.LastName,
Addtess = e.Address
}).ToList();
var towns = addresses.Select(a =>
new
{
Name = a.Name,
Town = a.Addtess.Town.Name
}).ToList();
var sofia = towns.Select(t => t).Where(t => t.Town == "Sofia").ToList();
foreach (var item in sofia)
{
Console.WriteLine(item.Name);
}
var selectSofia = context.Employees.Join(context.Addresses,
(e => e.AddressID), (a => a.AddressID), (e, a) =>
new
{
Name = e.FirstName + " " + e.LastName,
TownID = a.TownID
}).Join(context.Towns,
(x => x.TownID), (t => t.TownID), (x, t) =>
new
{
Name = x.Name,
Town = t.Name
}).Select(x => x).Where(x => x.Town == "Sofia");
foreach (var item in selectSofia)
{
Console.WriteLine(item.Name);
}
}
}
/* 1. Using Entity Framework write a SQL query to select all employees
* from the Telerik Academy database and later print their name, department and town.
* Try the both variants: with and without .Include(…). Compare the number of executed SQL
* statements and the performance. */
static void Main(string[] args)
{
var dbCOn = new TelerikAcademyEntities();
using (dbCOn)
{
//Proffiler gave 339 requests
foreach (var s in dbCOn.Employees)
{
Console.WriteLine("Name: {0}, Department {1}, Town {2}", s.FirstName, s.Department.Name, s.Address.Town.Name);
}
//Proffiler gave 1 requests
foreach (var e in dbCOn.Employees.Include("Department").Include("Address.Town"))
{
Console.WriteLine("Name: {0}, Department {1}, Town {2}", e.FirstName, e.Department.Name, e.Address.Town.Name);
}
}
}
static void Main()
{
TelerikAcademyEntities context = new TelerikAcademyEntities();
using (context)
{
var employees = context.Employees.Select(e => e);
foreach (var employee in employees)
{
Console.WriteLine("Name: {0} - Deparment: {1}, Town: {2}",
employee.FirstName + " " + employee.LastName,
employee.Department.Name, employee.Address.TownID);
}
foreach (var employee in
context.Employees.Include("Department").Include("Address").Include("Address.Town"))
{
Console.WriteLine("Name: {0} - Deparment: {1}, Town: {2}",
employee.FirstName + " " + employee.LastName,
employee.Department.Name, employee.Address.Town.Name);
}
}
}
static void Main(string[] args)
{
TelerikAcademyEntities context = new TelerikAcademyEntities();
SlowQuery(context);
//FastQuery(context);
}