public static void FindDepartmentsWithMoreThanXEmpleyees(int minAmount)
{
// 4. Find all departments with more than 5 employees.
// Order them by employee count (ascending).
// Select the department name, manager name and first name, last name,
// hire date and job title of every employee.
SoftUniEntities context = new SoftUniEntities();
using (context)
{
var departments =
from d in context.Departments
where d.Employees.Count > minAmount
orderby d.Employees.Count
select new
{
DepartmentName = d.Name,
Manager = (from m in context.Employees
where m.EmployeeID == d.ManagerID
select new { Name = m.FirstName + " " + m.LastName }).FirstOrDefault().Name,
EmployeeCount = d.Employees.Count
};
foreach (var department in departments)
{
Console.WriteLine("--{0} - Manager: {1}, Employees: {2}",
department.DepartmentName,
department.Manager,
department.EmployeeCount
);
}
}
}
public static void FindEmployeesManagersAndProjectByYear(int y1, int y2)
{
using (SoftUniEntities context = new SoftUniEntities())
{
// 1. Find all employees who have projects started in the time period 2001 - 2003 (inclusive).
// Select the project's name, start date, end date and manager name.
var proj = from p in context.Projects
where p.StartDate >= new DateTime(y1, 1, 1) && p.EndDate <= new DateTime(y2, 12, 31)
select new
{
ProjectName = p.Name,
Start = p.StartDate,
End = p.EndDate,
Employees =
(
from e in p.Employees
select new
{
e.FirstName,
e.LastName,
e.EmployeeID,
Manager =
(
from m in context.Employees
where m.EmployeeID == e.ManagerID
select new
{
m.FirstName,
m.LastName
}
)
}
)
};
foreach (var p in proj)
{
Console.WriteLine("{0}", p.ProjectName);
Console.WriteLine(" => Start: {0}", p.Start);
Console.WriteLine(" => End: {0}", p.End);
foreach (var e in p.Employees)
{
Console.WriteLine(" |||||================");
Console.WriteLine(" |||||=> Employee: {0} {1} {2}", e.FirstName, e.LastName, e.EmployeeID);
foreach (var m in e.Manager)
{
Console.WriteLine(" |||||=> Manager: {0} {1}", m.FirstName, m.LastName);
}
Console.WriteLine(" |||||================");
}
}
}
}
public static void FindAddresses()
{
// 2. Find all addresses, ordered by the number of employees who live there (descending),
// then by town name (ascending).
// Take only the first 10 addresses and select their address text, town name and employee count.
SoftUniEntities context = new SoftUniEntities();
using (context)
{
var addresses = context.Addresses.OrderByDescending(a => a.Employees.Count)
.ThenBy(a => a.Town.Name)
.Take(10)
.Select(a => new
{
Address = a.AddressText,
Count = a.Employees.Count,
TownName = a.Town.Name
});
foreach (var address in addresses)
{
Console.WriteLine("{0}, {2} - {1} employees", address.Address, address.Count, address.TownName);
}
}
}
public static void GetEmployeeByIdAndPrintInfo(int id)
{
////3. Get an employee by id (e.g. 147).
//// Select only his/her first name, last name, job title and projects (only their names).
//// The projects should be ordered by name (ascending).
SoftUniEntities context = new SoftUniEntities();
using (context)
{
var employee =
from e in context.Employees
where e.EmployeeID == id
select new
{
e.FirstName,
e.LastName,
e.JobTitle,
proj = e.Projects.OrderBy(p => p.Name).Select(p => p.Name)
};
foreach (var e in employee)
{
Console.WriteLine("{0} {1}: {2}, Projects: {3}",
e.FirstName, e.LastName, e.JobTitle,
string.Join(", ", e.proj.ToArray()));
}
}
}
