public static string RemoveTown(SoftUniContext context)
{
var seattle = context
.Towns
.First(t => t.Name == "Seattle");
var addressesInTown = context
.Addresses
.Where(a => a.Town.Name == "Seattle");
var employeesToRemoveAddress = context
.Employees
.Where(e => addressesInTown.Contains(e.Address));
employeesToRemoveAddress.ToList().ForEach(x => x.AddressId = null);
context.Addresses.RemoveRange(addressesInTown);
addressesInTown.ToList().ForEach(x => context.Addresses.Remove(x));
int adressesCount = addressesInTown.Count();
context.Towns.Remove(seattle);
context.SaveChanges();
return($"{adressesCount} addresses in Seattle were deleted");
}
public static string RemoveTown(SoftUniContext context)
{
var townToDelete = context.Towns
.FirstOrDefault(t => t.Name == "Seattle");
var addressesToDelete = context.Addresses
.Where(a => a.TownId == townToDelete.TownId);
var addressesDeletedCount = addressesToDelete.Count();
var employeesAddressesToReplace = context.Employees
.Where(e => addressesToDelete.Any(a => a.AddressId == e.AddressId));
foreach (var employee in employeesAddressesToReplace)
{
employee.AddressId = null;
}
foreach (var address in addressesToDelete)
{
context.Addresses.Remove(address);
}
context.Towns.Remove(townToDelete);
context.SaveChanges();
return(addressesDeletedCount + " addresses in Seattle were deleted");
}
public static string DeleteProjectById(SoftUniContext context)
{
var sb = new StringBuilder();
var projectToDelete = context.Projects
.FirstOrDefault(p => p.ProjectId == 2);
var employeeProjectsToDelete = context.EmployeesProjects
.Where(ep => ep.ProjectId == 2)
.ToList();
foreach (var employeeProject in employeeProjectsToDelete)
{
context.EmployeesProjects.Remove(employeeProject);
}
context.Projects.Remove(projectToDelete);
context.SaveChanges();
var projects = context.Projects
.Select(p => p.Name)
.Take(10)
.ToList();
foreach (var project in projects)
{
sb.AppendLine($"{project}");
}
return(sb.ToString().Trim());
}
//----------Task 12 - Increase Salaries----------
public static string IncreaseSalaries(SoftUniContext context)
{
var desiredDepartments = new[] { "Engineering", "Tool Design", "Marketing", "Information Services" };
var employees = context
.Employees
.Where(e => desiredDepartments.Contains(e.Department.Name));
foreach (var e in employees)
{
e.Salary *= 1.12M;
}
context.SaveChanges();
var employeesWithIncreasedSalary = employees
.Select(e => new
{
firstName = e.FirstName,
lastName = e.LastName,
salary = e.Salary
})
.OrderBy(e => e.firstName)
.ThenBy(e => e.lastName)
.ToList();
var sb = new StringBuilder();
foreach (var em in employeesWithIncreasedSalary)
{
sb.AppendLine($"{em.firstName} {em.lastName} (${em.salary:F2})");
}
return(sb.ToString().Trim());
}
//Problem 6
public static string AddNewAddressToEmployee(SoftUniContext context)
{
StringBuilder sb = new StringBuilder();
Address address = new Address
{
AddressText = "Vitoshka 15",
TownId = 4
};
var employee = context.Employees
.First(e => e.LastName == "Nakov");
employee.Address = address;
context.SaveChanges();
List <string> result = context.Employees
.OrderByDescending(e => e.AddressId)
.Take(10)
.Select(e => e.Address.AddressText)
.ToList();
foreach (var addr in result)
{
sb.AppendLine(addr);
}
return(sb.ToString().TrimEnd());
}
//6. Adding a New Address and Updating Employee
public static string AddNewAddressToEmployee(SoftUniContext context)
{
StringBuilder sb = new StringBuilder();
Address newAdress = new Address()
{
AddressText = "Vitoshka 15",
TownId = 4
};
Employee nakov = context.Employees.First(e => e.LastName == "Nakov");
nakov.Address = newAdress;
context.SaveChanges();
Employee[] employees = context.Employees
.OrderByDescending(e => e.AddressId)
.Take(10)
.ToArray();
foreach (var item in employees)
{
sb.AppendLine(item.Address.AddressText);
}
return(sb.ToString().TrimEnd());
}
public static string RemoveTown(SoftUniContext context)
{
var town = context.Towns.FirstOrDefault(t => t.Name == "Seattle");
var townId = context.Towns
.Where(t => t.Name == "Seattle")
.Select(t => t.TownId)
.FirstOrDefault();
int addressId = context.Addresses
.Where(a => a.TownId == townId)
.Select(x => x.AddressId).FirstOrDefault();
var addressesWithTown = context.Addresses
.Where(t => t.TownId == townId)
.ToList();
var employees = context.Employees.Where(e => e.Address.TownId == townId).ToList();
employees.ForEach(e => e.AddressId = null);
context.Addresses.RemoveRange(addressesWithTown);
context.Towns.Remove(town);
context.SaveChanges();
return($"{addressesWithTown.Count} addresses in Seattle were deleted");
}
public static string RemoveTown(SoftUniContext context)
{
var town = context.Towns
.Include(x => x.Addresses)
.FirstOrDefault(x => x.Name == "Seattle");
var allAddressIds = town.Addresses.Select(x => x.AddressId).ToList();
var employees = context.Employees
.Where(x => x.AddressId.HasValue && allAddressIds.Contains(x.AddressId.Value))
.ToList();
foreach (var employee in employees)
{
employee.AddressId = null;
}
//context.SaveChanges();
foreach (var addressId in allAddressIds)
{
var address = context.Addresses.FirstOrDefault(x => x.AddressId == addressId);
context.Addresses.Remove(address);
}
context.Towns.Remove(town);
context.SaveChanges();
return($"{allAddressIds.Count} addresses in Seattle were deleted");
}
public static string AddNewAddressToEmployee(SoftUniContext context)
{
StringBuilder sb = new StringBuilder();
var address = new Address()
{
AddressText = "Vitoshka 15",
TownId = 4
};
context.Add(address);
var employee = context.Employees.FirstOrDefault(e => e.LastName == "Nakov");
employee.Address = address;
context.SaveChanges();
var employees = context.Employees
.Select(e => new
{
Address = e.Address.AddressText,
AddressId = e.Address.AddressId
})
.OrderByDescending(e => e.AddressId)
.Take(10)
.ToList();
foreach (var item in employees)
{
sb.AppendLine($"{item.Address}");
}
return(sb.ToString().Trim());
}
public static string AddNewAddressToEmployee(SoftUniContext context)
{
var result = new StringBuilder();
var address = new Address()
{
AddressText = "Vitoshka 15",
TownId = 4
};
var employee = context.Employees.Where(e => e.LastName == "Nakov").First();
employee.Address = address;
context.SaveChanges();
var addresses = context.Employees
.OrderByDescending(e => e.AddressId)
.Take(10)
.Select(e => e.Address.AddressText)
.ToList();
addresses.ForEach(a =>
result.AppendLine(a)
);
return(result.ToString().Trim());
}
public static string DeleteProjectById(SoftUniContext context)
{
var result = new StringBuilder();
var employeesProjects = context.EmployeesProjects
.Where(ep => ep.ProjectId == 2)
.ToList();
context.RemoveRange(employeesProjects);
var project = context.Projects
.Where(p => p.ProjectId == 2).FirstOrDefault();
context.Remove(project);
context.SaveChanges();
var projects = context.Projects
.Select(p => p.Name)
.Take(10)
.ToList();
projects.ForEach(p => result.AppendLine(p));
return(result.ToString().Trim());
}
public static string RemoveTown(SoftUniContext context)
{
var employees = context.Employees
.Where(e => e.Address.Town.Name == "Seattle").ToList();
employees.ForEach(e => e.AddressId = null);
var numOfAddresses = context.Addresses
.Where(a => a.Town.Name == "Seattle").Count();
var listOfAddresses = context.Addresses
.Where(a => a.Town.Name == "Seattle")
.ToList();
context.RemoveRange(listOfAddresses);
var town = context.Towns
.Where(t => t.Name == "Seattle").FirstOrDefault();
context.Remove(town);
context.SaveChanges();
var result = $"{numOfAddresses} addresses in Seattle were deleted";
return(result);
}
public static string IncreaseSalaries(SoftUniContext context)
{
var 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");
foreach (var employee in employees)
{
employee.Salary *= 1.12m;
}
context.SaveChanges();
var employeesUpdated = employees
.Select(e => new
{
e.FirstName,
e.LastName,
e.Salary
})
.OrderBy(e => e.FirstName)
.ThenBy(e => e.LastName)
.ToList();
employeesUpdated.ForEach(e =>
result.AppendLine($"{e.FirstName} {e.LastName} (${e.Salary:F2})")
);
return(result.ToString().Trim());
}
//Exercise 6
public static string AddNewAddressToEmployee(SoftUniContext context)
{
var address = new Address
{
AddressText = "Vitoshka 15",
TownId = 4
};
context.Addresses.Add(address); // can be skipped, EF will figure out to add the address to the addresses table
var nakov = context.Employees.FirstOrDefault(x => x.LastName == "Nakov");
nakov.Address = address;
context.SaveChanges();
var employeeAddresses = context.Employees
.OrderByDescending(x => x.AddressId)
.Select(a => a.Address.AddressText)
.Take(10)
.ToList();
StringBuilder sb = new StringBuilder();
foreach (var employeeAddress in employeeAddresses)
{
sb.AppendLine($"{employeeAddress}");
}
return(sb.ToString().TrimEnd());
}
//14. Delete Project by Id
public static string DeleteProjectById(SoftUniContext context)
{
StringBuilder sb = new StringBuilder();
Project project = context.Projects.Find(2);
var epCol = context.EmployeesProjects.Where(x => x.ProjectId == 2).ToList();
foreach (var item in epCol)
{
context.Remove(item);
}
context.Projects.Remove(project);
context.SaveChanges();
Project[] projects = context.Projects.Take(10).ToArray();
foreach (var item in projects)
{
sb.AppendLine(item.Name);
}
return(sb.ToString().TrimEnd());
}
//Problem 14
public static string DeleteProjectById(SoftUniContext db)
{
StringBuilder sb = new StringBuilder();
var project = db.Projects
.Where(x => x.ProjectId == 2)
.First();
var eps = db.EmployeesProjects
.Where(x => x.ProjectId == project.ProjectId);
foreach (var ep in eps)
{
db.EmployeesProjects.Remove(ep);
}
db.Projects.Remove(project);
db.SaveChanges();
var projects = db.Projects.Select(x => x.Name).Take(10);
foreach (var pName in projects)
{
sb.AppendLine(pName);
}
return(sb.ToString().TrimEnd());
}
//15. Remove Town
public static string RemoveTown(SoftUniContext context)
{
var townToDelete = context
.Towns
.First(t => t.Name == "Seattle");
IQueryable <Address> addressesToDelete =
context
.Addresses
.Where(a => a.TownId == townToDelete.TownId);
int addressesCount = addressesToDelete.Count();
IQueryable <Employee> employeesOnDeletedAddresses =
context
.Employees
.Where(e => addressesToDelete.Any(a => a.AddressId == e.AddressId));
foreach (var employee in employeesOnDeletedAddresses)
{
employee.AddressId = null;
}
foreach (var address in addressesToDelete)
{
context.Addresses.Remove(address);
}
context.Remove(townToDelete);
context.SaveChanges();
return($"{addressesCount} addresses in Seattle were deleted");
}
//Problem 06
public static string AddNewAddressToEmployee(SoftUniContext db)
{
StringBuilder sb = new StringBuilder();
Address address = new Address()
{
AddressText = "Vitoshka 15", TownId = 4,
};
db.Addresses.Add(address);
var employee = db.Employees.First(x => x.LastName == "Nakov");
employee.Address = address;
db.SaveChanges();
var addresses = db.Employees
.OrderByDescending(x => x.AddressId)
.Take(10)
.Select(x => x.Address)
.ToList();
foreach (var a in addresses)
{
sb.AppendLine(a.AddressText);
}
return(sb.ToString().TrimEnd());
}
//6. Adding a New Address and Updating Employee
public static string AddNewAddressToEmployee(SoftUniContext context)
{
var sb = new StringBuilder();
//local object
var newAddress = new Address
{
AddressText = "Vitoshka 15",
TownId = 4
};
//attack to db
var employeeNakov = context.Employees
.First(e => e.LastName == "Nakov");
//
context.Addresses.Add(newAddress); //can be omited - може да бъде пропуснат
employeeNakov.Address = newAddress;
context.SaveChanges();
var addresses = context.Employees
.OrderByDescending(e => e.AddressId)
.Take(10)
.Select(e => e.Address.AddressText)
.ToList();
foreach (var address in addresses)
{
sb.AppendLine(address);
}
return(sb.ToString().TrimEnd());
}
//PROBLEM - 6
public static string AddNewAddressToEmployee(SoftUniContext context)
{
var addresses = context.Addresses;
Address newAddress = new Address()
{
TownId = 4, AddressText = "Vitoshka 15"
};
addresses.Add(newAddress);
var employeeNakov = context.Employees
.Where(e => e.LastName == "Nakov")
.FirstOrDefault();
employeeNakov.Address = newAddress;
context.SaveChanges();
var employees = context.Employees
.OrderByDescending(e => e.AddressId)
.Take(10)
.Select(e => new { e.Address.AddressText })
.ToList();
StringBuilder sb = new StringBuilder();
foreach (var empl in employees)
{
sb.AppendLine(empl.AddressText);
}
return(sb.ToString().TrimEnd());
}
//----------Task 6 - Adding a New Address and Updating Employee----------
public static string AddNewAddressToEmployee(SoftUniContext context)
{
var employee = context
.Employees
.Where(e => e.LastName == "Nakov")
.FirstOrDefault();
var newAddress = new Address()
{
AddressText = "Vitoshka 15", TownId = 4
};
employee.Address = newAddress;
context.SaveChanges();
var addresses = context
.Employees
.OrderByDescending(a => a.AddressId)
.Take(10)
.Select(a => new
{
output = a.Address.AddressText
})
.ToList();
var sb = new StringBuilder();
foreach (var addr in addresses)
{
sb.AppendLine($"{addr.output}");
}
return(sb.ToString().Trim());
}
//PROBLEM - 12
public static string 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 s in employees)
{
s.Salary = s.Salary * 1.12M;
}
context.SaveChanges();
var updatedSalary = employees
.Select(e => new
{
e.FirstName,
e.LastName,
e.Salary
})
.OrderBy(e => e.FirstName)
.ThenBy(e => e.LastName)
.ToList();
StringBuilder sb = new StringBuilder();
foreach (var s in updatedSalary)
{
sb.AppendLine($"{s.FirstName} {s.LastName} (${s.Salary:f2})");
}
return(sb.ToString().TrimEnd());
}
//----------Task 15 - Remove Town----------
public static string RemoveTown(SoftUniContext context)
{
var townName = "Seattle";
var townToDelete = context
.Towns
.Where(t => t.Name == townName)
.FirstOrDefault();
var addresses = context
.Addresses
.Where(a => a.TownId == townToDelete.TownId)
.ToList();
foreach (var adr in addresses)
{
var employees = context
.Employees
.Where(e => e.AddressId == adr.AddressId)
.ToList();
foreach (var emp in employees)
{
emp.AddressId = null;
}
context.Addresses.Remove(adr);
}
context.Towns.Remove(townToDelete);
context.SaveChanges();
return($"{addresses.Count()} addresses in {townName} were deleted");
}
//PROBLEM - 14
public static string DeleteProjectById(SoftUniContext context)
{
var projectToDelete = context.Projects
.Where(p => p.ProjectId == 2)
.FirstOrDefault();
var mappingToDeleteList = context.EmployeesProjects
.Where(ep => ep.ProjectId == 2).ToArray();
context.EmployeesProjects.RemoveRange(mappingToDeleteList);
context.Projects.Remove(projectToDelete);
context.SaveChanges();
var projects = context.Projects
.Take(10)
.Select(p => p.Name)
.ToList();
StringBuilder sb = new StringBuilder();
foreach (var p in projects)
{
sb.AppendLine($"{p}");
}
return(sb.ToString().TrimEnd());
}
public static string IncreaseSalaries(SoftUniContext context)
{
var sb = new StringBuilder();
var departments = new string[]
{
"Engineering",
"Tool Design",
"Marketing",
"Information Services"
};
var employees = context.Employees.Where(x => departments.Contains(x.Department.Name)).OrderBy(e => e.FirstName)
.ThenBy(e => e.LastName)
.ToList();
foreach (var employee in employees)
{
employee.Salary *= 1.12M;
}
context.SaveChanges();
foreach (var employee in employees)
{
sb.AppendLine($"{employee.FirstName} {employee.LastName} (${employee.Salary:f2})");
}
return(sb.ToString().TrimEnd());
}
//PROBLEM - 15
public static string RemoveTown(SoftUniContext context)
{
var townToDelete = context.Towns
.Where(t => t.Name == "Seattle")
.FirstOrDefault();
var addressesIdToRemove = context.Addresses
.Where(a => a.TownId == townToDelete.TownId);
int addressesToDeleteCount = addressesIdToRemove.Count();
var employeesToDeleteAddress = context.Employees
.Where(e => addressesIdToRemove
.Any(a => a.AddressId == e.AddressId));
foreach (var e in employeesToDeleteAddress)
{
e.AddressId = null;
}
foreach (var address in addressesIdToRemove)
{
context.Addresses.Remove(address);
}
context.Towns.Remove(townToDelete);
context.SaveChanges();
return($"{addressesToDeleteCount} addresses in {townToDelete.Name} were deleted");
}
public static string AddNewAddressToEmployee(SoftUniContext context)
{
var nakov = context.Employees.FirstOrDefault(x => x.LastName == "Nakov");
nakov.Address = new Address()
{
AddressText = "Vitoshka 15",
TownId = 4
};
context.SaveChanges();
var addresses = context.Employees.Select(x => new
{
x.Address.AddressText,
x.Address.AddressId
}).OrderByDescending(x => x.AddressId).Take(10).ToList();
var sb = new StringBuilder();
foreach (var currentAddressaddress in addresses)
{
sb.AppendLine(
$"{currentAddressaddress.AddressText}");
}
return(sb.ToString().TrimEnd());
}
//12. Increase Salaries
public static string IncreaseSalaries(SoftUniContext context)
{
StringBuilder sb = new StringBuilder();
IQueryable <Employee> 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 e in employees)
{
e.Salary += e.Salary * 0.12m;
}
context.SaveChanges();
var displayedEmployees = employees
.Select(e => new
{
e.FirstName,
e.LastName,
e.Salary
})
.OrderBy(e => e.FirstName)
.ThenBy(e => e.LastName)
.ToArray();
foreach (var e in displayedEmployees)
{
sb.AppendLine($"{e.FirstName} {e.LastName} (${e.Salary:f2})");
}
return(sb.ToString().TrimEnd());
}
public static string DeleteProjectById(SoftUniContext context)
{
StringBuilder sb = new StringBuilder();
var projectToDelete = context
.Projects
.Where(p => p.ProjectId == 2)
.FirstOrDefault();
var employeesProjectsToRemove = context
.EmployeesProjects
.Where(ep => ep.ProjectId == projectToDelete.ProjectId);
foreach (var ep in employeesProjectsToRemove)
{
context.EmployeesProjects.Remove(ep);
}
context.Projects.Remove(projectToDelete);
context.SaveChanges();
var projectsToDisplay = context
.Projects
.Take(10)
.Select(p => p.Name);
foreach (var project in projectsToDisplay)
{
sb.AppendLine(project);
}
return(sb.ToString().TrimEnd());
}
public static string DeleteProjectById(SoftUniContext context)
{
StringBuilder sb = new StringBuilder();
var project = context
.Projects
.First(p => p.ProjectId == 2);
context
.EmployeesProjects
.ToList()
.ForEach(ep => context.EmployeesProjects.Remove(ep));
context.Projects.Remove(project);
context.SaveChanges();
var projectToPrint = context
.Projects
.Take(10)
.Select(p => new
{
p.Name
})
.ToList();
foreach (var p in projectToPrint)
{
sb.AppendLine(p.Name);
}
return(sb.ToString().TrimEnd());
}