//Getting Employee's three names and job title public static string GetEmployeeFullNames(SoftuniContext context) { StringBuilder sb = new StringBuilder(); var employees = context.Employees.OrderBy(employee => employee.EmployeeId) .Select(employee => new { FirstName = employee.FirstName, MiddleName = employee.MiddleName, LastName = employee.LastName, JobTitle = employee.JobTitle, EmployeeSalary = employee.Salary } ).ToArray(); foreach (var employee in employees) { sb.Append("Name: " + employee.FirstName + " " + employee.MiddleName + " " + employee.LastName + " Job title: " + employee.JobTitle + " Salary: " + String.Format("{0:0.00}", employee.EmployeeSalary) + Environment.NewLine); } return(sb.ToString()); }
public static string DepartmentsWithMoreThan5Employees(SoftuniContext dbContext) { var departmentsWithMoreThan5Empl = dbContext.Departments.Where(department => department.Employees.Count > 5) .OrderBy(department => department.Employees.Count) .ThenBy(department => department.Name) .Select(dep => new { DepartmentName = dep.Name, DepartmentManagerName = dep.Manager.FirstName + " " + dep.Manager.LastName, EmployeesInDepartment = dep.Employees.ToArray() }).ToArray(); StringBuilder sb = new StringBuilder(); foreach (var item in departmentsWithMoreThan5Empl) { sb.Append("Department information: " + Environment.NewLine); sb.Append("Department name: " + item.DepartmentName + " Manager: " + item.DepartmentManagerName + Environment.NewLine); sb.Append("Employees: " + Environment.NewLine); foreach (var employee in item.EmployeesInDepartment) { sb.Append(employee.FirstName + " " + employee.LastName + " " + employee.JobTitle + Environment.NewLine); } sb.Append("-------------------------------------------------" + Environment.NewLine); } return(sb.ToString()); }
public static int RemoveTown(SoftuniContext dbContext) { //first set the addresses of the employees living in seattle to null var employeesLivingInSeattle = dbContext.Employees.Where(employee => employee.Address.Town.Name.Equals("Seattle")); foreach (var employeeInSeattle in employeesLivingInSeattle) { employeeInSeattle.AddressId = null; } dbContext.SaveChanges(); //Remove the addresses which are located in seattle var addressesInSeattle = dbContext.Addresses.Where(address => address.TownId == 4); int addressDeleteCount = addressesInSeattle.Count(); foreach (var addressToRemove in addressesInSeattle) { dbContext.Remove(addressToRemove); } dbContext.SaveChanges(); var townToRemove = dbContext.Towns.FirstOrDefault(town => town.TownId == 4); dbContext.Remove(townToRemove); dbContext.SaveChanges(); return(addressDeleteCount); }
public static string IncreaseSalaries(SoftuniContext dbContext) { var employeesWhoseSalariesWillIncrease = dbContext.Employees .Where(empl => empl.Department.Name.Equals("Engineering") || empl.Department.Name.Equals("Tool Design") || empl.Department.Name.Equals("Marketing") || empl.Department.Name.Equals("Information Services")) .OrderByDescending(empl => empl.FirstName) .ThenBy(empl => empl.LastName); foreach (var item in employeesWhoseSalariesWillIncrease) { item.Salary = IncreaseSalaryBy12Procent(item.Salary); } dbContext.SaveChanges(); StringBuilder sb = new StringBuilder(); foreach (var item in employeesWhoseSalariesWillIncrease) { sb.Append(item.FirstName + " " + item.MiddleName + " " + item.LastName + " Salary: " + item.Salary + Environment.NewLine); } return(sb.ToString()); }
//Getting all employees from research and development department public static string EmployeesFromResearchAndDevelopment(SoftuniContext dbContext) { //finding the departmentID of Research and Development int researchAndDevelopemntID = dbContext.Departments.Where(department => department.Name.Equals("Research and Development")) .FirstOrDefault().DepartmentId; //getting the employees var employeesFromResearchAndDevelopment = dbContext.Employees.Where(employee => employee.DepartmentId == researchAndDevelopemntID) .OrderBy(employee => employee.Salary) .ThenByDescending(employee => employee.FirstName) .Select(employee => new { FirstNameOfEmployee = employee.FirstName, MiddleNameOfEmployee = employee.MiddleName, LastNameOfRmployee = employee.LastName, DepartmentName = "Research and Development", SalaryOfEmployee = employee.Salary } ).ToArray(); StringBuilder sb = new StringBuilder(); //appendint the employees data into the stringbuilder foreach (var employee in employeesFromResearchAndDevelopment) { sb.Append("Name: " + employee.FirstNameOfEmployee + " " + employee.MiddleNameOfEmployee + " " + employee.LastNameOfRmployee + " Department Name: " + employee.DepartmentName + " Salary: " + String.Format("{0:0.00}", employee.SalaryOfEmployee) + Environment.NewLine); } return(sb.ToString()); }
private static void Main(string[] args) { //Each problem is solved in different class - uncomment the class to start executing the solution for given problem with CTRL+F5: var gringottsContext = new GringottsContext(); //FirstLetter.GetFirstLetter(gringottsContext); var context = new SoftuniContext(); //EmployeesFullInformation.GetEmployeesFullInformation(context); //EmployeesWithSalaryOver5000.GetEmployeesWithSalariesOver5000(context); //EmployeesFromSeattle.GetEmployeesFromSeattle(context); //AddNewAddressAndUpdateEmployee.AddingNewAddressAndUpdateEmployee(context); //FindEmployeesInPeriod.FindingEmployeesInPeriod(context); //the result on the console depends on the regional options in the control panel, because //the project.EndDate can be Null and doesn't support .ToString(); //for correct result in Judge the regional options for "Date formats - Short date" should be "M/d/yyyy"; //AddressesByTownName.GetAddressesByTownName(context); //EmployeeWithId147.GetAddressesByTownName(context); //DepartmentsWithMoreThanFiveEmployees.GetDepartmentsWithMoreThanFiveEmployees(context); //FindLatestTenProjects.FindingLatestTenProjects(context); //IncreaseSalaries.IncreaseSalariesMethod(context); //FindEmployeesByFirstNameStartingWith.FindEmployeesByFirstName(context); //DeleteProjectById.DeleteProjectWithId(context); //RemoveTowns.RemoveTown(context); //NativeSQLQuerry.UseNativeSQLQuerry(context); }
//EX17 public static void CallAStoredProcedure() { var db = new SoftuniContext(); string[] names = Console.ReadLine().Split(' ').ToArray(); SqlParameter firstNameParameter = new SqlParameter("@FirstName", names[0]); SqlParameter lastNameParameter = new SqlParameter("@LastName", names[1]); var projects = db.Database.SqlQuery <ProjectViewModel>("EXEC usp_FindProjects @FirstName,@LastName", firstNameParameter, lastNameParameter).ToList(); foreach (var project in projects) { if (project.Description.Length >= 20) { Console.WriteLine( $"{project.Name} - {project.Description.Substring(0, 20)} ..., {project.StartDate}"); } else { Console.WriteLine($"{project.Name} - {project.Description}, {project.StartDate}"); } } }
static void Main() { SoftuniContext context = new SoftuniContext(); // Task[] tasks = new Task[900]; // for (int i = 0; i < 900; i++) // { // tasks[i] = Task.Run(() => Insert()); // } // // // Task.WaitAll(tasks); Console.WriteLine(context.Employees.Count()); //OrderBeforeToList(context); //OrderAfterToList(context); //Order before to list - 293 entries: 00:00:00.0329984 //Order before to list - 1000 entries: 00:00:00.0608150 //Order before to list - 10 000 entries: 00:00:00.2946331 //Order before to list - 100 000 entries: 00:00:04.1109568 //Order before to list - 1 000 000 entries: 00:00:45.6620870 //Order after to list - 293 entries: 00:00:00.0372287 //Order after to list - 1000 entries: 00:00:00.0545328 //Order after to list - 10 000 entries: 00:00:00.3236547 //Order after to list - 100 000 entries: 00:00:04.4210877 //Order after to list - 1 000 000 entries: 00:00:51.7048844 }
static void Main(string[] args) { var dbContext = new SoftuniContext(); var employees = dbContext.Employees .Where(x => x.Department.Manager.Department.Name == "Sales") .Select(x => new { Name = x.FirstName + ' ' + x.LastName, DepartmentName = x.Department.Name, Manager = x.Manager.LastName }); foreach (var employee in employees) { Console.WriteLine($"Name: {employee.Name} in department: {employee.DepartmentName} with manager: {employee.Manager}"); } //all employees var employeesAll = dbContext.Employees.ToList(); foreach (var employee in employeesAll) { Console.WriteLine($"{employee.FirstName} {employee.LastName}"); } }
private static void CallAStoreProcedure(SoftuniContext contex) { var projectsInfo = contex.GetProjectsByEmployee("Ruth", "Ellerbrock"); foreach (ProjectInfo projectInfo in projectsInfo) { Console.WriteLine($"{projectInfo.Name} - {projectInfo.Description.Substring(0, 20)}... {projectInfo.StartDate}"); } }
private static void PrimtNamesWithLinq(SoftuniContext context) { var employees = context.Employees .Where(e => e.Projects.Count(p => p.StartDate.Year == 2012) != 0);//.GroupBy(s=>s); foreach (var employee in employees) { //Console.WriteLine(employee.FirstName); } }
static void Main(string[] args) { using (var context = new SoftuniContext()) { var employees = context.Employees.Where(e => e.Salary > 50000); foreach (var employee in employees) { Console.WriteLine($"{employee.FirstName}"); } } }
static void Main(string[] args) { using (var context = new SoftuniContext()) { var employees = context.Employees; foreach (var employee in employees) { Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.MiddleName} {employee.JobTitle} {employee.Salary}"); } } }
static void Main(string[] args) { using (var context = new SoftuniContext()) { 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) { Console.WriteLine($"{employee.FirstName} {employee.LastName} from {employee.Department.Name} - ${employee.Salary:f2}"); } } }
static void Main() { var dbContext = new SoftuniContext(); int id = 147; var employee = dbContext.Employees.Find(id); Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.JobTitle}"); var projects = employee.Projects.OrderBy(n => n.Name).ToList(); projects.ForEach(p => Console.WriteLine(p.Name)); }
private static void OrderAfterToList(SoftuniContext context) { Stopwatch afterWatch = new Stopwatch(); afterWatch.Start(); var employees = context.Employees.ToList().OrderBy(employee => employee.JobTitle) .ThenByDescending(employee => employee.DepartmentID).ToList(); afterWatch.Stop(); Console.WriteLine($"Order after ToList: {afterWatch.Elapsed}"); }
//13. Find Employees by First Name Starting with ‘SA’ public static void NameStartingwithSAseSalaries() { SoftuniContext context = new SoftuniContext(); using (context) { var employeesWithSA = context.Employees.Where(e => e.FirstName.Substring(0, 2) == "SA"); foreach (var emp in employeesWithSA) { Console.WriteLine(emp.FirstName + " " + emp.LastName + " - " + emp.JobTitle + " -" + string.Format(" (${0:F4})", emp.Salary)); } } }
//4.Employees with Salary Over 50 000 public static void EmployeeswithSalaryOver50000() { SoftuniContext contex = new SoftuniContext(); using (contex) { List <string> EmployeeswithSalaryOver50000 = contex.Employees.Where(e => e.Salary > 50000).Select(e => e.FirstName).ToList(); foreach (var emp in EmployeeswithSalaryOver50000) { Console.WriteLine(emp); } } }
//08 Address by Town Name public static void AddressByTownName() { SoftuniContext context = new SoftuniContext(); using (context) { var address = context.Employees.Select(e => e.Address).OrderByDescending(e => e.Employees.Count).ThenBy(e => e.Town.Name).Distinct().Take(1000); foreach (var ad in address) { Console.WriteLine(ad.AddressText + ", " + ad.Town.Name + " "); } } }
private static void EmployeesMaximumSalaries(SoftuniContext contex) { var departmentsMax = contex.Departments.Select(department => new { department.Name, MaxSalary = department.Employees.Max(employee => employee.Salary) }).Where(arg => arg.MaxSalary < 30000 | arg.MaxSalary > 70000); foreach (var departmentMax in departmentsMax) { Console.WriteLine($"{departmentMax.Name} - {departmentMax.MaxSalary}"); } }
private static void PrintNamesWithNativeQuery(SoftuniContext context) { string query = "SELECT DISTINCT e.FirstName" + "FROM [dbo].[EmployeesProjects] AS ep" + "INNER JOIN [dbo].[Projects] AS p" + "ON p.ProjectID = ep.ProjectID" + "INNER JOIN[dbo].[Employees] AS e" + "ON e.EmployeeID = ep.EmployeeID" + "WHERE YEAR(p.StartDate) = 2002"; var a = context.Database.SqlQuery <SoftuniContext>(query); a.ToListAsync(); }
//05 Employees from Seattle public static void EmployeesfromSeattle() { SoftuniContext contex = new SoftuniContext(); using (contex) { List <string> employeesfromSeattle = contex.Employees.OrderBy(e => e.Salary).ThenByDescending(e => e.FirstName).Where(e => e.DepartmentID == 6).Select(e => e.FirstName + " " + e.LastName + " from " + e.Department.Name + " - $" + e.Salary).ToList(); foreach (var name in employeesfromSeattle) { Console.WriteLine(name); } } }
//EX18 public static void EmployeesMaxSalaries() { var db = new SoftuniContext(); var depts = db.Departments.Where( d => d.Employees.Max(e => e.Salary) < 30000m || d.Employees.Max(e => e.Salary) > 70000m) .Select(d => new { d.Name, max = d.Employees.Max(e => e.Salary) }) .ToList(); foreach (var d in depts) { Console.WriteLine($"{d.Name} - {d.max}"); } }
//09 Employee with id 147 public static void EmployeeWithId147() { SoftuniContext context = new SoftuniContext(); using (context) { var employee147 = context.Employees.Find(147); var projectEmp147 = employee147.Projects.OrderBy(p => p.Name); Console.WriteLine(employee147.FirstName + " " + employee147.LastName + " " + employee147.JobTitle); foreach (var project in projectEmp147) { Console.WriteLine(project.Name); } } }
static void Main(string[] args) { var optionBuilder = new DbContextOptionsBuilder <SoftuniContext>(); // optionBuilder.UseSqlServer("path to database or use default"); optionBuilder.UseLoggerFactory(factory); using var db = new SoftuniContext(optionBuilder.Options); //if table doesnt exist create it db.Database.EnsureCreated(); var firstEmployee = db.Employees.FirstOrDefault(); firstEmployee.FirstName = "Delyan"; db.SaveChanges(); }
//11. Find Latest 10 Projects public static void FindLast10Projects() { SoftuniContext context = new SoftuniContext(); using (context) { var lastTenProjects = context.Projects.OrderByDescending(p => p.StartDate) .Take(10).OrderBy(p => p.Name); foreach (Project p in lastTenProjects) { Console.WriteLine(p.Name + " " + p.Description + " " + p.StartDate + " " + p.EndDate); } } }
static void Main(string[] args) { using (var context = new SoftuniContext()) { CultureInfo.DefaultThreadCurrentCulture = CultureInfo.InstalledUICulture; var employees = context.Employees.Where(e => e.Projects.Count(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003) > 0).Take(30); foreach (var employee in employees) { Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.Manager.FirstName}"); foreach (var project in employee.Projects) { Console.WriteLine($"--{project.Name} {project.StartDate} {project.EndDate}"); } } } }
// 03.Employees Full Information public static void EmployeesFullInformation() { SoftuniContext context = new SoftuniContext(); using (context) { List <string> employees = context.Employees.OrderBy(e => e.EmployeeID) .Select( e => e.FirstName + " " + e.LastName + " " + e.MiddleName + " " + e.JobTitle + " " + e.Salary) .ToList(); foreach (var id in employees) { Console.WriteLine(id + "00"); } } }
//15.First Letter public static void DeleteProjectById()//The way to delete a project { SoftuniContext context = new SoftuniContext(); using (context) { var project = context.Projects.Find(2); foreach (Employee emp in project.Employees)//First we delete all projects from all employees { emp.Projects.Remove(project); } context.Projects.Remove(project); context.SaveChanges(); var projects = context.Projects.Select(p => p.Name).Take(10); Console.WriteLine(project); } }
static void Main(string[] args) { using var dbContext = new SoftuniContext(); //Console.WriteLine(GetEmployeeFullNames(dbContext)); //Console.WriteLine(EmployeesWithSalaryOver5000(dbContext)); //Console.WriteLine(EmployeesFromResearchAndDevelopment(dbContext)); //AddNewAddressToEmployee(dbContext); //Console.WriteLine(AddNewAddressToEmployee(dbContext)); //Console.WriteLine(EmployeesWithProjectsInPeriod2001_2003(dbContext)); //Console.WriteLine(GetAddressesByTown(dbContext)); //Console.WriteLine(GetEmployee147(dbContext)); //Console.WriteLine(DepartmentsWithMoreThan5Employees(dbContext)); //Console.WriteLine(GetLatest10Projects(dbContext)); //Console.WriteLine(IncreaseSalaries(dbContext)); //Console.WriteLine(IncreaseSalaryBy12Procent(13500.0000m)); //Console.WriteLine(RemoveTown(dbContext)); //Console.WriteLine(EmployeesNameStartsWithSa(dbContext)); }