private static void DeleteProjectById(SoftuniContext context)
{
var project = context.Projects.FirstOrDefault(p => p.ProjectID == 2);
if (project != null)
{
var projectEmployees = project.Employees;
foreach (var emp in projectEmployees)
{
emp.Projects.Remove(project);
}
context.Projects.Remove(project);
context.SaveChanges();
}
var projectsName = context.Projects
.Select(p => p.Name).Take(10).ToList();
foreach (var name in projectsName)
{
Console.WriteLine(name);
}
}
private static void RemoveTowns(SoftuniContext context)
{
var townName = Console.ReadLine();
var town = context.Towns.FirstOrDefault(t => t.Name == townName);
if (town == null)
{
Console.WriteLine("There isn't a town with that name in database");
}
else
{
var addressIds = context.Addresses
.Where(a => a.TownID == town.TownID).ToList().Select(a => a.AddressID).ToList();
var employeeWithAdress = context.Employees
.Where(e => addressIds.Contains((int)e.AddressID)).ToList();
foreach (var emp in employeeWithAdress)
{
emp.AddressID = null;
}
foreach (var addressId in addressIds)
{
context.Addresses.Remove(context.Addresses.FirstOrDefault(a => a.AddressID == addressId));
}
Console.WriteLine($"{addressIds.Count} addresses in {townName} were deleted");
context.Towns.Remove(town);
context.SaveChanges();
}
}
private static void EmplProjFrom20012003(SoftuniContext context)
{
var employees = context.Employees.ToList();
StringBuilder sb = new StringBuilder();
foreach (var employee in employees)
{
var emplProjects = employee.Projects
.Where(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003).ToList();
if (emplProjects.Count > 0)
{
if (employee.Manager == null)
{
sb.AppendLine($"{employee.FirstName} {employee.LastName} ");
}
else
{
sb.AppendLine($"{employee.FirstName} {employee.LastName} {employee.Manager.FirstName}");
}
foreach (var project in emplProjects)
{
sb.AppendLine($"--{project.Name} {project.StartDate:M/d/yyyy h:mm:ss tt} {project.EndDate:M/d/yyyy h:mm:ss tt}");
}
}
}
Console.Write(sb.ToString());
}
private static void AddAddressToEmployee(SoftuniContext context)
{
Address address = new Address();
address.AddressText = "Vitoshka 15";
address.TownID = 4;
context.Addresses.Add(address);
var employees = context.Employees
.Where(e => e.LastName == "Nakov");
foreach (var employee in employees)
{
employee.Addresses = address;
}
context.SaveChanges();
var emplAdresses = context.Employees
.OrderByDescending(e => e.AddressID)
.Take(10)
.Select(e => e.Addresses.AddressText)
.ToList();
foreach (var adress in emplAdresses)
{
Console.WriteLine(adress);
}
}
private static void AddingNewAddress(SoftuniContext context)
{
Address addr = new Address()
{
AddressText = "Vitoshka 15",
TownID = 4
};
Employee employee = null;
employee = context.Employees
.Where(e => e.LastName == "Nakov")
.FirstOrDefault();
employee.Address = addr;
context.SaveChanges();
List <string> employeesAddr = context.Employees
.OrderByDescending(e => e.AddressID)
.Take(10)
.Select(e => e.Address.AddressText)
.ToList();
foreach (string ea in employeesAddr)
{
Console.WriteLine(ea);
}
}
private static void EmployeeFullInfo(SoftuniContext context)
{
List <Employee> emloyees = context.Employees.ToList();
foreach (Employee e in emloyees)
{
Console.WriteLine($"{e.FirstName} {e.LastName} {e.MiddleName} {e.JobTitle} {e.Salary:f4}");
}
}
private static void EmployeesFullInformation(SoftuniContext context)
{
var employees = context.Employees.ToList().OrderBy(e => e.EmployeeID);
foreach (var e in employees)
{
Console.WriteLine($"{e.FirstName} {e.LastName} {e.MiddleName} {e.JobTitle} {e.Salary:f4}");
}
}
private static void FindEmplStartWithSA(SoftuniContext context)
{
var employees = context.Employees
.Where(e => e.FirstName.StartsWith("sa"));
foreach (var emp in employees)
{
Console.WriteLine($"{emp.FirstName} {emp.LastName} - {emp.JobTitle} - (${emp.Salary:f4})");
}
}
private static void EmployeeWithSalaryOver50000(SoftuniContext context)
{
List <String> emloyeeNames = context.Employees
.Where(e => e.Salary > 50000)
.Select(e => e.FirstName).ToList();
foreach (string name in emloyeeNames)
{
Console.WriteLine(name);
}
}
private static void EmplFNAmeSalaryOver50000(SoftuniContext context)
{
var employeesName = context.Employees
.Where(e => e.Salary > 50000)
.Select(e => e.FirstName);
foreach (var empName in employeesName)
{
Console.WriteLine(empName);
}
}
private static void AddressesByTownName(SoftuniContext context)
{
var adresses = context.Addresses
.OrderByDescending(a => a.Employees.Count)
.ThenBy(a => a.Town.Name)
.Take(10);
foreach (var a in adresses)
{
Console.WriteLine($"{a.AddressText}, {a.Town.Name} - {a.Employees.Count} employees");
}
}
private static void EmplFromResearchAndDev(SoftuniContext context)
{
var employeesReserchDevelop = context.Employees
.Where(e => e.Department.Name == "Research and Development")
.OrderBy(e => e.Salary)
.ThenByDescending(e => e.FirstName);
foreach (var emp in employeesReserchDevelop)
{
Console.WriteLine($"{emp.FirstName} {emp.LastName} from Research and Development - ${emp.Salary:f2}");
}
}
private static void FindLatest10Project(SoftuniContext context)
{
var projects = context.Projects
.OrderByDescending(p => p.StartDate)
.Take(10)
.OrderBy(p => p.Name);
foreach (var pr in projects)
{
Console.WriteLine($"{pr.Name} {pr.Description} {pr.StartDate:M/d/yyyy h:mm:ss tt} {pr.EndDate:M/d/yyyy h:mm:ss tt}");
}
}
private static void FindEmployeesWhithSA(SoftuniContext context)
{
List <Employee> employees = context.Employees
.Where(e => e.FirstName.StartsWith("Sa")).
ToList();
foreach (Employee e in employees)
{
Console.WriteLine($"{e.FirstName} " +
$"{e.LastName} - {e.JobTitle} - " +
$"(${e.Salary:f4})");
}
}
private static void Employee147(SoftuniContext context)
{
var employee = context.Employees.FirstOrDefault(e => e.EmployeeID == 147);
if (employee == null)
{
throw new ArgumentNullException("There is no employee with ID 147");
}
Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.JobTitle}");
foreach (var name in employee.Projects.OrderBy(p => p.Name).Select(p => p.Name).ToList())
{
Console.WriteLine(name);
}
}
private static void FindLatest10Projects(SoftuniContext context)
{
List <Project> projects = context.Projects
.OrderByDescending(p => p.StartDate)
.Take(10)
.OrderBy(p => p.Name)
.ToList();
foreach (Project p in projects)
{
Console.WriteLine($"{p.Name} " +
$"{p.Description} " +
$"{p.StartDate} {p.EndDate}");
}
}
private static void EmployeesFromSeattle(SoftuniContext context)
{
List <Employee> emloyees = context.Employees
.Where(e => e.Department.Name == "Research and Development")
.OrderBy(e => e.Salary)
.ThenByDescending(e => e.FirstName)
.ToList();
foreach (Employee e in emloyees)
{
Console.WriteLine($"{e.FirstName} " +
$"{e.LastName} from {e.Department.Name}" +
$" - ${e.Salary:f2}");
}
}
private static void DepWithMoreEmpl(SoftuniContext context)
{
var departments = context.Departments
.Where(d => d.DepartmentEmployees.Count > 5)
.OrderBy(d => d.DepartmentEmployees.Count)
.ToList();
foreach (var dep in departments)
{
Console.WriteLine($"{dep.Name} {dep.Manager.FirstName}");
foreach (var emp in dep.DepartmentEmployees)
{
Console.WriteLine($"{emp.FirstName} {emp.LastName} {emp.JobTitle}");
}
}
}
private static void FindEmployeesInPeriod(SoftuniContext context)
{
List <Employee> employees = context.Employees
.Where(e => e.Projects.Count(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003) > 0)
.Take(30).ToList();
foreach (Employee e in employees)
{
Console.WriteLine($"{e.FirstName} {e.LastName} " +
$"{e.Manager.FirstName}");
foreach (Project p in e.Projects)
{
Console.WriteLine($"--{p.Name} " +
$"{p.StartDate} " +
$"{p.EndDate}");
}
}
}
private static void IncreaseSalaries(SoftuniContext context)
{
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 emp in employees)
{
emp.Salary *= 1.12m;
}
context.SaveChanges();
foreach (var e in employees)
{
Console.WriteLine($"{e.FirstName} {e.LastName} (${e.Salary:f6})");
}
}
private static void DepartmentsWithMoreThan5Employees(SoftuniContext context)
{
List <Department> deps = context.Departments
.Where(d => d.Employees.Count > 5)
.OrderBy(d => d.Employees.Count)
.ToList();
foreach (Department d in deps)
{
Console.WriteLine($"{d.Name} " +
$"{d.Manager.FirstName}");
foreach (Employee e in d.Employees)
{
Console.WriteLine($"{e.FirstName} " +
$"{e.LastName} {e.JobTitle}");
}
}
}
private static void DeleteProjectById(SoftuniContext context)
{
var project = context.Projects.Find(2);
foreach (Employee e in project.Employees)
{
e.Projects.Remove(project);
}
context.Projects.Remove(project);
context.SaveChanges();
List <Project> projects = context.Projects
.Take(10)
.ToList();
foreach (Project p in projects)
{
Console.WriteLine(p.Name);
}
}
private static void RemoveTowns(SoftuniContext context)
{
Console.Write("Enter town to delete: ");
string town = Console.ReadLine();
Town tn = context.Towns
.Where(t => t.Name == town)
.FirstOrDefault();
List <Address> addrs = context.Addresses
.Where(a => a.Town.Name == town)
.ToList();
context.Towns.Remove(tn);
foreach (Address a in addrs)
{
List <Employee> empls = context.Employees
.Where(e => e.AddressID == a.AddressID)
.ToList();
foreach (Employee e in empls)
{
e.AddressID = null;
}
context.Addresses.Remove(a);
}
context.SaveChanges();
if (addrs.Count == 1)
{
Console.WriteLine($"{addrs.Count} " +
$"address in {town} was deleted");
}
else
{
Console.WriteLine($"{addrs.Count} " +
$"addresses in {town} were deleted");
}
}
private static void EmployeeWithId147(SoftuniContext context)
{
Employee emp = context.Employees
.Where(e => e.EmployeeID == 147)
.FirstOrDefault();
Console.WriteLine($"{emp.FirstName} " +
$"{emp.LastName} {emp.JobTitle}");
List <Project> projects = new List <Project>();
foreach (Project p in emp.Projects)
{
projects.Add(p);
}
var projs = projects.OrderBy(p => p.Name);
foreach (Project p in projs)
{
Console.WriteLine(p.Name);
}
}
private static void IncreaseSalaries(SoftuniContext context)
{
List <Employee> 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 (Employee e in employees)
{
e.Salary = e.Salary + e.Salary * 12 / 100;
}
context.SaveChanges();
foreach (Employee e in employees)
{
Console.WriteLine($"{e.FirstName} " +
$"{e.LastName} (${e.Salary:f6})");
}
}
static void Main(string[] args)
{
SoftuniContext context = new SoftuniContext();
Console.WriteLine("Select an option:");
Console.WriteLine("3. Employees full information");
Console.WriteLine("4. Employees with Salary Over 50 000");
Console.WriteLine("5. Employees from Seattle");
Console.WriteLine("6. Adding a New Address and Updating Employee");
Console.WriteLine("7. Find employees in period");
Console.WriteLine("8. Addresses by town name");
Console.WriteLine("9. Employee with id 147");
Console.WriteLine("10. Departments with more than 5 employees");
Console.WriteLine("11. Find Latest 10 Projects");
Console.WriteLine("12. Increase Salaries");
Console.WriteLine("13. Find Employees by First Name starting with SA");
Console.WriteLine("14. First Letter");
Console.WriteLine("15. Delete Project by Id");
Console.WriteLine("16. Remove Towns");
Console.WriteLine();
Console.Write("Enter your choise: ");
int input = 0;
try
{
input = int.Parse(Console.ReadLine());
}
catch
{
Console.WriteLine("You need to enter a number.");
}
switch (input)
{
case 3: EmployeeFullInfo(context); break;
case 4: EmployeeWithSalaryOver50000(context); break;
case 5: EmployeesFromSeattle(context); break;
case 6: AddingNewAddress(context); break;
case 7: FindEmployeesInPeriod(context); break;
case 8: AddressByTownName(context); break;
case 9: EmployeeWithId147(context); break;
case 10: DepartmentsWithMoreThan5Employees(context); break;
case 11: FindLatest10Projects(context); break;
case 12: IncreaseSalaries(context); break;
case 13: FindEmployeesWhithSA(context); break;
case 14: FirstLetter(); break;
case 15: DeleteProjectById(context); break;
case 16: RemoveTowns(context); break;
default: break;
}
}
public static void Main()
{
SoftuniContext context = new SoftuniContext();
//select with ef
var data = context.Employees.Select(e => new
{
Name = e.FirstName + " " + e.LastName,
TownName = e.Addresses.Town.Name
}).ToList();
//join tables in ef
var employees = context.Employees.Join(
context.Departments,
(e => e.DepartmentID),
(d => d.DepartmentID),
(e, d) => new
{
EmployeeName = e.FirstName + " " + e.LastName,
JobTitle = e.JobTitle,
Department = d.Name
}).ToList();
//group by table
var group = context.Employees.GroupBy(e => e.DepartmentID).Select(e => new AverigeSalariesByDepartment()
{
DepartmentId = e.Key,
AverageSalary = e.Average(d => d.Salary)
}).ToList();
foreach (var item in group)
{
PrintSalary(item);
}
/* 03.EmployeesFullInformation(context);
*
* 04.EmplFNAmeSalaryOver50000(context);
*
* 05.EmplFromResearchAndDev(context);
*
* 06.AddAddressToEmployee(context);
*
* 07.EmplProjFrom20012003(context);
*
* 08.AddressesByTownName(context);
*
* 09.Employee147(context);
*
* 10.DepWithMoreEmpl(context);
*
* 11. FindLatest10Project(context);
*
* 12. IncreaseSalaries(context);
*
* 13. FindEmplStartWithSA(context);
*
* 15.DeleteProjectById(context);
*
* 16. RemoveTowns(context);*/
}
