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);
        }