private static void NPlusOneQueryProblem()
{
//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.
using (TelerikAcademyEntities dbContext = new TelerikAcademyEntities())
{
foreach (Employee employee in dbContext.Employees)
{
Console.WriteLine("First name = {0}, Last name = {1}, Department = {2}, Town = {3}.",
employee.FirstName, employee.LastName, employee.Department.Name, employee.Address.Town.Name);
}
Console.ReadLine();
Console.Clear();
foreach (Employee employee in dbContext.Employees
.Include("Department")
.Include("Address.Town"))
{
Console.WriteLine("First name = {0}, Last name = {1}, Department = {2}, Town = {3}.",
employee.FirstName, employee.LastName, employee.Department.Name, employee.Address.Town.Name);
}
}
}
/// <summary>
/// Demonstrates optimized query execution.
/// </summary>
private static void SelectWithOptimizedToList()
{
using (var telerikAcademyDBContext = new TelerikAcademyEntities())
{
var result = telerikAcademyDBContext.Employees.Select(e => e.Address).Select(a => a.Town).Where(t => t.Name == "Sofia").ToList();
}
Console.WriteLine("Done!");
}
/// <summary>
/// Prints employees without using include.
/// </summary>
private static void PrintEmployeesWithoutInclude()
{
using (var telerikAcademyDBContext = new TelerikAcademyEntities())
{
var employees = telerikAcademyDBContext.Employees;
foreach (var employee in employees)
{
Console.WriteLine("Name: {0}, Department: {1}, Town: {2}", employee.FirstName + ' ' + employee.LastName, employee.Department.Name, employee.Address.Town.Name);
}
}
}
public static void QueryWithInclude()
{
using (var db = new TelerikAcademyEntities())
{
foreach (var employee in db.Employees.Include("Department").Include("Address.Town"))
{
Console.WriteLine("Employee name: {0} Departmant: {1} Town: {2}",
(employee.FirstName + " " + employee.LastName).PadRight(25),
employee.Department.Name.PadRight(20),
employee.Address.Town.Name);
}
}
}
public static void FastToListOperation()
{
using (var context = new TelerikAcademyEntities())
{
var sw = Stopwatch.StartNew();
var sofiaEmployees = context.Employees.Select(e => e).Select(e => e.Address).Select(a => a.Town)
.Where(t => t.Name.ToLower() == "sofia");
var count = sofiaEmployees.Count();
sw.Stop();
Console.WriteLine("Slow: {0} milliseconds.", sw.Elapsed.TotalMilliseconds);
}
}
public static void FastEmployeeQuery()
{
using (var context = new TelerikAcademyEntities())
{
var sw = Stopwatch.StartNew();
foreach (var employee in context.Employees.Include("Department").Include("Address.Town"))
{
var Name = employee.LastName;
var Department = employee.Department.Name;
var Town = employee.Address.Town.Name;
}
sw.Stop();
Console.WriteLine("Fast: {0} milliseconds.", sw.Elapsed.TotalMilliseconds);
}
}
static void Main()
{
using (var db = new TelerikAcademyEntities())
{
var sw = new Stopwatch();
Console.WriteLine("Slow option");
sw.Start();
var employeesSlowSelect = (from e in db.Employees
select e).ToList()
.Select(e => e.Address).ToList()
.Select(a => a.Town)
.Where(t => t.Name == "Sofia");
sw.Stop();
Console.WriteLine("Needed milliseconds: {0}", sw.Elapsed);
foreach (var employee in employeesSlowSelect)
{
Console.WriteLine(employee.Name);
}
sw.Reset();
sw.Start();
Console.WriteLine("\nFaster option");
var employeesFastSelect = from e in db.Employees
join a in db.Addresses
on e.AddressID equals a.AddressID
join t in db.Towns
on a.TownID equals t.TownID
where t.Name == "Sofia"
select new { Name = e.FirstName, Town = t.Name };
sw.Stop();
Console.WriteLine("Needed milliseconds: {0}", sw.Elapsed);
foreach (var employee in employeesFastSelect)
{
Console.WriteLine("{0} {1}", employee.Name, employee.Town);
}
}
}
private static void ToListProblem()
{
//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.
using (TelerikAcademyEntities dbContext = new TelerikAcademyEntities())
{
var townsSlow = dbContext.Employees
.ToList()
.Select(x => x.Address)
.ToList()
.Select(x => x.Town)
.ToList()
.Where(x => x.Name == "Sofia");
foreach (var town in townsSlow)
{
Console.WriteLine(town.Name);
}
Console.ReadLine();
Console.Clear();
var townsFast = dbContext.Employees
.Select(x => x.Address)
.Select(x => x.Town)
.Where(x => x.Name == "Sofia");
foreach (var town in townsFast)
{
Console.WriteLine(town.Name);
}
}
}
