public static void RemoveTown(SoftuniContext context) { string townName = Console.ReadLine(); var town = context.Towns.First(x => x.Name == townName); var addressesToRemove = town.Addresses.ToList(); foreach (var address in addressesToRemove) { var employeesAddresses = address.Employees.ToList(); foreach (var employee in employeesAddresses) { employee.AddressID = null; } } context.Addresses.RemoveRange(addressesToRemove); context.Towns.Remove(town); context.SaveChanges(); if (addressesToRemove.Count == 1) { Console.WriteLine($"{addressesToRemove.Count} address in {townName} was deleted"); } else { Console.WriteLine($"{addressesToRemove.Count} addresses in {townName} were deleted"); } }
public static void AddingNewAddressAndUpdateEmployee(SoftuniContext context) { string newAddressText = "Vitoshka 15"; int newAddressTownId = 4; var address = new Address() { AddressText = newAddressText, TownID = newAddressTownId }; Employee employee = context.Employees.FirstOrDefault(x => x.LastName == "Nakov"); if (employee != null) { employee.Address = address; } context.SaveChanges(); var employees = context.Employees.OrderByDescending(x => x.AddressID) .Take(10); foreach (var emp in employees) { Console.WriteLine($"{emp.Address.AddressText}"); } }
public static void FindEmployeesByFirstName(SoftuniContext context) { string pattern = "Sa"; var employees = context.Employees.Where(x => x.FirstName.StartsWith(pattern)).ToList(); foreach (var employee in employees) { Console.WriteLine($"{employee.FirstName} {employee.LastName} - {employee.JobTitle} - (${employee.Salary})"); } }
private static void PrintNamesWithLinq(SoftuniContext context) { var employeesNames = context.Employees.Where(x => x.Projects.Count(p => p.StartDate.Year == 2002) != 0) .Select(x => x.FirstName) .GroupBy(s => s); foreach (var name in employeesNames) { } }
public static void FindingLatestTenProjects(SoftuniContext context) { var projects = context.Projects.OrderByDescending(x => x.StartDate) .Take(10); foreach (var project in projects.OrderBy(x => x.Name)) { string dateTimeFormat = "M/d/yyyy h:mm:ss tt"; Console.WriteLine($"{project.Name} {project.Description} {project.StartDate.ToString(dateTimeFormat, CultureInfo.InvariantCulture)} {project.EndDate}"); } }
public static void GetAddressesByTownName(SoftuniContext context) { var addresses = context.Addresses.OrderByDescending(x => x.Employees.Count) .ThenBy(x => x.Town.Name) .Take(10); foreach (var address in addresses) { Console.WriteLine($"{address.AddressText}, {address.Town.Name} - {address.Employees.Count} employees"); } }
public static void GetEmployeesWithSalariesOver5000(SoftuniContext context) { var employeesNames = context.Employees .Where(x => x.Salary > 50000) .Select(x => x.FirstName); foreach (var employeeName in employeesNames) { Console.WriteLine(employeeName); } }
public static void GetEmployeesFullInformation(SoftuniContext context) { var employees = context.Employees .Select(x => new { x.EmployeeID, x.FirstName, x.MiddleName, x.LastName, x.JobTitle, x.Salary }) .OrderBy(x => x.EmployeeID); foreach (var employee in employees) { Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.MiddleName} {employee.JobTitle} {employee.Salary}"); } }
public static void GetEmployeesFromSeattle(SoftuniContext context) { var employees = context.Employees .Where(x => x.Department.Name == "Research and Development") .Select(x => new { x.FirstName, x.LastName, x.Department, x.Salary }) .OrderBy(x => x.Salary) .ThenByDescending(x => x.FirstName); foreach (var employee in employees) { Console.WriteLine($"{employee.FirstName} {employee.LastName} from Research and Development - ${employee.Salary:F2}"); } }
public static void GetEmployeeWithId147(SoftuniContext context) { var employee = context.Employees.First(x => x.EmployeeID == 147); //var employee = context.Employees.Find(147); Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.JobTitle}"); foreach (var project in employee.Projects.OrderBy(x => x.Name)) { Console.WriteLine(project.Name); } }
public static void GetDepartmentsWithMoreThanFiveEmployees(SoftuniContext context) { var departments = context.Departments.Where(x => x.Employees.Count > 5) .OrderBy(x => x.Employees.Count); foreach (var department in departments) { Console.WriteLine($"{department.Name} {department.Manager.FirstName}"); foreach (var employee in department.Employees) { Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.JobTitle}"); } } }
private static void PrintNamesWithNativeQuery(SoftuniContext context) { string query = "SELECT e.FirstName FROM Employees AS e " + "JOIN EmployeesProjects AS ep " + "ON e.EmployeeId = ep.EmployeeId " + "JOIN Projects AS p " + "ON ep.ProjectId = p.ProjectId " + "AND YEAR(p.StartDate) = 2002 " + "GROUP BY e.FirstName"; var result = context.Database.SqlQuery <string>(query); foreach (var res in result) { } }
public static void UseNativeSQLQuerry(SoftuniContext context) { var timer = new Stopwatch(); timer.Start(); PrintNamesWithLinq(context); timer.Stop(); Console.WriteLine($"Linq: {timer.Elapsed}"); timer.Reset(); timer.Start(); PrintNamesWithNativeQuery(context); timer.Stop(); Console.WriteLine($"Native: {timer.Elapsed}"); timer.Reset(); }
public static void FindingEmployeesInPeriod(SoftuniContext context) { var employees = context.Employees.Where(x => x.Projects.Count(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003) > 0) .Take(30); foreach (var employee in employees) { Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.Manager.FirstName}"); foreach (var project in employee.Projects) { string dateTimeFormat = "M/d/yyyy h:mm:ss tt"; Console.WriteLine($"--{project.Name} {project.StartDate.ToString(dateTimeFormat, CultureInfo.InvariantCulture)} {project.EndDate:M/d/yyyy h:mm:ss tt}"); //the result on the console depends on the regional options in the control panel, because //the project.EndDate can be Null and doesn't support .ToString(); //for correct result in Judge the regional options for "Date formats - Short date" should be "M/d/yyyy" } } }
public static void IncreaseSalariesMethod(SoftuniContext context) { var employees = context.Employees.Where(x => x.Department.Name == "Engineering" || x.Department.Name == "Tool Design" || x.Department.Name == "Marketing" || x.Department.Name == "Information Services"); foreach (var employee in employees) { employee.Salary *= (decimal)1.12; } foreach (var employee in employees) { Console.WriteLine($"{employee.FirstName} {employee.LastName} (${employee.Salary})"); } context.SaveChanges(); }
public static void DeleteProjectWithId(SoftuniContext context) { var project = context.Projects.Find(2); var employees = project.Employees; foreach (var employee in employees) { employee.Projects.Remove(project); } context.Projects.Remove(project); context.SaveChanges(); var projects = context.Projects.Select(x => x.Name).Take(10); foreach (var proj in projects) { Console.WriteLine(proj); } }