private static void PrintNameWithLinq(SoftuniContext context) { var employeesNames = context.Employees .Where(employee => employee.Projects.Count(project => project.StartDate.Year == 2002) != 0) .Select(employee => employee.FirstName).GroupBy(s => s); foreach (var s in employeesNames) { } }
private static void PrintNamesWithNativeQuery(SoftuniContext context) { string query = "SELECT em.FirstName FROM Employees em " + "JOIN EmployeesProjects emproj " + "ON emproj.EmployeeId = em.EmployeeId " + "JOIN Projects proj " + "ON emproj.ProjectId = proj.ProjectId AND YEAR(proj.StartDate) = 2002 " + "GROUP BY em.FirstName"; var result = context.Database.SqlQuery <string>(query); foreach (var f in result) { } }
static void Main(string[] args) { var context = new SoftuniContext(); StringBuilder content = new StringBuilder(); #region //Employees full information //IEnumerable<Employee> employees = context.Employees; //foreach (Employee employee in employees) //{ // content.AppendLine($"{employee.FirstName} {employee.LastName} {employee.MiddleName} {employee.JobTitle} {employee.Salary}"); //} #endregion #region //Employees with Salary Over 50 000 //var employees = context.Employees.Where(x => x.Salary > 50000).Select(e => e.FirstName); //foreach (string employee in employees) //{ // content.AppendLine($"{employee}"); //} #endregion #region//Employees from Seattle //var employees = context.Employees //.Where(e=>e.Department.Name=="Research and Development") //.OrderBy(e=>e.Salary).ThenByDescending(e=>e.FirstName); //foreach (var employee in employees) //{ // content.AppendLine($"{employee.FirstName} {employee.LastName} from {employee.Department.Name} - ${employee.Salary:f2}"); //} #endregion #region//Adding a New Address and Updating Employee //var adress = new Address() //{ // AddressText = "Vitoshka 15", // TownID = 4 //}; //var employee = context.Employees //.First(e => e.LastName == "Nakov"); //employee.Address = adress; //context.SaveChanges(); //var employeeAddresses = context.Employees // .OrderByDescending(e => e.Address.AddressID) // .Take(10) // .Select(e => e.Address.AddressText); //foreach (string employeeAddress in employeeAddresses) //{ // content.AppendLine(employeeAddress); //} #endregion #region//Find Employees in Period //var employees = context.Employees //.Where(emp => emp.Projects // .Count(p => // p.StartDate.Year >= 2001 && // p.StartDate.Year <= 2003) > 0) // .Take(30); //foreach (var employee in employees) //{ // content.AppendLine($"{employee.FirstName} {employee.LastName}" + // $" {employee.Manager.FirstName}"); // foreach (Project project in employee.Projects) // { // content.AppendLine($"--{project.Name} {project.StartDate.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture)}" + // $" {project.EndDate?.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture)}"); // } //} #endregion #region//Adresses By Town Name //var adresses = context.Addresses // .OrderByDescending(adress => adress.Employees.Count) // .ThenBy(adress => adress.Town.Name) // .Take(10); //foreach (var adress in adresses) //{ // content.AppendLine($"{adress.AddressText}, {adress.Town.Name} - {adress.Employees.Count} employees"); //} #endregion #region//Employee with id 147 //var employee147 = context.Employees.Find(147); //content.AppendLine($"{employee147.FirstName} {employee147.LastName}"+ // $" {employee147.JobTitle}"); //foreach (var project in employee147.Projects.OrderBy(p=>p.Name)) //{ // content.AppendLine(project.Name); //} #endregion #region//Departments with more than 5 employees //var departments = context.Departments //.Where(d => d.Employees.Count > 5) //.OrderBy(d=>d.Employees.Count); //foreach (var dep in departments) //{ // content.AppendLine($"{dep.Name} {dep.Employee.FirstName}"); // foreach (var empl in dep.Employees) // { // content.AppendLine($"{empl.FirstName} {empl.LastName} {empl.JobTitle}"); // } //} #endregion #region//Find Latest 10 Projects //var projects = context.Projects.OrderByDescending(p => p.StartDate).Take(10); //foreach (var proj in projects.OrderBy(p => p.Name)) //{ // content.AppendLine($"{proj.Name} {proj.Description}"+ // $" {proj.StartDate.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture)}"); //} #endregion #region//Increase Salaries //var employees = context.Employees.Where(e => e.Department.Name == "Engineering" // || e.Department.Name == "Tool Design" // || e.Department.Name == "Marketing" // || e.Department.Name == "Information Services"); //foreach (var empl in employees) //{ // empl.Salary *= 1.12m; // content.AppendLine($"{empl.FirstName} {empl.LastName} (${empl.Salary})"); //} //context.SaveChanges(); #endregion #region//Find Employees by First Name Starting with "SA" //var employees = context.Employees.Where(e => e.FirstName.StartsWith("SA")); //foreach (var empl in employees) //{ // content.AppendLine($"{empl.FirstName} {empl.LastName} - {empl.JobTitle} - (${empl.Salary})"); //} #endregion #region//Delete Project by ID =2 //var projectToDelete = context.Projects.Find(2); //var employees = context.Employees; //foreach (var empl in employees) //{ // empl.Projects.Remove(projectToDelete); //} //context.SaveChanges(); //var projects = context.Projects.Take(10).Select(project1 => project1.Name); //foreach (var proj in projects) //{ // content.AppendLine(proj); //} #endregion #region//Remove Towns with name as an input //Console.Write("Enter town to delete: "); //string townName = Console.ReadLine(); //var townToDelete = context.Towns.FirstOrDefault(n => n.Name.Equals(townName)); //int count = 0; //foreach (var adress in townToDelete.Addresses.ToArray()) //{ // foreach (var employee in adress.Employees.ToArray()) // { // employee.Address = null; // } // context.Addresses.Remove(adress); // count++; //} //context.Towns.Remove(townToDelete); //context.SaveChanges(); //int counterCopy = townToDelete.Addresses.ToArray().Length; //if (count != 0) //{ // if (count == 1) // { // content.AppendLine($"{count} adress in {townToDelete.Name} was deleted"); // } // else // { // content.AppendLine($"{count} adresses in {townToDelete.Name} were deleted"); // } //} #endregion #region//Native SQL Query context.Addresses.Count(); var timer = new Stopwatch(); timer.Start(); PrintNameWithLinq(context); timer.Stop(); content.AppendLine($"Linq: {timer.Elapsed}"); timer.Reset(); timer.Start(); PrintNamesWithNativeQuery(context); timer.Stop(); content.AppendLine($"Native: {timer.Elapsed}"); timer.Reset(); #endregion Console.WriteLine(content); }