private static void Main()
{
//Problem 1
var context = new SoftUniEntities();
//Problem 2
TestClass.TestOperations();
//Problem 3.1
var employees = context.Employees.Where(e => e.Projects
.Any(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003))
.Select(e => new
{
e.FirstName,
e.LastName,
Projects = e.Projects
.Where(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003)
.Select(p => new
{
p.Name,
p.StartDate,
p.EndDate
}),
ManagerName = e.Employee1.FirstName + " " + e.Employee1.LastName
});
foreach (var employee in employees)
{
Console.WriteLine("Name: {0} {1}", employee.FirstName, employee.LastName);
Console.WriteLine("\tManager: {0}", employee.ManagerName);
Console.WriteLine("\tProjects:");
foreach (var project in employee.Projects)
{
Console.WriteLine("\t\tProject Name: {0}\n\t\tStart Date:" +
" {1}\n\t\tEnd Date{2}\n",
project.Name,
project.StartDate,
project.EndDate);
}
}
//Problem 3.2
var addresses = context.Addresses.Select(a => new
{
Address = a.AddressText,
a.Town.Name,
NumberOfEmployees = a.Employees.Count()
}).OrderByDescending(a => a.NumberOfEmployees).ThenBy(a => a.Name).Take(10);
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2}",
address.Address,
address.Name,
address.NumberOfEmployees);
}
//Problem 3.3
var emp = context.Employees.Where(e => e.EmployeeID == 1).Select(e => new
{
e.FirstName,
e.LastName,
e.JobTitle,
Projects = e.Projects
});
foreach (var e in emp)
{
string prj = "";
foreach (var proj in e.Projects)
{
prj += proj.Name + "\n ";
}
Console.WriteLine("First Name: {0}\nLast Name: {1}\nJob Title: {2}\nProjects: {3}",
e.FirstName,
e.LastName,
e.JobTitle,
prj);
}
////Problem 3.4
var deps = context.Departments.Where(d => d.Employees.Count > 5).Select(d => new
{
d.Name,
Manager = context.Employees.Where(e => e.EmployeeID == d.ManagerID).Select(e => new { e.LastName }),
Employees = context.Employees.Where(em => em.DepartmentID == d.DepartmentID).Select(em => new
{
em.FirstName,
em.LastName,
em.HireDate,
em.JobTitle
})
}).OrderBy(d => d.Employees.Count());
foreach (var dep in deps)
{
string Man = "";
foreach (var m in dep.Manager)
{
Man = m.LastName;
}
Console.WriteLine("-- {0} - Manager: {1}, Employees: {2}",
dep.Name,
Man,
dep.Employees.ToList().Count()
);
}
// Problem 4.1
string NativeQuery =
"SELECT e.FirstName FROM employees e " +
"INNER JOIN EmployeesProjects ep ON e.EmployeeID = ep.EmployeeID " +
"INNER JOIN Projects p ON ep.ProjectID = p.ProjectID";
var names = context.Database.SqlQuery<string>(NativeQuery).ToList();
foreach (var name in names)
{
Console.WriteLine(name);
}
//Problem 4.2
//Find all employees who have projects with start date in the year 2002. Select only their first name.
var EmpPr = context.Employees.Where(e => e.Projects
.Any(p => p.StartDate.Year == 2002))
.Select(e => e.FirstName);
foreach (var empl in EmpPr)
{
Console.WriteLine(empl);
}
//Problem 5
var context2 = new SoftUniEntities();
var Cont1Change = context.Employees.Find(1).FirstName = "gosho2";
var Cont2Change = context2.Employees.Find(1).FirstName = "ivan3";
// When the concurency control is changed to fixed it goes boom.
context.SaveChanges();
// context2.SaveChanges();
//Problem 6
var ProjectsList = context.usp_Projects4Employee("Roberto", "Tamburello");
foreach (var project in ProjectsList)
{
Console.WriteLine("Name: {0}\nDescription: {1}\nStarting Date: {2}\n",
project.Name,
project.Description,
project.StartDate);
}
}
private static void Main()
{
//Problem 1
var context = new SoftUniEntities();
//Problem 2
TestClass.TestOperations();
//Problem 3.1
var employees = context.Employees.Where(e => e.Projects
.Any(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003))
.Select(e => new
{
e.FirstName,
e.LastName,
Projects = e.Projects
.Where(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003)
.Select(p => new
{
p.Name,
p.StartDate,
p.EndDate
}),
ManagerName = e.Employee1.FirstName + " " + e.Employee1.LastName
});
foreach (var employee in employees)
{
Console.WriteLine("Name: {0} {1}", employee.FirstName, employee.LastName);
Console.WriteLine("\tManager: {0}", employee.ManagerName);
Console.WriteLine("\tProjects:");
foreach (var project in employee.Projects)
{
Console.WriteLine("\t\tProject Name: {0}\n\t\tStart Date:" +
" {1}\n\t\tEnd Date{2}\n",
project.Name,
project.StartDate,
project.EndDate);
}
}
//Problem 3.2
var addresses = context.Addresses.Select(a => new
{
Address = a.AddressText,
a.Town.Name,
NumberOfEmployees = a.Employees.Count()
}).OrderByDescending(a => a.NumberOfEmployees).ThenBy(a => a.Name).Take(10);
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2}",
address.Address,
address.Name,
address.NumberOfEmployees);
}
//Problem 3.3
var emp = context.Employees.Where(e => e.EmployeeID == 1).Select(e => new
{
e.FirstName,
e.LastName,
e.JobTitle,
Projects = e.Projects
});
foreach (var e in emp)
{
string prj = "";
foreach (var proj in e.Projects)
{
prj += proj.Name + "\n ";
}
Console.WriteLine("First Name: {0}\nLast Name: {1}\nJob Title: {2}\nProjects: {3}",
e.FirstName,
e.LastName,
e.JobTitle,
prj);
}
////Problem 3.4
var deps = context.Departments.Where(d => d.Employees.Count > 5).Select(d => new
{
d.Name,
Manager = context.Employees.Where(e => e.EmployeeID == d.ManagerID).Select(e => new { e.LastName }),
Employees = context.Employees.Where(em => em.DepartmentID == d.DepartmentID).Select(em => new
{
em.FirstName,
em.LastName,
em.HireDate,
em.JobTitle
})
}).OrderBy(d => d.Employees.Count());
foreach (var dep in deps)
{
string Man = "";
foreach (var m in dep.Manager)
{
Man = m.LastName;
}
Console.WriteLine("-- {0} - Manager: {1}, Employees: {2}",
dep.Name,
Man,
dep.Employees.ToList().Count()
);
}
// Problem 4.1
string NativeQuery =
"SELECT e.FirstName FROM employees e " +
"INNER JOIN EmployeesProjects ep ON e.EmployeeID = ep.EmployeeID " +
"INNER JOIN Projects p ON ep.ProjectID = p.ProjectID";
var names = context.Database.SqlQuery <string>(NativeQuery).ToList();
foreach (var name in names)
{
Console.WriteLine(name);
}
//Problem 4.2
//Find all employees who have projects with start date in the year 2002. Select only their first name.
var EmpPr = context.Employees.Where(e => e.Projects
.Any(p => p.StartDate.Year == 2002))
.Select(e => e.FirstName);
foreach (var empl in EmpPr)
{
Console.WriteLine(empl);
}
//Problem 5
var context2 = new SoftUniEntities();
var Cont1Change = context.Employees.Find(1).FirstName = "gosho2";
var Cont2Change = context2.Employees.Find(1).FirstName = "ivan3";
// When the concurency control is changed to fixed it goes boom.
context.SaveChanges();
// context2.SaveChanges();
//Problem 6
var ProjectsList = context.usp_Projects4Employee("Roberto", "Tamburello");
foreach (var project in ProjectsList)
{
Console.WriteLine("Name: {0}\nDescription: {1}\nStarting Date: {2}\n",
project.Name,
project.Description,
project.StartDate);
}
}