private void NativeSQLWithMyQueryFromFile(string queryPath, SoftuniContext context)
{
var sql = File.ReadAllText(queryPath);
var foundEmployees = context.Database.SqlQuery <string>(sql).ToArray();
Console.WriteLine($"SQL with my query from FILE Found employees: {foundEmployees.Length}");
}
private void PrintEmployeesFullInformation(SoftuniContext context)
{
foreach (var e in context.Employees.OrderBy(e => e.EmployeeID))
{
Console.WriteLine($"{e.FirstName} {e.LastName} {e.MiddleName} {e.JobTitle} {e.Salary}");
}
}
private void RemoveTown(string townName, SoftuniContext context)
{
var deletingTown = context.Towns
.FirstOrDefault(t => t.Name.Equals(townName, StringComparison.OrdinalIgnoreCase));
if (deletingTown == null)
{
Console.WriteLine($"Town {townName} not found.");
return;
}
foreach (var employee in context.Employees.Where(e => e.Address.TownID == deletingTown.TownID))
{
employee.Address = null;
}
var deletingAddresses = context.Addresses
.Where(a => a.TownID == deletingTown.TownID).ToArray();
context.Addresses.RemoveRange(deletingAddresses);
context.Towns.Remove(deletingTown);
context.SaveChanges();
Console.WriteLine($"{deletingAddresses.Length} address in {deletingTown.Name} was deleted");
}
private void PrintEmployeesWhoseFirstNameStartsWith(string key, SoftuniContext context)
{
foreach (var employee in context.Employees
.Where(e => e.FirstName.StartsWith(key)))
{
Console.WriteLine($"{employee.FirstName} {employee.LastName} - {employee.JobTitle} - (${employee.Salary:F4})");
}
}
private void LINQsQuery(SoftuniContext context)
{
var foundEmployees = context.Employees
.Where(e => e.Projects.Any(p => p.StartDate.Year == 2002))
.Select(e => e.FirstName)
.ToArray();
Console.WriteLine($"LINQ Found employees: {foundEmployees.Length}");
}
private void PrintAddressesByTownName(SoftuniContext context)
{
foreach (var address in context.Addresses
.OrderByDescending(a => a.Employees.Count)
.ThenBy(a => a.Town.Name)
.Take(10))
{
Console.WriteLine($"{address.AddressText}, {address.Town.Name} - {address.Employees.Count} employees");
}
}
private void PrintEmployeesByDepartment(string keyDepartmentName, SoftuniContext context)
{
foreach (var employee in context.Employees
.Where(e => e.Department.Name == keyDepartmentName)
.OrderBy(e => e.Salary)
.ThenByDescending(e => e.FirstName))
{
Console.WriteLine($"{employee.FirstName} {employee.LastName} from {keyDepartmentName} - ${employee.Salary:F2}");
}
}
private void PrintLatestStartedProjects(int numberOfProjects, SoftuniContext context)
{
foreach (var project in context.Projects
.OrderByDescending(p => p.StartDate)
.Take(numberOfProjects)
.OrderBy(p => p.Name))
{
Console.WriteLine($"{project.Name} {project.Description} " +
$"{project.StartDate.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture)} " +
$"{project.EndDate?.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture)}");
}
}
private void PrintDepartmentsWithEmployeesMoreThan(int empMoreThan, SoftuniContext context)
{
foreach (var department in context.Departments
.Where(d => d.Employees.Count > empMoreThan)
.OrderBy(d => d.Employees.Count))
{
Console.WriteLine($"{department.Name} {department.Manager.FirstName}");
foreach (var employee in department.Employees)
{
Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.JobTitle}");
}
}
}
private void PrintEmployeeProjectsById(int employeeId, SoftuniContext context)
{
var employee = context.Employees
.FirstOrDefault(e => e.EmployeeID == employeeId);
if (employee == null)
{
Console.WriteLine($"There is not an employee with Id {employeeId}");
return;
}
Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.JobTitle}");
Console.WriteLine(string.Join(Environment.NewLine, employee.Projects
.Select(p => p.Name)
.OrderBy(pn => pn)));
}
private void IncreaseSalariesByDepartment(int percentageIncreasement, SoftuniContext context, params string[] departmentNames)
{
var employees = context.Employees
.Where(e => departmentNames.Contains(e.Department.Name));
foreach (var employee in employees)
{
employee.Salary *= 1.12M;
}
context.SaveChanges();
foreach (var employee in employees)
{
Console.WriteLine($"{employee.FirstName} {employee.LastName} (${employee.Salary:F6})");
}
}
private void DeleteProject(int projectId, SoftuniContext context)
{
var project = context.Projects.Find(projectId);
if (project == null)
{
return;
}
foreach (var employee in context.Employees.Where(e => e.Projects.Any(p => p.ProjectID == projectId)))
{
employee.Projects.Remove(project);
}
context.Projects.Remove(project);
context.SaveChanges();
}
private void NativeSQLWithLINQsGeneratedQuery(SoftuniContext context)
{
var sql = @"
SELECT [Extent1].[FirstName] AS [FirstName]
FROM [dbo].[Employees] AS [Extent1]
WHERE EXISTS
(
SELECT 1 AS [C1]
FROM [dbo].[EmployeesProjects] AS [Extent2]
INNER JOIN [dbo].[Projects] AS [Extent3] ON [Extent3].[ProjectID] = [Extent2].[ProjectID]
WHERE([Extent1].[EmployeeID] = [Extent2].[EmployeeID])
AND (2002 = (DATEPART(year, [Extent3].[StartDate])))
);
";
var foundEmployees = context.Database.SqlQuery <string>(sql).ToArray();
Console.WriteLine($"SQL with LINQ query Found employees: {foundEmployees.Length}");
}
private void NativeSQLWithMyQuery(SoftuniContext context)
{
var sql = @"
SELECT FirstName
FROM
(
SELECT DISTINCT
e.EmployeeID,
e.FirstName
FROM Employees AS e
JOIN EmployeesProjects AS ep ON ep.EmployeeID = e.EmployeeID
JOIN Projects AS p ON p.ProjectID = ep.ProjectID
WHERE DATEPART(YEAR, p.StartDate) = 2002
) AS EmployeeIdNameSelection;
";
var foundEmployees = context.Database.SqlQuery <string>(sql).ToArray();
Console.WriteLine($"SQL with my query Found employees: {foundEmployees.Length}");
}
public void Run()
{
using (SoftuniContext context = new SoftuniContext())
{
// this.PrintEmployeesFullInformation(context); // 3. Employees Full Information
// this.PrintEmployeesWithSalaryOver(50000, context); // 4. Employees with Salary Over 50 000
// this.PrintEmployeesByDepartment("Research and Development", context); // 5. Employees from Research and Development
// this.AssignANewAddress("Nakov", "Vitoshka 15", 4, context); // 6. Adding a New Address and Updating Employee
// this.PrintFirsttTenEmployeesOrderedDescendingById(context); // 6. Adding a New Address and Updating Employee
// this.PrintTopThirtyEmployeesAccordingProjectStartdate(2001, 2003, context); // 7. Find Employees in Period
// this.PrintAddressesByTownName(context); // 8. Addresses by Town Name
// this.PrintEmployeeProjectsById(147, context); // 9. Employee with id 147
// this.PrintDepartmentsWithEmployeesMoreThan(5, context); // 10. Departments with more than 5 employees
// this.PrintLatestStartedProjects(10, context); // 11. Find Latest 10 Projects
// this.IncreaseSalariesByDepartment(12, context, "Engineering", "Tool Design", "Marketing", "Information Services"); // 12. Increase Salaries
// this.PrintEmployeesWhoseFirstNameStartsWith("SA", context); // 13. Find Employees by First Name Starting with ‘SA’
// this.DeleteProject(2, context); // 15. Delete Project by Id
// this.PrintFirstTenProjects(context); // 15. Delete Project by Id
// this.RemoveTown("Sofia", context); // 16. Remove Towns
this.TestLinqVsSqlCommand(context); // 17. *Native SQL Query
}
}
private void TestLinqVsSqlCommand(SoftuniContext context)
{
Stopwatch watch = new Stopwatch();
// Connection before the test makes the result real
context.Projects.Count(); // Just for initializing the connection
watch.Start();
LINQsQuery(context);
watch.Stop();
Console.WriteLine($"Ticks: {watch.Elapsed.Ticks}");
Console.WriteLine($"Milliseconds: {watch.Elapsed.Milliseconds}");
Console.WriteLine();
watch.Reset();
watch.Start();
NativeSQLWithMyQuery(context);
watch.Stop();
Console.WriteLine($"Ticks: {watch.Elapsed.Ticks}");
Console.WriteLine($"Milliseconds: {watch.Elapsed.Milliseconds}");
Console.WriteLine();
watch.Reset();
watch.Start();
NativeSQLWithLINQsGeneratedQuery(context);
watch.Stop();
Console.WriteLine($"Ticks: {watch.Elapsed.Ticks}");
Console.WriteLine($"Milliseconds: {watch.Elapsed.Milliseconds}");
Console.WriteLine();
watch.Reset();
watch.Start();
NativeSQLWithMyQueryFromFile(@"..\..\Employees selection query.sql", context);
watch.Stop();
Console.WriteLine($"Ticks: {watch.Elapsed.Ticks}");
Console.WriteLine($"Milliseconds: {watch.Elapsed.Milliseconds}");
}
private void PrintFirstTenProjects(SoftuniContext context)
=> Console.WriteLine(string.Join(Environment.NewLine, context.Projects
.Take(10)
.Select(p => p.Name)));
private void PrintEmployeesWithSalaryOver(int salary, SoftuniContext context)
=> Console.WriteLine(string.Join(Environment.NewLine, context.Employees
.Where(e => e.Salary > salary)
.Select(e => e.FirstName)));
private void PrintTopThirtyEmployeesAccordingProjectStartdate(int minYear, int maxYear, SoftuniContext context)
{
foreach (var employee in context.Employees
.Where(e => e.Projects.Any(p => p.StartDate.Year >= minYear) &&
e.Projects.Any(p => p.StartDate.Year <= maxYear))
.Take(30))
{
Console.WriteLine($"{employee.FirstName} {employee.LastName} {employee.Manager.FirstName}");
Console.Write("--");
Console.WriteLine(string.Join($"{Environment.NewLine}--", employee.Projects
.Select(p => $"{p.Name} {p.StartDate.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture)} {p.EndDate?.ToString("M/d/yyyy h:mm:ss tt", CultureInfo.InvariantCulture)}")));
}
}
private void AssignANewAddress(string familyToAssignNewAddress, string newAddressText, int townId, SoftuniContext context)
{
var newAddress = new Address()
{
AddressText = newAddressText,
TownID = townId
};
foreach (var employee in context
.Employees.Where(e => e.LastName == familyToAssignNewAddress))
{
employee.Address = newAddress;
}
context.SaveChanges();
}
private void PrintFirsttTenEmployeesOrderedDescendingById(SoftuniContext context)
=> Console.WriteLine(string.Join(Environment.NewLine, context.Employees
.OrderByDescending(e => e.AddressID)
.Take(10)
.Select(e => e.Address.AddressText)));
