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