public static void AllDepartmentsWithMoreThan5Employee()
{
var db = new SoftUniEntities();
var departments =
db.Departments.Where(d => d.Employees.Count > 5).OrderBy(d => d.Employees.Count).Select(d => new
{
DepartmentName = d.Name,
MName = d.Employee.FirstName + " " + d.Employee.LastName,
Employees = d.Employees.Select(e => new
{
e.FirstName,
e.LastName,
e.HireDate,
e.JobTitle
})
.ToList()
})
.ToList();
foreach (var department in departments)
{
Console.WriteLine("---{0} - Manager: {1}, Employees: {2}",
department.DepartmentName, department.MName, department.Employees.Count);
}
}
public static void DeleteEmployeeFromId(int id)
{
var db = new SoftUniEntities();
var employee = db.Employees.Find(id);
db.Employees.Remove(employee);
db.SaveChanges();
}
public static void UpdateFirstNameOfEmployee(int id, string firstName)
{
var db = new SoftUniEntities();
var employee = db.Employees.Find(id);
employee.FirstName = firstName;
db.SaveChanges();
}
public static void EmployeeByDepartmentAndManager(string departmentName, string managerFirstName,
string managerLastName)
{
var db = new SoftUniEntities();
var manager = db.Employees.FirstOrDefault(e => e.FirstName == managerFirstName && e.LastName == managerLastName);
var employeeByDepartmentAndManager =
db.Employees.FirstOrDefault(e => e.Department.Name == departmentName || e.ManagerID == manager.EmployeeID);
Console.WriteLine(employeeByDepartmentAndManager.FirstName + " " + employeeByDepartmentAndManager.LastName);
}
public static void GetAnEmployeeById(int id)
{
var db = new SoftUniEntities();
var employeeById = db.Employees.Find(id);
var empProjects = employeeById.Projects.OrderBy(p => p.Name).Select(p => p.Name);
Console.WriteLine("Employee: {0} {1}, Job Title: {2}, Projects: {3}",
employeeById.FirstName,
employeeById.LastName,
employeeById.JobTitle,
string.Join(", ", empProjects));
}
public static void PrintNameWithNativeQuery(int projectStartDateYear)
{
var softUniEntities = new SoftUniEntities();
var query = "SELECT [e].[FirstName]" +
"FROM Employees [e]" +
"JOIN EmployeesProjects [ep]" +
"ON [ep].[EmployeeID] = [e].[EmployeeID]" +
"JOIN Projects [p]" +
"ON [p].[ProjectID] = [ep].[ProjectID]" +
"WHERE YEAR([p].[StartDate]) = '{0}'" +
"GROUP BY [e].[FirstName]" +
"ORDER BY [e].[FirstName]";
var employeeFirstNames = softUniEntities.Database.SqlQuery<string>(String.Format(query, projectStartDateYear)).ToList();
}
public static void AllAddresses()
{
var db = new SoftUniEntities();
var addresses = db.Addresses.OrderByDescending(a => a.Employees.Count).ThenBy(a => a.Town.Name).Select(e => new
{
Address = e.AddressText,
TownName = e.Town.Name,
EmployeeCount = e.Employees.Count
}).Take(10)
.ToList();
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2} employees", address.Address, address.TownName, address.EmployeeCount);
}
}
public static void AllEmpWithProjects(int year)
{
var db = new SoftUniEntities();
var employees = db.Employees.Where(e => e.Projects.Any(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003));
foreach (var employee in employees)
{
Console.WriteLine("Manager name: " + employee.Employee1.FirstName + " " + employee.Employee1.LastName);
foreach (var project in employee.Projects)
{
Console.WriteLine("project name: {0}, start date: {1}, end date: {2}", project.Name,
project.StartDate.ToString("dd-MM-yyyy"), project.EndDate.ToString());
}
Console.WriteLine();
}
}
public static void InsertEmployee(string firstName, string lastName, string jobTitle, string department,
DateTime hireDate, int salary)
{
var db = new SoftUniEntities();
var depName = db.Departments.First(d => d.Name == department.ToString());
var employee = new Employee()
{
FirstName = firstName,
LastName = lastName,
JobTitle = jobTitle,
DepartmentID = depName.DepartmentID,
HireDate = hireDate,
Salary = salary
};
db.Employees.Add(employee);
db.SaveChanges();
}
public static void PrintNameWithLinqQuery(int projectStartDateYear)
{
var db = new SoftUniEntities();
var employees = db.Employees.Where(e => e.Projects.Any(p => p.StartDate.Year.ToString() == "2002")).Select(e => e.FirstName);
}