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