public static string GetDepartmentsWithMoreThan5Employees(SoftUniContext context) { StringBuilder sb = new StringBuilder(); var departments = context.Departments.Where(e => e.Employees.Count() > 5) .OrderBy(d => d.Employees.Count()) .ThenBy(d => d.Name) .Select(d => new { d.Name, d.Manager.FirstName, d.Manager.LastName, Employees = d.Employees.Select(e => new { e.FirstName, e.LastName, e.JobTitle }) .OrderBy(e => e.FirstName).ThenBy(e => e.LastName).ToList() }) .ToList(); foreach (var d in departments) { sb.AppendLine($"{d.Name} - {d.FirstName} {d.LastName}"); foreach (var e in d.Employees) { sb.AppendLine($"{e.FirstName} {e.LastName} - {e.JobTitle}"); } } return(sb.ToString().TrimEnd()); }
public static string GetEmployeesFromResearchAndDevelopment(SoftUniContext context) { StringBuilder sb = new StringBuilder(); var employees = context.Employees .Select(e => new { e.FirstName, e.LastName, DepName = e.Department.Name, e.Salary }) .Where(x => x.DepName == "Research and Development") .OrderBy(x => x.Salary) .ThenByDescending(x => x.FirstName) .ToArray(); foreach (var employee in employees) { sb.AppendLine($"{employee.FirstName} {employee.LastName} from {employee.DepName} - ${employee.Salary:f2}"); } return(sb.ToString()); }
//P03 public static string GetEmployeesFullInformation(SoftUniContext context) { var result = new StringBuilder(); var employees = context.Employees.Select(e => new { e.EmployeeId, e.FirstName, e.MiddleName, e.LastName, e.JobTitle, e.Salary }) .OrderBy(e => e.EmployeeId) .ToList(); foreach (var e in employees) { result.AppendLine($"{e.FirstName} {e.LastName} {e.MiddleName} {e.JobTitle} {e.Salary:F2}"); } return(result.ToString().TrimEnd()); }
//15.Remove Town public static string RemoveTown(SoftUniContext context) { var addresses = context.Addresses .Where(a => a.Town.Name == "Seattle") .ToList(); var town = context.Towns .Where(t => t.Name == "Seattle") .FirstOrDefault(); foreach (var employee in context.Employees) { if (addresses.Contains(employee.Address)) { employee.Address = null; } } context.Addresses.RemoveRange(addresses); context.Towns.Remove(town); context.SaveChanges(); return($"{addresses.Count} addresses in Seattle were deleted"); }
public static string GetEmployeesInPeriod(SoftUniContext context) { StringBuilder result = new StringBuilder(); var employees = context.Employees. Where(e => e.EmployeesProjects.Any(p => p.Project.StartDate.Year >= 2001 && p.Project.StartDate.Year <= 2003)) .Select(x => new { EmployeeFullName = x.FirstName + " " + x.LastName, ManagerFullName = x.Manager.FirstName + " " + x.Manager.LastName, Projects = x.EmployeesProjects.Select(p => new { ProjectName = p.Project.Name, StartDate = p.Project.StartDate.ToString("M/d/yyyy h:mm:ss tt"), EndDate = p.Project.EndDate }) }) .Take(10).ToArray(); foreach (var employee in employees) { result.AppendLine($"{employee.EmployeeFullName} - Manager: {employee.ManagerFullName}"); foreach (var project in employee.Projects) { string endDateString = !project.EndDate.HasValue ? "not finished" : Convert.ToDateTime(project.EndDate).ToString("M/d/yyyy h:mm:ss tt"); result.AppendLine($"--{project.ProjectName} - {project.StartDate} - {endDateString}"); } } return(result.ToString().TrimEnd()); }
public static string GetEmployeesFromResearchAndDevelopment(SoftUniContext context) { var employees = context.Employees .Where(e => e.Department.Name == "Research and Development") .Select(e => new { e.FirstName, e.LastName, e.Department.Name, e.Salary }) .OrderBy(e => e.Salary) .ThenByDescending(e => e.FirstName) .ToList(); StringBuilder sb = new StringBuilder(); foreach (var employee in employees) { sb.AppendLine($"{employee.FirstName} {employee.LastName}" + $" from Research and Development - ${employee.Salary:F2}"); } return(sb.ToString().TrimEnd()); }
//08.Addresses by Town public static string GetAddressesByTown(SoftUniContext context) { var addresses = context.Addresses .Select(a => new { AddressText = a.AddressText, TownName = a.Town.Name, EmployeeCount = a.Employees.Count }) .OrderByDescending(e => e.EmployeeCount) .ThenBy(e => e.TownName) .ThenBy(e => e.AddressText) .Take(10) .ToList(); StringBuilder sb = new StringBuilder(); foreach (var address in addresses) { sb.AppendLine($"{address.AddressText}, {address.TownName} - {address.EmployeeCount} employees"); } return(sb.ToString()); }
public static string AddNewAddressToEmployee(SoftUniContext context) { var address = new Address { AddressText = "Vitoshka 15", TownId = 4 }; context.Addresses.Add(address); var nakov = context.Employees .FirstOrDefault(e => e.LastName == "Nakov"); nakov.Address = address; context.SaveChanges(); var employees = context.Employees .Select(e => new { e.Address.AddressText, e.AddressId }) .OrderByDescending(e => e.AddressId) .Take(10) .ToList(); StringBuilder sb = new StringBuilder(); foreach (var employee in employees) { sb.AppendLine($"{employee.AddressText}"); } return(sb.ToString().TrimEnd()); }
public static string GetEmployeesByFirstNameStartingWithSa(SoftUniContext context) { var employees = context.Employees .Where(e => EF.Functions.Like(e.FirstName, "sa%")) .Select(e => new { e.FirstName, e.LastName, e.JobTitle, e.Salary }) .OrderBy(x => x.FirstName) .ThenBy(x => x.LastName) .ToList(); StringBuilder sb = new StringBuilder(); foreach (var employee in employees) { sb.AppendLine($"{employee.FirstName} {employee.LastName} - {employee.JobTitle} - (${employee.Salary:F2})"); } return(sb.ToString().TrimEnd()); }
public static string GetEmployee147(SoftUniContext context) { StringBuilder str = new StringBuilder(); var employee147 = context.Employees.FirstOrDefault(x => x.EmployeeId == 147); str.AppendLine($"{employee147.FirstName} {employee147.LastName} - {employee147.JobTitle}"); var projects = context.EmployeesProjects .Where(x => x.EmployeeId == 147) .Select(x => new { ProjectName = x.Project.Name }) .OrderBy(x => x.ProjectName) .ToList(); foreach (var project in projects) { str.AppendLine($"{project.ProjectName}"); } return(str.ToString()); }
public static string GetDepartmentsWithMoreThan5Employees(SoftUniContext context) { StringBuilder sb = new StringBuilder(); var departments = context.Departments .Where(x => x.Employees.Count > 5) .OrderBy(x => x.Employees.Count) .ThenBy(x => x.Name) .Select(x => new { DepartmentName = x.Name, ManagerFullName = x.Manager.FirstName + " " + x.Manager.LastName, Employees = x.Employees.Select(e => new { EmployeeFullName = e.FirstName + " " + e.LastName, JobTitle = e.JobTitle }) .OrderBy(x => x.EmployeeFullName) .ToList() }) .ToList(); foreach (var department in departments) { sb.AppendLine($"{department.DepartmentName} {department.ManagerFullName}"); foreach (var employee in department.Employees) { sb.AppendLine($"{employee.EmployeeFullName} {employee.JobTitle}"); } } return(sb.ToString()); }
public static string IncreaseSalaries(SoftUniContext context) { StringBuilder result = new StringBuilder(); var employees = context.Employees .Where(e => e.Department.Name == "Engineering" || e.Department.Name == "Tool Design" || e.Department.Name == "Marketing" || e.Department.Name == "Information Services") .ToList(); foreach (var employee in employees) { employee.Salary += (employee.Salary * 0.12m); } var promotedЕmployees = employees.Select(e => new { e.FirstName, e.LastName, Salary = e.Salary.ToString("F2") }) .OrderBy(e => e.FirstName) .ThenBy(e => e.LastName) .ToList(); foreach (var employee in promotedЕmployees) { result.AppendLine($"{employee.FirstName} {employee.LastName} (${employee.Salary})"); } return(result.ToString().TrimEnd()); }
public static string DeleteProjectById(SoftUniContext context) { var employeeProjects = context.EmployeesProjects.Where(x => x.ProjectId == 2).ToList(); foreach (var employeeProject in employeeProjects) { context.EmployeesProjects.Remove(employeeProject); } context.Projects.Remove(context.Projects.Find(2)); context.SaveChanges(); var sb = new StringBuilder(); foreach (var project in context.Projects.Take(10).ToList()) { sb.AppendLine($"{project.Name}"); } var result = sb.ToString().TrimEnd(); return(result); }
public static string RemoveTown(SoftUniContext context) { var employees = context.Employees .Where(e => e.Address.Town.Name == "Seattle") .ToList(); foreach (var employee in employees) { employee.AddressId = null; context.SaveChanges(); } var towns = context.Towns .Where(t => t.Name == "Seattle") .ToList(); var addresses = context.Addresses .Where(a => a.Town.Name == "Seattle") .ToList(); int count = addresses.Count(); foreach (var address in addresses) { context.Addresses.Remove(address); context.SaveChanges(); } foreach (var town in towns) { context.Towns.Remove(town); context.SaveChanges(); } return($"{count} addresses in Seattle were deleted"); }
//Problem 08 public static string GetAddressesByTown(SoftUniContext context) { var sb = new StringBuilder(); var addresses = context.Addresses .OrderByDescending(a => a.Employees.Count()) .ThenBy(a => a.Town.Name) .ThenBy(a => a.AddressText) .Take(10) .Select(a => new { a.AddressText, AddressTownName = a.Town.Name, EmployeeCount = a.Employees.Count }).ToList(); foreach (var a in addresses) { sb.AppendLine ($@"{a.AddressText}, {a.AddressTownName} - {a.EmployeeCount} employees"); } return(sb.ToString().TrimEnd()); }
public static string GetEmployee147(SoftUniContext context) { StringBuilder builder = new StringBuilder(); var employee = context.Employees .Where(x => x.EmployeeId == 147) .Select(x => new { FirstName = x.FirstName, LastName = x.LastName, JobTitle = x.JobTitle, Projects = x.EmployeesProjects.Select(y => new { ProjectName = y.Project.Name }).ToList() }) .FirstOrDefault(); builder.AppendLine($"{employee.FirstName} {employee.LastName} - {employee.JobTitle}"); foreach (var employeeProject in employee.Projects.OrderBy(x => x.ProjectName)) { builder.AppendLine(employeeProject.ProjectName); } return(builder.ToString()); }
public static string DeleteProjectById(SoftUniContext context) { var id = context.Projects.Find(2); var employeeProjects = context.EmployeesProjects.Where(ep => ep.ProjectId == id.ProjectId) .ToList(); context.EmployeesProjects.RemoveRange(employeeProjects); context.Projects.Remove(id); context.SaveChanges(); var projects = context.Projects .Select(p => p.Name) .Take(10) .ToList(); StringBuilder sb = new StringBuilder(); foreach (var project in projects) { sb.AppendLine($"{project}"); } return(sb.ToString().TrimEnd()); }
public static string IncreaseSalaries(SoftUniContext context) { StringBuilder sb = new StringBuilder(); var employees = context .Employees .Where(e => e.Department.Name == "Engineering" || e.Department.Name == "Tool Design" || e.Department.Name == "Marketing" || e.Department.Name == "Information Services"); foreach (var employee in employees) { employee.Salary *= 1.12M; } context.SaveChanges(); var employeesToDisplay = employees .Select(e => new { e.FirstName, e.LastName, e.Salary }) .OrderBy(e => e.FirstName) .ThenBy(e => e.LastName); foreach (var employee in employeesToDisplay) { sb.AppendLine($"{employee.FirstName} {employee.LastName} (${employee.Salary:f2})"); } return(sb.ToString().TrimEnd()); }
public static string GetLatestProjects(SoftUniContext context) { StringBuilder sb = new StringBuilder(); var addressesByTown = context .Addresses .Select(x => new { x.AddressText, TownName = x.Town.Name, x.Employees }) .OrderByDescending(x => x.Employees.Count()) .ThenBy(x => x.TownName) .ThenBy(x => x.AddressText) .Take(10); foreach (var item in addressesByTown) { sb.AppendLine($"{item.AddressText}, {item.TownName} - {item.Employees.Count} employees"); } return(sb.ToString().TrimEnd()); }
public static string GetAddressesByTown(SoftUniContext context) { var adresses = context.Addresses .OrderByDescending(x => x.Employees.Count) .ThenBy(x => x.Town.Name) .ThenBy(x => x.AddressText) .Take(10) .Select(x => new { x.AddressText, x.Town.Name, Count = x.Employees.Count }) .ToList(); var result = new StringBuilder(); foreach (var adress in adresses) { result.AppendLine($"{adress.AddressText}, {adress.Name} - {adress.Count} employees"); } return(result.ToString().TrimEnd()); }
//10.Departments with More Than 5 Employees public static string GetDepartmentsWithMoreThan5Employees(SoftUniContext context) { var departmentEmployees = context.Departments .Where(d => d.Employees.Count > 5) .OrderBy(d => d.Employees.Count) .ThenBy(d => d.Name) .Select(d => new { DepartmentName = d.Name, ManagerName = d.Manager.FirstName + " " + d.Manager.LastName, Employees = d.Employees .Select(e => new { e.FirstName, e.LastName, e.JobTitle }) .OrderBy(e => e.FirstName) .ThenBy(e => e.LastName) .ToList() }).ToList(); StringBuilder sb = new StringBuilder(); foreach (var department in departmentEmployees) { sb.AppendLine($"{department.DepartmentName} - {department.ManagerName}"); foreach (var employee in department.Employees) { sb.AppendLine($"{employee.FirstName} {employee.LastName} - {employee.JobTitle}"); } } return(sb.ToString()); }
public static string GetEmployeesByFirstNameStartingWithSa(SoftUniContext context) { var employees = context.Employees .Where(x => x.FirstName.StartsWith("Sa")) .Select(x => new { x.FirstName, x.LastName, x.JobTitle, x.Salary }) .OrderBy(x => x.FirstName) .ThenBy(x => x.LastName) .ToList(); var result = new StringBuilder(); foreach (var employee in employees) { result.AppendLine($"{employee.FirstName} {employee.LastName} - {employee.JobTitle} - (${employee.Salary:F2})"); } return(result.ToString().TrimEnd()); }
/* * * Write a program that finds all employees whose first name starts with "Sa". * Return their first, last name, their job title and salary, rounded to 2 symbols * after the decimal separator in the format given in the example below. Order them by first name, * then by last name (ascending). * */ public static string GetEmployeesByFirstNameStartingWithSa(SoftUniContext context) { var employees = context.Employees .Where(x => x.FirstName.StartsWith("Sa")) .Select(x => new { FirstName = x.FirstName, LastName = x.LastName, Job = x.JobTitle, Salary = x.Salary }) .OrderBy(x => x.FirstName) .ThenBy(x => x.LastName) .ToList(); StringBuilder sb = new StringBuilder(); foreach (var e in employees) { sb.AppendLine($"{e.FirstName} {e.LastName} - {e.Job} - (${e.Salary:f2})"); } return(sb.ToString().TrimEnd()); }
//P07. Employees and Projects public static string GetEmployeesInPeriod(SoftUniContext context) { var employees = context.Employees .Where(e => e.EmployeesProjects .Any(p => p.Project.StartDate.Year >= 2001 && p.Project.StartDate.Year <= 2003)) .Take(10) .Select(e => new { EmployeeFullName = e.FirstName + " " + e.LastName, ManagerFullName = e.Manager.FirstName + " " + e.Manager.LastName, Projects = e.EmployeesProjects.Select(p => new { ProjectName = p.Project.Name, ProjectStartDate = p.Project.StartDate, ProjectEndDate = p.Project.EndDate }).ToList() }) .ToList(); var info = new StringBuilder(); foreach (var employee in employees) { info.AppendLine($"{employee.EmployeeFullName} - Manager: {employee.ManagerFullName}"); foreach (var project in employee.Projects) { string startDate = project.ProjectStartDate.ToString("M/d/yyyy h:mm:ss tt"); string endDate = project.ProjectEndDate == null ? "not finished" : project.ProjectEndDate.Value.ToString("M/d/yyyy h:mm:ss tt"); info.AppendLine($"--{project.ProjectName} - {startDate} - {endDate}"); } } return(info.ToString().TrimEnd()); }
public static string GetEmployeesByFirstNameStartingWithSa(SoftUniContext context) { var employees = context.Employees .Where(e => e.FirstName.Substring(0, 2) == "Sa") .Select(e => new { e.FirstName, e.LastName, e.JobTitle, e.Salary }) .OrderBy(e => e.FirstName) .ThenBy(e => e.LastName) .ToList(); var sb = new StringBuilder(); foreach (var e in employees) { sb.AppendLine($"{e.FirstName} {e.LastName} - {e.JobTitle} - (${e.Salary:F2})"); } return(sb.ToString().TrimEnd()); }
public static string GetEmployee147(SoftUniContext context) { StringBuilder sb = new StringBuilder(); var employee = context.Employees .Select(e => new { e.EmployeeId, e.FirstName, e.LastName, e.JobTitle, Projects = e.EmployeesProjects.Select(em => em.Project.Name) .OrderBy(x => x) }) .FirstOrDefault(x => x.EmployeeId == 147); sb.AppendLine($"{employee.FirstName} {employee.LastName} - {employee.JobTitle}"); foreach (var project in employee.Projects) { sb.AppendLine(project); } return(sb.ToString().TrimEnd()); }
//P07 public static string GetEmployeesInPeriod(SoftUniContext context) { StringBuilder sb = new StringBuilder(); var employees = context.Employees .Where(e => e.EmployeesProjects .Any(ep => ep.Project.StartDate.Year >= 2001 && ep.Project.StartDate.Year <= 2003 )) .Select(e => new { e.FirstName, e.LastName, ManagerFirstName = e.Manager.FirstName, ManagerLastName = e.Manager.LastName, Projects = e.EmployeesProjects.Select(ep => new { ProjectName = ep.Project.Name, StartDate = ep.Project.StartDate.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture), EndDate = ep.Project.EndDate.HasValue ? ep.Project.EndDate.Value.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture) : "not finished" }).ToList() }) .Take(10) .ToList(); foreach (var e in employees) { sb.AppendLine($"{e.FirstName} {e.LastName} - Manager: {e.ManagerFirstName} {e.ManagerLastName}"); foreach (var p in e.Projects) { sb.AppendLine($"--{p.ProjectName} - {p.StartDate} - {p.EndDate}"); } } return(sb.ToString().TrimEnd()); }
public static string AddNewAddressToEmployee(SoftUniContext context) { StringBuilder sb = new StringBuilder(); Address address = new Address() { AddressText = "Vitoshka 15", TownId = 4 }; Employee employee = context.Employees .FirstOrDefault(e => e.LastName == "Nakov"); employee.Address = address; context.SaveChanges(); var addresses = context.Addresses .OrderByDescending(a => a.AddressId) .Select(e => e.AddressText) .Take(10) .ToList(); addresses.ForEach(e => sb.AppendLine(e)); return(sb.ToString().TrimEnd()); }
public static string GetLatestProjects(SoftUniContext context) { StringBuilder sb = new StringBuilder(); var projects = context.Projects .OrderByDescending(p => p.StartDate) .Select(p => new { p.Name, p.Description, StartDate = p.StartDate.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture) }) .Take(10) .ToArray(); foreach (var item in projects.OrderBy(p => p.Name)) { sb.AppendLine($"{item.Name}"); sb.AppendLine($"{item.Description}"); sb.AppendLine($"{item.StartDate}"); } return(sb.ToString().TrimEnd()); }
public static string RemoveTown(SoftUniContext context) { var townToDel = context.Towns .Include(x => x.Addresses) .FirstOrDefault(t => t.Name == "Seattle"); var addressToDel = townToDel.Addresses .Select(a => a.AddressId) .ToList(); var employees = context.Employees .Where(x => x.AddressId.HasValue && addressToDel.Contains(x.AddressId.Value)) .ToList(); var addressesCount = addressToDel.Count(); foreach (var empl in employees) { empl.AddressId = null; } foreach (var item in addressToDel) { var address = context.Addresses.FirstOrDefault(x => x.AddressId == item); context.Addresses.Remove(address); } context.Towns.Remove(townToDel); context.SaveChanges(); var result = $"{addressesCount} addresses in Seattle were deleted"; return(result); }