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