private static void PrintNumberOfEmployeesLinqQuery(SoftUniEntities context)
{
var projects = context.Employees
.Where(e => e.Projects
.Any(
p => p.StartDate.Year >= 2001 &&
p.StartDate.Year <= 2003
))
.OrderBy(e => e.FirstName)
.Select(e => new
{
ManagerName = e.Manager.LastName,
Projects = e.Projects
.Where(p => p.StartDate.Year >= 2001 &&
p.StartDate.Year <= 2003)
})
.ToList();
Console.WriteLine(projects.Count());
}
private static void PrintNumberOfEmployeesNativeQuery(SoftUniEntities context)
{
var employees = context.Employees.SqlQuery(@"
SELECT *
FROM Employees e
JOIN EmployeesProjects ep
ON e.EmployeeID = ep.EmployeeID
JOIN Projects p
ON p.ProjectID = ep.ProjectID
WHERE YEAR(p.StartDate) BETWEEN '2001' AND '2003'")
.ToList();
Console.WriteLine(employees.Count);
}
static void Main(string[] args)
{
// Problem 2
//var employee = DAO.FindByKey(10);
//Console.WriteLine(employee.FirstName);
//DAO.Modify(employee, "peshka");
//Console.WriteLine(employee.FirstName);
//employee.FirstName = "PESHKA";
//DAO.Add(employee);
// Problem 3
// task 1
//var context = new SoftUniEntities();
//var projects = context.Employees
// .Where(e => e.Projects
// .Any(
// p => p.StartDate.Year >= 2001 &&
// p.StartDate.Year <= 2003
// ))
// .OrderBy(e => e.FirstName)
// .Select(e => new
// {
// ManagerName = e.Manager.LastName,
// Projects = e.Projects
// .Where(p => p.StartDate.Year >= 2001 &&
// p.StartDate.Year <= 2003)
// })
// .Take(5)
// .ToList();
//foreach (var project in projects)
//{
// Console.WriteLine("Manager: {0} {1}", project.ManagerName);
// foreach (var pr in project.Projects)
// {
// Console.WriteLine("Name: {0}, Start: {1}, End: {2}",
// pr.Name,
// pr.StartDate,
// pr.EndDate
// );
// }
//}
// task 2
// addresses with employees' count
//var context = new SoftUniEntities();
//var addresses = context.Addresses
// .OrderByDescending(a => a.Employees.Count())
// .ThenBy(a => a.Town.Name)
// .Take(10)
// .Select(a => new
// {
// Address = a.AddressText,
// Town = a.Town.Name,
// EmployeeCount = a.Employees.Count()
// });
//foreach (var address in addresses)
//{
// Console.WriteLine("{0}, {1} - {2} employees",
// address.Address,
// address.Town,
// address.EmployeeCount
// );
//}
// task 3
//var context = new SoftUniEntities();
//var employee = context.Employees.Find(147);
//var projects = employee.Projects
// .OrderBy(p => p.Name);
//foreach (var pr in projects)
//{
// Console.WriteLine("{0} {1} ({2}) - {3}",
// employee.FirstName,
// employee.LastName,
// employee.JobTitle,
// pr.Name);
//}
// task 4
//var context = new SoftUniEntities();
//var departments = context.Departments
// .Where(d => d.Employees.Count() > 5)
// .OrderBy(d => d.Employees.Count())
// .Select(d => new
// {
// DepartmentName = d.Name,
// ManagerName = d.Manager.LastName,
// Employees = d.Employees
// .Select(e => new
// {
// e.FirstName,
// e.LastName,
// e.HireDate,
// e.JobTitle
// })
// });
//Console.WriteLine("Count of departments: {0}", departments.Count());
//Console.WriteLine();
//foreach (var dep in departments)
//{
// Console.WriteLine("--{0} - Manager: {1}, Employees: {2}",
// dep.DepartmentName,
// dep.ManagerName,
// dep.Employees.Count()
// );
// foreach (var empl in dep.Employees)
// {
// Console.WriteLine("{0} {1}, {2} - {3}",
// empl.FirstName,
// empl.LastName,
// empl.HireDate,
// empl.JobTitle);
// }
//}
// Problem 4
//var context = new SoftUniEntities();
//var stopWatch = new Stopwatch();
//stopWatch.Start();
//PrintNumberOfEmployeesNativeQuery(context);
//Console.WriteLine(stopWatch.Elapsed);
//stopWatch.Restart();
//PrintNumberOfEmployeesLinqQuery(context);
//Console.WriteLine(stopWatch.Elapsed);
//stopWatch.Stop();
// Problem 5
var context = new SoftUniEntities();
var projByEmployee = context.usp_ProjectsByEmployee("Roberto", "Tamburello");
foreach (var project in projByEmployee)
{
Console.WriteLine(project);
}
//Console.WriteLine(string.Join(", ", employee));
}
