internal static void Main()
{
var stopwatch = new Stopwatch();
var context = new TelerikAcademyEntities();
stopwatch.Start();
var employees = context.Employees;
foreach (var employee in employees)
{
Console.WriteLine(
"{0}, {1}, {2}",
(employee.FirstName + ' ' + employee.LastName).PadLeft(30),
employee.Department.Name.PadLeft(30),
employee.Address.Town.Name.PadLeft(15));
}
stopwatch.Stop();
Console.WriteLine("Time elapsed: {0} seconds", (decimal)stopwatch.Elapsed.Milliseconds / 1000);
stopwatch.Reset();
stopwatch.Start();
var includeEmployees = context.Employees.Include("Address").Include("Department");
foreach (var employee in includeEmployees)
{
Console.WriteLine(
"{0}, {1}, {2}",
(employee.FirstName + ' ' + employee.LastName).PadLeft(30),
employee.Department.Name.PadLeft(30),
employee.Address.Town.Name.PadLeft(15));
}
stopwatch.Stop();
Console.WriteLine("Time elapsed: {0} seconds", (decimal)stopwatch.Elapsed.Milliseconds / 1000);
}
static void Main(string[] args)
{
TelerikAcademyEntities db = new TelerikAcademyEntities();
Stopwatch sw = new Stopwatch();
sw.Start();
var query = db.Employees.ToList()
.Select(employee => employee.Address).ToList()
.Select(employee => employee.Town).ToList()
.Where(employee => employee.Name == "Sofia").ToList();
sw.Stop();
var slow = sw.Elapsed;
Console.WriteLine("{0} employees found", query.Count);
sw.Restart();
var querySmart = db.Employees
.Select(employee => employee.Address)
.Select(employee => employee.Town)
.Where(employee => employee.Name == "Sofia").ToList();
sw.Stop();
Console.WriteLine("{0} employees found", querySmart.Count);
Console.WriteLine("Slow: {0}\nFast: {1}", slow, sw.Elapsed);
//1298 vs 13 queries
}
private static void QueryAllEmployeesWithOptimization(TelerikAcademyEntities dbContext)
{
var allEmployees = dbContext.Employees
.Select(e => e.Address)
.Select(e => e.Town)
.Where(e => e.Name == "Sofia").ToList();
}
public static void Main()
{
var dbContext = new TelerikAcademyEntities();
SelectWithToList(dbContext);
SelectWithoutToList(dbContext);
}
static void Main(string[] args)
{
TelerikAcademyEntities db = new TelerikAcademyEntities();
//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();
Console.WriteLine("Controll test (no Include)");
sw.Start();
foreach (var employee in db.Employees)
{
Console.WriteLine("Name: {0} {1}\nDepartment: {2}\nTown: {3}\n\n", employee.FirstName, employee.LastName, employee.Department.Name, employee.Address.Town.Name);
}
sw.Stop();
var noIncludeTime = sw.Elapsed;
Console.WriteLine("\nInclude test");
sw.Restart();
foreach (var employee in db.Employees.Include("Address").Include("Department"))
{
Console.WriteLine("Name: {0} {1}\nDepartment:{2}\nTown: {3}\n\n", employee.FirstName, employee.LastName, employee.Department.Name, employee.Address.Town.Name);
}
sw.Stop();
Console.WriteLine("Without Include:\t{0}\nWith Include:\t\t{1}", noIncludeTime, sw.Elapsed);
//355 vs 6 queries
}
public static void Main()
{
using (var dbContext = new TelerikAcademyEntities())
{
// QueryAllEmployeesWithoutOptimization(dbContext);
QueryAllEmployeesWithOptimization(dbContext);
}
}
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 Optimized(TelerikAcademyEntities db)
{
var towns = db.Employees
.Select(emp => emp.Address)
.Select(town => town.Town)
.Where(town => town.Name == "Sofia");
foreach (var town in towns)
{
Console.WriteLine(town.Name);
}
}
private static void ListEmployeesWithoutOptimization(TelerikAcademyEntities dbContext)
{
foreach (var employee in dbContext.Employees)
{
Console.WriteLine(
"Employee: {0} {1}; Department {2}; Town: {3}",
employee.FirstName,
employee.LastName,
employee.Department.Name,
employee.Address.Town.Name);
}
}
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);
}
}
static void Main()
{
Stopwatch sw = new Stopwatch();
string firstResult, secondResult;
string optimizedResult, notOptimizedResult;
using (var database = new TelerikAcademyEntities())
{
sw.Start();
PrintWithoutInclude(database);
sw.Stop();
firstResult = sw.Elapsed.ToString();
sw.Restart();
Console.WriteLine("========================================");
PrintWithInclude(database);
sw.Stop();
secondResult = sw.Elapsed.ToString();
Console.WriteLine("Time without include: {0}", firstResult);
Console.WriteLine("Time with include: {0}", secondResult);
////////////////// Task 2 //////////////////
sw.Restart();
Console.WriteLine("========================================");
Optimized(database);
sw.Stop();
optimizedResult = sw.Elapsed.ToString();
sw.Restart();
Console.WriteLine("========================================");
NotOptimized(database);
sw.Stop();
notOptimizedResult = sw.Elapsed.ToString();
Console.WriteLine("Time without optimized: {0}", notOptimizedResult);
Console.WriteLine("Time with optimized: {0}", optimizedResult);
}
}
internal static void Main()
{
var stopwatch = new Stopwatch();
var context = new TelerikAcademyEntities();
stopwatch.Start();
var employees = context.Employees.ToList().Select(e => e.Address).ToList().Select(a => a.Town).Where(t => t.Name == "Sofia");
Console.WriteLine("Count: {0}", employees.Count());
stopwatch.Stop();
Console.WriteLine("Time elapsed: {0} seconds", (decimal)stopwatch.Elapsed.Milliseconds / 1000);
stopwatch.Reset();
stopwatch.Start();
var optimizedEmployees = context.Employees.Select(e => e.Address).Select(a => a.Town).Where(t => t.Name == "Sofia");
Console.WriteLine("Count: {0}", optimizedEmployees.Count());
stopwatch.Stop();
Console.WriteLine("Time elapsed: {0} seconds", (decimal)stopwatch.Elapsed.Milliseconds / 1000);
}
static void Main(string[] args)
{
//Too much queries
//using (var db = new TelerikAcademyEntities())
//{
// var sw = new Stopwatch();
// sw.Start();
// var employees = db.Employees;
// foreach (var employee in employees)
// {
// Console.WriteLine(employee.FirstName);
// Console.WriteLine(employee.Departments1.Name);
// Console.WriteLine(employee.Addresses.Towns.Name);
// Console.WriteLine("-------");
// }
// Console.WriteLine("Time: " + sw.Elapsed);
//}
//Faster way
//using (var db = new TelerikAcademyEntities())
//{
// var sw = new Stopwatch();
// sw.Start();
// var employees = db.Employees.Include("Addresses.Towns").Include("Departments1");
// foreach (var employee in employees)
// {
// Console.WriteLine(employee.FirstName);
// Console.WriteLine(employee.Departments1.Name);
// Console.WriteLine(employee.Addresses.Towns.Name);
// Console.WriteLine("-------");
// }
// Console.WriteLine(sw.Elapsed);
//}
//Another fast way
//using (var db = new TelerikAcademyEntities())
//{
// var sw = new Stopwatch();
// sw.Start();
// var employees = db.Employees.Select(e => new
// {
// FirstName = e.FirstName,
// DepartmentName = e.Departments1.Name,
// Town = e.Addresses.Towns.Name
// });
// foreach (var item in employees)
// {
// Console.WriteLine(item.FirstName);
// Console.WriteLine(item.DepartmentName);
// Console.WriteLine(item.Town);
// Console.WriteLine("-------");
// }
// Console.WriteLine(sw.Elapsed);
//}
//TASK2
using (var db = new TelerikAcademyEntities())
{
var result = db.Employees.ToList()
.Select(e => e.Addresses).ToList()
.Select(e => e.Towns).ToList()
.Select(e => e.Name == "Sofia")
.ToList();
}
//using (var db = new TelerikAcademyEntities())
//{
// var result = db.Employees
// .Select(e => e.Addresses)
// .Select(e => e.Towns)
// .Select(e => e.Name == "Sofia")
// .ToList();
//}
}
private static void PrintWithInclude(TelerikAcademyEntities db)
{
foreach (var emp in db.Employees.Include("Department").Include("Address.Town").Take(20))
{
Console.WriteLine("DepartmentID: {0}, {1} {2}\tFrom {3}",
emp.Department.DepartmentID,
emp.FirstName,
emp.LastName,
emp.Address.Town.Name);
}
}
