public static void firstQuery()
{
var context = new SoftUniEntities();
var employees = context.Employees
.Where(e => e.Projects.Any(
p => p.StartDate.Year >= 2001 && p.EndDate.Value.Year <= 2003))
.Select(e => new
{
e.FirstName,
e.LastName,
ManagerName = e.Employee1.FirstName,
Projects = e.Projects.Select(p => new
{
p.Name,
p.StartDate,
p.EndDate
})
});
foreach (var employee in employees)
{
Console.WriteLine("--{0} {1} ManId: {2}",
employee.FirstName,
employee.LastName,
employee.ManagerName);
foreach (var project in employee.Projects)
{
Console.WriteLine("{0} {1} {2}",
project.Name,
project.StartDate.Date,
project.EndDate.HasValue ? project.EndDate.Value.Date.ToString() : "not finished");
}
}
}
public static void forthQuery()
{
var context = new SoftUniEntities();
var departments = context.Departments
.Where(d => d.Employees.Count > 5)
.OrderBy(d => d.Employees.Count)
.Select(d => new
{
d.Name,
Manager = d.Employee.LastName,
Employees = d.Employees
.Select(e => new
{
e.FirstName,
e.LastName,
e.HireDate,
e.JobTitle
})
}).ToList();
Console.WriteLine(departments.Count());
foreach (var department in departments)
{
Console.WriteLine("--{0} - Manager: {1}, Employees: {2}",
department.Name,
department.Manager,
department.Employees.Count());
}
}
public static void Add(Employee employee)
{
var softuniContext = new SoftUniEntities();
ContextEntities.Employees.Add(employee);
ContextEntities.SaveChanges();
}
static void Main(string[] args)
{
//Read
var context = new SoftUniEntities();
var employee = context.Employees.Find(1);
Console.WriteLine(employee.FirstName + ' ' + employee.LastName);
var employees = context.Employees
.Where(e => e.Salary > 50000)
.Select(e => new {
e.FirstName,
e.LastName,
e.Salary
})
.ToList();
foreach (var emp in employees)
{
Console.WriteLine($"{emp.FirstName} {emp.LastName} - {emp.Salary}");
}
//insert
var town = new Town();
town.Name = "Stara Zagora";
context.Towns.Add(town);
context.SaveChanges();
//Cascade Insert
var addr = new Address();
addr.AddressText = ("Some Adress");
addr.Town = new Town()
{
Name = "Velingrad"
};
context.Addresses.Add(addr);
context.SaveChanges();
//Update
var empp = context.Employees.First();
empp.FirstName = "Dani";
employee.Department = context.Departments.First();
context.SaveChanges();
var departament = context.Departments.First();
var guys = departament.Employees.ToList();
foreach (var guy in guys)
{
Console.WriteLine(guy.FirstName);
}
//Delete
}
public static void ChangeRecord()
{
var context1 = new SoftUniEntities();
var context2 = new SoftUniEntities();
context1.Employees.Find(1).FirstName = "context1";
context2.Employees.Find(1).FirstName = "context2";
context1.SaveChanges();
context2.SaveChanges();
}
static void Main()
{
var context = new SoftUniEntities();
var projects = context.FindAllProjectsForGivenEmployee("Ruth", "Ellerbrock");
foreach (var project in projects)
{
Console.WriteLine("{0} - {1} - {2}", project.Name, project.Description, project.StartDate);
}
}
static void Main()
{
var context = new SoftUniEntities();
// Estabish connection to server in advance
var totalCount = context.Employees.Count();
Stopwatch sw = new Stopwatch();
sw.Start();
string query = @"
SELECT
e.FirstName
FROM Employees AS e
WHERE EXISTS (SELECT
1
FROM EmployeesProjects AS ep
INNER JOIN Projects AS p ON p.ProjectID = ep.ProjectID
WHERE (e.EmployeeID = ep.EmployeeID) AND (2002 = (DATEPART (year, p.StartDate)))
)";
var employeesGetByNativeQuery = context.Database.SqlQuery<string>(query).ToArrayAsync().Result;
foreach (var employeeN in employeesGetByNativeQuery)
{
//Console.WriteLine(employeeN);
}
Console.WriteLine("Native: {0}", sw.Elapsed);
sw.Restart();
var employeesGetByLinq = context.Employees
.Where(e => e.Projects
.Any(p => p.StartDate.Year == 2002))
.Select(e => new { e.FirstName });
foreach (var employeeL in employeesGetByLinq)
{
//Console.WriteLine(employeeL.FirstName);
}
Console.WriteLine("Linq: {0}", sw.Elapsed);
}
static DAO()
{
db = new SoftUniEntities();
}
private static void PrintNamesWithNativeQuery()
{
var db = new SoftUniEntities();
string nativeSQLQuery =
"SELECT e.FirstName, p.StartDate" +
"FROM dbo.Employees AS e" +
"JOIN EmployeesProjects AS ep ON e.EmployeeID = ep.EmployeeID" +
"JOIN Projects AS p ON ep.ProjectID = p.ProjectID" +
"WHERE p.StartDate BETWEEN '2002-01-01' AND '2002-12-31'";
var employees = db.Database.SqlQuery<string>(
nativeSQLQuery, "Employees");
}
private static void PrintNamesWithLinqQuery()
{
var db = new SoftUniEntities();
using (db)
{
var employees = db.Employees
.Where(e => e.Projects.Any(p => p.StartDate.Year == 2002))
.Select(e => e.FirstName);
}
}
static void Main(string[] args)
{
//
// Problem 2.Employee DAO Class
//
var employee = new Employee()
{
FirstName = "Pencho",
LastName = "Minkov",
JobTitle = "CC&B Developer",
DepartmentID = 1,
HireDate = DateTime.Now,
Salary = 1500
};
//DAO.Add(employee);
//Employee employee1 = DAO.FindByKey(294);
//Console.WriteLine(employee1.FirstName + " " + employee1.LastName);
//employee1.FirstName = "Angel";
//DAO.Modify(employee1);
//DAO.Delete(employee);
//
// Problem 3.Database Search Queries
//
var db = new SoftUniEntities();
// 1.Find all employees who have projects started in the time period 2001 - 2003 (inclusive). Select each employee's first name, last name and manager name and each of their projects' name, start date, end date.
//using (db)
//{
// var employees = db.Employees
// .Where(emp => emp.Projects.Any(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003))
// .Select(emp => new
// {
// FirstName = emp.FirstName,
// LastName = emp.LastName,
// Projects = emp.Projects
// .Where(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003)
// .Select(p => p.Name + ", start date: " + p.StartDate),
// Manager = emp.Employee1.FirstName + " " + emp.Employee1.LastName
// });
// foreach (var empl in employees)
// {
// Console.Write(
// empl.FirstName + " "
// + empl.LastName + ", Manager - "
// + empl.Manager + ", Projects - ");
// foreach (var project in empl.Projects)
// {
// Console.Write("{0} ", project.ToString());
// }
// Console.WriteLine();
// }
//}
// 2.Find all addresses, ordered by the number of employees who live there (descending), then by town name (ascending). Take only the first 10 addresses and select their address text, town name and employee count.
//using (db)
//{
// var addresess =
// (from a in db.Addresses
// orderby a.Employees.Count descending, a.Town.Name ascending
// select new
// {
// Adress = a.AddressText,
// Town = a.Town.Name,
// EmployeeCount = a.Employees.Count
// }).Take(10);
// foreach (var address in addresess)
// {
// Console.WriteLine("{0}, {1} - {2} emplayees",
// address.Adress,
// address.Town,
// address.EmployeeCount);
// }
//}
// 3.Get an employee by id (e.g. 147). Select only his/her first name, last name, job title and projects (only their names). The projects should be ordered by name (ascending).
//using (db)
//{
// var employeeById = db.Employees
// .Where(empl => empl.EmployeeID == 147)
// .Select(empl => new
// {
// Info = empl.FirstName + " " + empl.LastName + ", Job Title: " + empl.JobTitle + ", Projects: ",
// Projects = empl.Projects
// .OrderBy(p => p.Name)
// .Select(p => p.Name)
// }
// ).FirstOrDefault();
// Console.Write(employeeById.Info);
// foreach (var project in employeeById.Projects)
// {
// Console.Write(project.ToString() + ", ");
// }
// Console.WriteLine();
//}
//4.Find all departments with more than 5 employees. Order them by employee count (ascending). Select the department name, manager name and first name, last name, hire date and job title of every employee.
//using (db)
//{
// var departments = db.Departments
// .Where(d => d.Employees.Count > 5)
// .OrderBy(d => d.Employees.Count)
// .Select(d => new
// {
// DepartmentName = d.Name,
// Manager = d.Employee.FirstName,
// Employees = d.Employees
// .Select(e => new
// {
// Info = e.FirstName + " " + e.LastName + " " + e.HireDate + " " + e.JobTitle
// })
// });
// foreach (var department in departments)
// {
// Console.WriteLine("{0}: {1} - ", department.DepartmentName, department.Manager);
// foreach (var empl in department.Employees)
// {
// Console.WriteLine(" {0}", empl.Info);
// }
// }
//}
//
// Problem 4.Native SQL Query
//
//var totalcount = db.Employees.Count();
//var sw = new Stopwatch();
//sw.Start();
//PrintNamesWithNativeQuery();
//Console.WriteLine("Native: {0}", sw.Elapsed);
//sw.Restart();
//PrintNamesWithLinqQuery();
//Console.WriteLine("Linq: {0}", sw.Elapsed);
//
// Problem 5.Concurrent Database Changes with EF
//
//var employee1 = db.Employees.Find(294);
//employee1.FirstName = "Angel";
//var db2 = new SoftUniEntities();
//var employee2 = db2.Employees.Find(294);
//employee1.FirstName = "Acho";
//db.SaveChanges();
//db2.SaveChanges();
//
// Problem 6.Call a Stored Procedure
//
// Stored Procedure - SQL
//CREATE PROCEDURE GetProjectsByEmployee
// @FirstName nvarchar(50),
// @LastName nvarchar(50)
//AS
// SET NOCOUNT ON;
// SELECT p.Name, p.Description, p.StartDate
// FROM dbo.Employees AS e
// JOIN EmployeesProjects AS ep ON e.EmployeeID = ep.EmployeeID
// JOIN Projects AS p ON ep.ProjectID = p.ProjectID
// WHERE e.FirstName = @FirstName AND e.LastName = @LastName
//GO
var projects = db.GetProjectsByEmployee("Rob", "Walters");
foreach (var project in projects)
{
Console.WriteLine(project.Name + ", " + project.Description + ", " + project.StartDate);
}
}
public static void secondQuery()
{
var context = new SoftUniEntities();
var addresses = context.Addresses
.OrderByDescending(a => a.Employees.Count())
.ThenBy(a => a.Town.Name)
.Select(a => new
{
a.AddressText,
Town = a.Town.Name,
a.Employees.Count
})
.Take(10);
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2} employees",
address.AddressText,
address.Town,
address.Count);
}
}
public static void nativeQuery()
{
DbContext haha = new SoftUniEntities();
var emps2 = haha.Database.SqlQuery<string>(@"SELECT e.FirstName FROM Employees e
JOIN EmployeesProjects ep ON ep.EmployeeID = e.EmployeeID
JOIN Projects p ON p.ProjectID = ep.ProjectID
WHERE YEAR(p.StartDate) = 2002").ToList();
}
public static void linqQuery()
{
var context = new SoftUniEntities();
var emps =
context.Employees.Where(e => e.Projects.Any(p => p.StartDate.Year == 2002)).Select(e => e.FirstName).ToList();
}
public static void thirdQuery()
{
var context = new SoftUniEntities();
var employee = context.Employees
.Where(e => e.EmployeeID == 147)
.Select(e => new
{
e.FirstName,
e.LastName,
e.JobTitle,
Projects = e.Projects
.Select(p => p.Name)
.OrderBy(p => p)
}).ToList();
foreach (var emp in employee)
{
Console.WriteLine("{0} {1} {2}",
emp.FirstName,
emp.LastName,
emp.JobTitle);
foreach (var project in emp.Projects)
{
Console.WriteLine(project);
}
}
}
static void Main()
{
var context = new SoftUniEntities();
Console.Write("Select from 1 to 4: ");
string input = Console.ReadLine();
switch (input)
{
case "1":
DateTime startDate = Convert.ToDateTime("01/01/2001");
DateTime endDate = Convert.ToDateTime("31/12/2003");
var employees = context.Employees
.Where(e => e.Projects
.Any(p => p.StartDate >= startDate && p.EndDate <= endDate))
.Select(e => new
{
FirstName = e.FirstName,
LastName = e.LastName,
ManagerFirstName = e.Manager.FirstName,
ManagerLastName = e.Manager.LastName,
Projects = e.Projects.Select(p => new
{
ProjectName = p.Name,
StartDate = p.StartDate,
EndDate = p.EndDate
})
});
foreach (var employee in employees)
{
foreach (var project in employee.Projects)
{
Console.WriteLine("Employee: {0} {1}\nManager: {2} {3}\nProject: {4} - {5} - {6}\n",
employee.FirstName,
employee.LastName,
employee.ManagerFirstName,
employee.ManagerLastName,
project.ProjectName,
project.StartDate,
project.EndDate);
}
}
break;
case "2":
var addresses = context.Addresses
.OrderByDescending(a => a.Employees.Count)
.ThenBy(a => a.Town.Name)
.Take(10)
.Select(a => new
{
AddressText = a.AddressText,
Town = a.Town.Name,
EmployeeCount = a.Employees.Count
});
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2} employees",
address.AddressText,
address.Town,
address.EmployeeCount);
}
break;
case "3":
var emp = context.Employees
.Select(e => new
{
ID = e.EmployeeID,
FirstName = e.FirstName,
LastName = e.LastName,
JobTitle = e.JobTitle,
Projects = e.Projects.OrderBy(p => p.Name).Select(p => new { Name = p.Name })
})
.FirstOrDefault(e => e.ID == 147);
Console.WriteLine("{0} - {1} - {2} - Projects:",
emp.FirstName,
emp.LastName,
emp.JobTitle);
foreach (var project in emp.Projects)
{
Console.WriteLine(project.Name);
}
break;
case "4":
var departments = context.Departments
.Where(d => d.Employees.Count > 5)
.OrderBy(d => d.Employees.Count)
.Select(d => new
{
Name = d.Name,
Manager = d.Manager.FirstName,
Employees = d.Employees
});
foreach (var department in departments)
{
Console.WriteLine("Department: {0}\nManager: {1}\n", department.Name, department.Manager);
foreach (var employee in department.Employees)
{
Console.WriteLine("Employee: {0} {1}; Hire on: {2}; Job title: {3}",
employee.FirstName,
employee.LastName,
employee.HireDate,
employee.JobTitle);
}
}
break;
default:
Console.WriteLine("Wrong input.");
break;
}
}