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

            }
        }
Beispiel #2
0
        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);
            }
        }