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