static void Main()
{
//Problem_02:
// 1. Inserts an employee
//2. Prints his/her primary key generated by the DB
//3. Changes the employee first name and saves it to the database
//4. Deletes an employee
Employee employee = new Employee();
employee.FirstName = "Pesho";
employee.LastName = "Peshov";
employee.JobTitle = "Marketing Manager";
employee.DepartmentID = 5;
employee.ManagerID = 10;
employee.HireDate = DateTime.Now;
employee.Salary = 5000m;
employee.AddressID = 100;
DAO.Add(employee);
int employeeId = employee.EmployeeID;
Console.WriteLine(employeeId);
employee.FirstName = "Gosho";
DAO.Modify(employee);
Console.WriteLine(DAO.FindByKey(employeeId).FirstName);
Console.ReadLine();
DAO.Delete(employee);
//Console.WriteLine(DAO.FindByKey(employeeId).FirstName);
//Problem03_Database Search Queries
//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 context = new SoftUniEntities();
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.Managers.FirstName + " " + e.Managers.LastName
});
foreach (var emp in employees)
{
Console.WriteLine("Name: {0} {1}", emp.FirstName, emp.LastName);
Console.WriteLine("\tManager: {0}", emp.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);
}
Console.WriteLine();
}
//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.
var addresses = context.Addresses
.OrderByDescending(a => a.Employees.Count)
.Take(10)
.Select(a => new
{
a.AddressText,
Town = a.Town.Name,
Employees = a.Employees.Count
});
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2} employees",
address.AddressText,
address.Town,
address.Employees);
}
//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).
var employeeSearched = context.Employees
.Where(e => e.EmployeeID == 147)
.Select(e => new
{
e.FirstName,
e.LastName,
e.JobTitle,
Projects = e.Projects.Select(p => p.Name).OrderBy(p => p)
})
.FirstOrDefault();
Console.WriteLine("{0} {1} - {2}\nProjects:",
employeeSearched.FirstName,
employeeSearched.LastName,
employeeSearched.JobTitle);
foreach (var project in employeeSearched.Projects)
{
Console.WriteLine("\t" + project);
}
//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.
var departments = context.Departments
.Where(d => d.Employees.Count > 5)
.OrderBy(d => d.Employees.Count)
.Select(d => new
{
DepartmentName = d.Name,
ManagerName = d.Managers.FirstName
+ " "
+ d.Managers.LastName,
Employees = d.Employees.Select(e => new
{
e.FirstName,
e.LastName,
e.HireDate,
e.JobTitle
}),
EmployeesCount = d.Employees.Count
});
foreach (var department in departments)
{
Console.WriteLine("Department name: {0}, employess in department - {1}",
department.DepartmentName, department.EmployeesCount);
Console.WriteLine("Manager: {0}\n\nEmployees:", department.ManagerName);
foreach (var emp in department.Employees)
{
Console.WriteLine("\tName: {0} {0}", emp.FirstName, emp.LastName);
Console.WriteLine("\tHire Date: " + emp.HireDate);
Console.WriteLine("\tJob Title: " + emp.JobTitle);
Console.WriteLine();
}
Console.WriteLine();
}
//Problem 4. Native SQL Query
var stopWatch = new Stopwatch();
var employeesLINQ = context.Employees
.Where(e => e.Projects.Any(p => p.StartDate.Year == 2002))
.Select(e => e.FirstName);
Console.WriteLine("LINQ: {0}", stopWatch.Elapsed);
stopWatch.Restart();
var employeesSQL = context.Database.SqlQuery <string>("SELECT Name FROM" +
"Employees e JOIN EmployeesProjects ep ON e.EmployeeID = ep.EmployeeID" +
"JOIN Projects p ON ep.ProjectID = p.ProjectID" +
"WHERE YEAR(p.StartDate) = 2002");
Console.WriteLine("Native: {0}", stopWatch.Elapsed);
//It is strange but LINQ took less time.
//Problem 5. Concurrent Database Changes with EF
var context1 = new SoftUniEntities();
var context2 = new SoftUniEntities();
var employee1 = context1.Employees.Find(147);
var employee2 = context2.Employees.Find(147);
employee1.FirstName = "change1";
employee2.FirstName = "change2";
context1.SaveChanges();
try
{
context2.SaveChanges();
}
catch (Exception)
{
Console.WriteLine("An exception occured");;
}
employee = context.Employees.Find(147);
Console.WriteLine(employee.FirstName);
//Without [Concurrency Mode] the second change takes place.
employee1 = context1.Employees.Find(147);
employee2 = context2.Employees.Find(147);
employee1.FirstName = "update3";
employee2.FirstName = "update2";
//context1.SaveChanges();
try
{
context1.SaveChanges();
}
catch (Exception)
{
Console.WriteLine("An exception occured");;
}
try
{
context2.SaveChanges();
}
catch (Exception)
{
Console.WriteLine("An exception occured");;
}
employee = context.Employees.Find(147);
Console.WriteLine(employee.FirstName);
//When concurrency mode is on, the first change gets valid and the application throws an exception
//when we try to save the second change
var projects = context.usp_FindProjectsByEmployeesName("Laura", "Norman");
foreach (var project in projects)
{
Console.WriteLine("{0} - {1}, {2}\n", project.Name, project.Description, project.StartDate);
}
Console.ReadLine();
}
public static void Add(Employee employee)
{
context.Employees.Add(employee);
context.SaveChanges();
}