public static void AllDepartmentsWithMoreThan5Employee() { var db = new SoftUniEntities(); var departments = db.Departments.Where(d => d.Employees.Count > 5).OrderBy(d => d.Employees.Count).Select(d => new { DepartmentName = d.Name, MName = d.Employee.FirstName + " " + d.Employee.LastName, Employees = d.Employees.Select(e => new { e.FirstName, e.LastName, e.HireDate, e.JobTitle }) .ToList() }) .ToList(); foreach (var department in departments) { Console.WriteLine("---{0} - Manager: {1}, Employees: {2}", department.DepartmentName, department.MName, department.Employees.Count); } }
public static void DeleteEmployeeFromId(int id) { var db = new SoftUniEntities(); var employee = db.Employees.Find(id); db.Employees.Remove(employee); db.SaveChanges(); }
public static void UpdateFirstNameOfEmployee(int id, string firstName) { var db = new SoftUniEntities(); var employee = db.Employees.Find(id); employee.FirstName = firstName; db.SaveChanges(); }
public static void EmployeeByDepartmentAndManager(string departmentName, string managerFirstName, string managerLastName) { var db = new SoftUniEntities(); var manager = db.Employees.FirstOrDefault(e => e.FirstName == managerFirstName && e.LastName == managerLastName); var employeeByDepartmentAndManager = db.Employees.FirstOrDefault(e => e.Department.Name == departmentName || e.ManagerID == manager.EmployeeID); Console.WriteLine(employeeByDepartmentAndManager.FirstName + " " + employeeByDepartmentAndManager.LastName); }
public static void GetAnEmployeeById(int id) { var db = new SoftUniEntities(); var employeeById = db.Employees.Find(id); var empProjects = employeeById.Projects.OrderBy(p => p.Name).Select(p => p.Name); Console.WriteLine("Employee: {0} {1}, Job Title: {2}, Projects: {3}", employeeById.FirstName, employeeById.LastName, employeeById.JobTitle, string.Join(", ", empProjects)); }
public static void PrintNameWithNativeQuery(int projectStartDateYear) { var softUniEntities = new SoftUniEntities(); var query = "SELECT [e].[FirstName]" + "FROM Employees [e]" + "JOIN EmployeesProjects [ep]" + "ON [ep].[EmployeeID] = [e].[EmployeeID]" + "JOIN Projects [p]" + "ON [p].[ProjectID] = [ep].[ProjectID]" + "WHERE YEAR([p].[StartDate]) = '{0}'" + "GROUP BY [e].[FirstName]" + "ORDER BY [e].[FirstName]"; var employeeFirstNames = softUniEntities.Database.SqlQuery<string>(String.Format(query, projectStartDateYear)).ToList(); }
public static void AllAddresses() { var db = new SoftUniEntities(); var addresses = db.Addresses.OrderByDescending(a => a.Employees.Count).ThenBy(a => a.Town.Name).Select(e => new { Address = e.AddressText, TownName = e.Town.Name, EmployeeCount = e.Employees.Count }).Take(10) .ToList(); foreach (var address in addresses) { Console.WriteLine("{0}, {1} - {2} employees", address.Address, address.TownName, address.EmployeeCount); } }
public static void AllEmpWithProjects(int year) { var db = new SoftUniEntities(); var employees = db.Employees.Where(e => e.Projects.Any(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003)); foreach (var employee in employees) { Console.WriteLine("Manager name: " + employee.Employee1.FirstName + " " + employee.Employee1.LastName); foreach (var project in employee.Projects) { Console.WriteLine("project name: {0}, start date: {1}, end date: {2}", project.Name, project.StartDate.ToString("dd-MM-yyyy"), project.EndDate.ToString()); } Console.WriteLine(); } }
public static void InsertEmployee(string firstName, string lastName, string jobTitle, string department, DateTime hireDate, int salary) { var db = new SoftUniEntities(); var depName = db.Departments.First(d => d.Name == department.ToString()); var employee = new Employee() { FirstName = firstName, LastName = lastName, JobTitle = jobTitle, DepartmentID = depName.DepartmentID, HireDate = hireDate, Salary = salary }; db.Employees.Add(employee); db.SaveChanges(); }
public static void PrintNameWithLinqQuery(int projectStartDateYear) { var db = new SoftUniEntities(); var employees = db.Employees.Where(e => e.Projects.Any(p => p.StartDate.Year.ToString() == "2002")).Select(e => e.FirstName); }