//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.
public static void FastCorrect()
{
TelerikAcademyEntities dbTelerikAcademy = new TelerikAcademyEntities();
Stopwatch omega = new Stopwatch();
var queryEmployee = dbTelerikAcademy.Employees.Select(x =>
new
{
Name = x.FirstName,
LastName = x.LastName,
Town = x.Address.Town.Name
}).ToList();
using (dbTelerikAcademy)
{
omega.Start();
foreach (var employeeName in queryEmployee)
{
if (employeeName.Town == "Sofia")
{
Console.WriteLine("{0} {1} --> {2}", employeeName.Name, employeeName.LastName, employeeName.Town);
}
}
omega.Stop();
Console.WriteLine(omega.Elapsed);
}
}
private static void Main()
{
var telerikAcademy = new TelerikAcademyEntities();
var stopwatch = new Stopwatch();
var stringBuilder = new StringBuilder();
stopwatch.Start();
var towns =
telerikAcademy.Employees.ToList()
.Select(emp => emp.Address)
.ToList()
.Select(town => town.Town)
.ToList()
.Where(town => town.Name == "Sofia");
stopwatch.Stop();
var firstResult = stopwatch.Elapsed.ToString();
UseTowns(towns, stringBuilder);
stopwatch.Restart();
towns = telerikAcademy.Employees.Select(emp => emp.Address)
.Select(town => town.Town)
.Where(town => town.Name == "Sofia");
UseTowns(towns, stringBuilder);
stopwatch.Stop();
var secondResult = stopwatch.Elapsed.ToString();
Console.WriteLine("Time with ToList: {0}", firstResult);
Console.WriteLine("Time without ToList: {0}", secondResult);
}
static void Main(string[] args)
{
TelerikAcademyEntities context = new TelerikAcademyEntities();
SlowQuery(context);
//FastQuery(context);
}
static void Main()
{
using (var db = new TelerikAcademyEntities())
{
long time1 = 0;
long time2 = 0;
var sw = new Stopwatch();
sw.Start();
foreach (var emp in db.Employees)
{
Console.WriteLine(emp.FirstName + " " + emp.LastName + " works in " + emp.Department.Name + " and lives in " + emp.Address.Town.Name);
}
time1 = sw.ElapsedMilliseconds;
sw.Restart();
foreach (var emp in db.Employees.Include("Department").Include("Address"))
{
Console.WriteLine(emp.FirstName + " " + emp.LastName + " works in " + emp.Department.Name + " and lives in " + emp.Address.Town.Name);
}
time2 = sw.ElapsedMilliseconds;
Console.WriteLine();
Console.WriteLine("Outcome");
Console.WriteLine("First way made 342 queries and took {0} miliseconds", time1);
Console.WriteLine("Second way made only 1 queries and took {0} miliseconds", time2);
}
}
static void Main()
{
var dbContext = new TelerikAcademyEntities();
var sw = new Stopwatch();
sw.Start();
var allEmployees = dbContext.Employees.ToList()
.Select(e => e.Address).ToList()
.Select(e => e.Town).ToList()
.Where(e => e.Name == "Sofia");
sw.Stop();
var withTolist = sw.Elapsed;
sw.Reset();
sw.Start();
var allEmployeesOptimized = dbContext.Employees
.Select(e => e.Address)
.Select(e => e.Town)
.Where(e => e.Name == "Sofia").ToList();
sw.Stop();
var withoutToList = sw.Elapsed;
Console.WriteLine("Elapsed time with ToList: " + withTolist);
Console.WriteLine("Elapsed time without ToList: " + withoutToList);
}
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);
}
}
}
public static void Main()
{
var telerikAcademy = new TelerikAcademyEntities();
telerikAcademy.Departments.Count();
var stringBuilder = new StringBuilder();
var stopwatch = new Stopwatch();
stopwatch.Start();
var withoutInclude = telerikAcademy.Employees;
UseTheData(stringBuilder, withoutInclude);
stopwatch.Stop();
var firstResult = stopwatch.Elapsed.ToString();
stopwatch.Restart();
var withInclude = telerikAcademy.Employees.Include("Department").Include("Address.Town");
var resultWithInclude = UseTheData(stringBuilder, withInclude);
stopwatch.Stop();
var secondResult = stopwatch.Elapsed.ToString();
Console.WriteLine(resultWithInclude);
Console.WriteLine("Time without include: {0}", firstResult);
Console.WriteLine("Time with include: {0}", secondResult);
}
static void Main()
{
TelerikAcademyEntities dbContext = new TelerikAcademyEntities();
using (dbContext)
{
Stopwatch sw = new Stopwatch();
sw.Start();
//GetInfoWithInclude(dbContext);
sw.Stop();
var withInclude = sw.Elapsed;
sw.Reset();
sw.Start();
GetInfoWithoutInclude(dbContext);
sw.Stop();
var withoutInclude = sw.Elapsed;
Console.WriteLine("Elapsed time with include: " + withInclude);
Console.WriteLine("Elapsed time without include: " + withoutInclude);
//using the SQL Server Profiler for SQL Management Studio 2014:
//with include - 1
//without include - there are tens of queries in the profiler log
}
}
public static void Main()
{
var dbContext = new TelerikAcademyEntities();
SelectEmployessWithoutInclude(dbContext);
SelectEmployessWithInclude(dbContext);
}
private static void SelectEmployeesUsingToList()
{
using (var context = new TelerikAcademyEntities())
{
Console.WriteLine("Employees:");
var employees = context.Employees.ToList()
.Select(e => new
{
FirstName = e.FirstName,
LastName = e.LastName,
Address = e.Address.AddressText,
TownName = e.Address.Town.Name
})
.ToList()
.Where(e => e.TownName == "Sofia")
.ToList();
foreach (var employee in employees)
{
Console.WriteLine(
"Name: {0} {1}\nAddress: {2}\nTown: {3}",
employee.FirstName,
employee.LastName,
employee.Address,
employee.TownName);
}
}
}
static void Main(string[] args)
{
var context = new TelerikAcademyEntities();
CreateDatabaseIfNotExists(context);
var deps = context.Departments;
var employees = context.Employees;
//var persToAdd = new Employee
//{
// FirstName = "marto",
// LastName = "peshds2z",
// JobTitle = "asdasd",
// HireDate = DateTime.Now,
// Salary = 5000,
// ManagerID = 314
//};
//employees.Add(persToAdd);
//var persTest = employees.ToList().Find(x => x.EmployeeID == 313);
//employees.Remove(persTest);
//var depToRemove = deps.ToList().Find(x => x.Employee == 313);
//deps.Remove(depToRemove);
context.SaveChanges();
}
private static void SelectEmployeesUsingOptimization()
{
using (var context = new TelerikAcademyEntities())
{
Console.WriteLine("Employees:");
var employees =
from employee in context.Employees
join address in context.Addresses
on employee.AddressID equals address.AddressID
join town in context.Towns
on address.TownID equals town.TownID
where town.Name == "Sofia"
select employee;
foreach (var employee in employees)
{
Console.WriteLine(
"Name: {0} {1}\nAddress: {2}\nTown: {3}",
employee.FirstName,
employee.LastName,
employee.Address.AddressText,
employee.Address.Town.Name);
}
}
}
private static IEnumerable <Town> GetTownsWithToList(TelerikAcademyEntities db)
{
var towns = db.Employees.ToList().Select(e => e.Address)
.ToList().Select(e => e.Town).ToList().Where(t => t.Name == "Sofia");
return(towns);
}
public static void Main()
{
var dbContext = new TelerikAcademyEntities();
SelectWithToList(dbContext);
SelectWithoutToList(dbContext);
}
public static void Main()
{
var dbContext = new TelerikAcademyEntities();
SelectEmployessWithoutInclude(dbContext);
SelectEmployessWithInclude(dbContext);
}
private static void Main(string[] args)
{
using (var context = new TelerikAcademyEntities())
{
NonOptimizedEmployeeQuery(context);
OptimizedEmployeeQuery(context);
}
}
private static IQueryable <Employee> GetEmployeesWithInclude(TelerikAcademyEntities db)
{
var employees = db.Employees
.Include("Address.Town")
.Include("Department");
return(employees);
}
private static IQueryable <Employee> GetEmployeesWithoutInclude(TelerikAcademyEntities db)
{
var employees =
from empl in db.Employees
select empl;
return(employees);
}
private static IEnumerable <Town> GetTownsMultipleToList(TelerikAcademyEntities db)
{
var employees = db.Employees
.Select(e => e.Address)
.Select(e => e.Town)
.Where(t => t.Name == "Sofia").ToList();
return(employees);
}
static void GetInfoWithInclude(TelerikAcademyEntities dbContext)
{
foreach (var employee in dbContext.Employees.Include("Address.Town").Include("Department"))
{
Console.WriteLine("{0} {1}", employee.FirstName, employee.LastName);
Console.WriteLine("{0}", employee.Department.Name);
Console.WriteLine("{0}", employee.Address.Town.Name);
Console.WriteLine();
}
}
private static void SelectEmployeedWithoutUsingIncludeStatement()
{
using (var dbContext = new TelerikAcademyEntities())
{
foreach (var employee in dbContext.Employees)
{
Console.WriteLine("{{ Name: {0}, Department: {1}, Town: {2} }}",
employee.FirstName, employee.Department.Name, employee.Address.Town.Name);
}
}
}
private static void PrintWithoutInclude(TelerikAcademyEntities db)
{
foreach (var emp in db.Employees)
{
Console.WriteLine("DepartmentID: {0}, {1} {2}\tFrom {3}",
emp.Department.DepartmentID,
emp.FirstName,
emp.LastName,
emp.Address.Town.Name);
}
}
private static IEnumerable <Town> GetTownsToListAtEnd(TelerikAcademyEntities db)
{
List <Employee> results = new List <Employee>();
var employees = db.Employees.ToList()
.Select(e => e.Address).ToList()
.Select(e => e.Town).ToList()
.Where(e => e.Name == "Sofia").ToList();
return(employees);
}
private static void Main()
{
using (var context = new TelerikAcademyEntities())
{
StringBuilder container = new StringBuilder();
AnonymusVariable(context, container);
SeparateCalls(context, container);
IncludeUsing(context, container);
}
}
public static void PrintEmployeInfoFast()
{
using (TelerikAcademyEntities dbContext = new TelerikAcademyEntities())
{
foreach (var employe in dbContext.Employees.Include("Department").Include("Address").Include("Address.Town"))
{
Console.WriteLine("Employe name: {0}", employe.FirstName);
Console.WriteLine("Employe department name: {0}", employe.Department.Name);
Console.WriteLine("Employe town name: {0}", employe.Address.Town.Name);
}
}
}
private static void OptimizedEmployeeQuery(TelerikAcademyEntities context)
{
Console.WriteLine("Employees:");
var employeesInSofia = context.Employees
.Where(e => e.Address.Town.Name == "Sofia")
.Select(e => e.FirstName + " " + e.MiddleName + " " + e.LastName);
foreach (var employee in employeesInSofia)
{
Console.WriteLine("{0}", string.Join(", ", employee));
}
}
public static void PrintEmployeesFast()
{
TelerikAcademyEntities telerikAcademyDb = new TelerikAcademyEntities();
using (telerikAcademyDb)
{
var employees = telerikAcademyDb.Employees.Include("Department").Include("Address").Include("Address.Town");
foreach (var employee in employees)
{
Console.WriteLine("Employee: {0}, {1}, {2}", employee.FirstName, employee.Department, employee.Address.Town);
}
}
}
static void Main(string[] args)
{
var context = new TelerikAcademyEntities();
//Makes 342 queries
GetEmployeesWithoutInclude(context);
//Makes 36 queries
GetEmployeesWithInclude(context);
//Makes 1 query
GetEmployeesWithExtraInclude(context);
}
private static void NonOptimizedEmployeeQuery(TelerikAcademyEntities context)
{
var employees = context.Employees.ToList();
var addresses = employees.Select(e => e.Address).ToList();
var towns = addresses.Select(a => a.Town).ToList();
var sofiaTownId = towns.Where(t => t.Name.ToLowerInvariant() == "Sofia".ToLowerInvariant()).First().TownID;
Console.WriteLine("Employees:");
foreach (var employee in employees.Where(e => e.Address.TownID == sofiaTownId).Select(e => e.FirstName + " " + e.MiddleName + " " + e.LastName))
{
Console.WriteLine("{0}", string.Join(", ", employee));
}
}
private static void UsingToListOnceTest()
{
using (var dbContext = new TelerikAcademyEntities())
{
var employees = dbContext.Employees
.Select(e => e.Address)
.Select(a => a.Town)
.Where(t => t.Name == "Sofia")
.ToList();
Console.WriteLine("Records: " + employees.Count);
}
}
private static void SelectWithoutToList(TelerikAcademyEntities dbContext)
{
using (dbContext)
{
var employessInSofia = dbContext.Employees
.Select(e => e.Address)
.Select(a => a.Town)
.Where(a => a.Name == "Sofia")
.ToList();
Console.WriteLine("Employees in Sofia count: " + employessInSofia.Count);
}
}
private static void SelectEmployessWithInclude(TelerikAcademyEntities dbContext)
{
using (dbContext)
{
foreach (var employee in dbContext.Employees.Include("Department").Include("Address.Town"))
{
var firstName = employee.FirstName;
var departmentName = employee.Department.Name;
var townName = employee.Address.Town.Name;
Console.WriteLine("{{ Name: {0}, Department: {1}, Town: {2} }}",
firstName, departmentName, townName);
}
}
}
static void Main(string[] args)
{
TelerikAcademyEntities db = new TelerikAcademyEntities();
//------------------------------------Task 1-----------------------------------------
//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.
Stopwatch sw = new Stopwatch();
sw.Start();
foreach (var e in db.Employees)
{
Console.WriteLine("Product: {0}, {1}, {2};", e.FirstName, e.Department, e.Address.Town);
}
sw.Stop();
Console.WriteLine(sw.Elapsed);
sw.Reset();
Console.WriteLine();
Console.WriteLine("-------------------------------------------------------");
Console.WriteLine();
sw.Start();
foreach (var e in db.Employees.Include("Address"))
{
Console.WriteLine("Product: {0}, {1}, {2};", e.FirstName, e.Department, e.Address.Town);
}
sw.Stop();
Console.WriteLine(sw.Elapsed);
//conclusion Include makes the query about 4 times faster. 52 queries when Include used, and 372 when not
//---------------------------------------Task 2 ------------------------------------
//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.
IEnumerable query = db.Employees.ToList()
.Select(x => x.Address).ToList()
.Select(t => t.Town).ToList()
.Where(t => t.Name == "Sofia");
foreach (var t in query)
{
Console.WriteLine(t);
} // made 1292 queries
IEnumerable querySmart = db.Employees
.Select(x => x.Address)
.Select(t => t.Town)
.Where(t => t.Name == "Sofia").ToList();
foreach (var t in querySmart)
{
Console.WriteLine(t);
} // made 2 queries
}
