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); }