public static void PrintNamesWithLinqQuery(SoftUniEntities context)
{
var employees =
context.Employees.Where(emp => emp.Projects.Any(p => p.StartDate.Year == 2002))
.OrderBy(emp => emp.FirstName)
.Select(emp => emp.FirstName)
.Distinct();
//Console.WriteLine(employees.Count());
//foreach (var employee in employees)
//{
// Console.WriteLine(employee);
//}
}
public static void Main()
{
var context = new SoftUniEntities();
var totalCount = context.Employees.Count();
var sw = new Stopwatch();
sw.Start();
PrintNamesWithLinqQuery(context);
Console.WriteLine("Linq: {0}", sw.Elapsed);
sw.Restart();
PrintNamesWithNativeQuery(context);
Console.WriteLine("Native: {0}", sw.Elapsed);
}
public static void Main()
{
var context1 = new SoftUniEntities();
var context2 = new SoftUniEntities();
var employee1 = context1.Employees.Find(1);
employee1.FirstName = "HaHaHaHa";
var employee2 = context2.Employees.Find(1);
employee2.FirstName = "BrBrBrBr";
context1.SaveChanges();
context2.SaveChanges();
}
public static void Main()
{
var context = new SoftUniEntities();
var projects = context.GetProjectsByEmployee("Ruth", "Ellerbrock");
foreach (var project in projects)
{
Console.WriteLine(project.Name);
Console.WriteLine(project.Description);
Console.WriteLine(project.StartDate);
Console.WriteLine();
}
}
public static void PrintNamesWithNativeQuery(SoftUniEntities context)
{
var query = "SELECT e.FirstName FROM dbo.Employees e"
+ " JOIN dbo.EmployeesProjects ep ON e.EmployeeID = ep.EmployeeID"
+ " JOIN dbo.Projects p ON ep.ProjectID = p.ProjectID"
+ " WHERE DATEPART(yyyy, p.StartDate) = 2002"
+ " GROUP BY e.FirstName";
var employees = context.Database.SqlQuery<string>(query);
//Console.WriteLine(employees.Count());
//foreach (var employee in employees)
//{
// Console.WriteLine(employee);
//}
}
public static void Main()
{
var context = new SoftUniEntities();
// 01. 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 employees = context.Employees
.Where(emp => emp.Projects.Any(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003))
.Select(emp => new
{
FirstName = emp.FirstName,
LastName = emp.LastName,
Projects = emp.Projects
.Where(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003)
.Select(p => p.Name + ", start date: " + p.StartDate),
Manager = emp.Employee1.FirstName + " " + emp.Employee1.LastName
});
foreach (var employee in employees)
{
Console.WriteLine(employee.FirstName + " " + employee.LastName + ", Projects:");
Console.WriteLine("Manager: " + employee.Manager);
foreach (var project in employee.Projects)
{
Console.WriteLine(project);
}
}
Console.WriteLine();
// 02. 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.
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(address.Address + ", " + address.Town + " - " + address.EmployeeCount + " employees.");
}
Console.WriteLine();
// 03. 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).
var employeeById = context.Employees
.Where(e => e.EmployeeID == 147)
.Select(e => new
{
FirstName = e.FirstName,
LastName = e.LastName,
JobTitle = e.JobTitle,
Projects = e.Projects.OrderBy(p => p.Name).Select(p => p.Name)
})
.FirstOrDefault();
Console.WriteLine("First name:" + employeeById.FirstName);
Console.WriteLine("Last name:" + employeeById.LastName);
Console.WriteLine("Job Title:" + employeeById.JobTitle);
Console.WriteLine("Projects:");
Console.WriteLine(string.Join(", ", employeeById.Projects));
Console.WriteLine();
// 04. 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.
var departments =
context.Departments.Where(d => d.Employees.Count >= 5)
.OrderBy(d => d.Employees.Count)
.Select(d => new
{
departmentName = d.Name,
managerName = d.Employee.FirstName + " " + d.Employee.LastName,
employees = d.Employees.Select(e => new
{
firstName = e.FirstName,
lastName = e.LastName,
hireDate = e.HireDate,
jobTitle = e.JobTitle
})
});
foreach (var department in departments)
{
Console.WriteLine(
"--{0} - Manager: {1}, Employees: {2}",
department.departmentName,
department.managerName,
department.employees.Count());
//foreach (var employee in department.employees)
//{
// Console.WriteLine("Employee: " + employee.firstName + " " + employee.lastName + ", " +
// employee.hireDate + ", " + employee.jobTitle);
//}
}
}
