public static void Delete(Employee employee)
{
var context = new SoftUniEntities();
context.Entry(employee).State = EntityState.Deleted;
context.SaveChanges();
}
public static Employee FindByKey(object key)
{
var context = new SoftUniEntities();
Employee employee = context.Employees.Find(key);
return(employee);
}
public static void Add(Employee employee)
{
var context = new SoftUniEntities();
var entry = context.Entry(employee);
entry.State = EntityState.Added;
context.Employees.Add(employee);
context.SaveChanges();
}
public static void PrintNamesWithLinqQuery_LINQtoEntities()
{
var context = new SoftUniEntities();
var queryResult = (from e in context.Employees
where (from p in e.Projects
where p.StartDate.Year == 2002
select p).Any()
select e.FirstName);
Console.WriteLine(string.Join(" | ", queryResult));
}
public static void PrintNamesWithLinqQuery_WithExtensionMethods()
{
var context = new SoftUniEntities();
var queryResult = context.Employees
.Where(emp => emp.Projects.Any(p => p.StartDate.Year == 2002))
.Select(emp => emp.FirstName);
var employees = queryResult.ToList();
Console.WriteLine(string.Join(" | ", employees));
}
public static void DataChanges()
{
var ctx1 = new SoftUniEntities();
var employee1 = ctx1.Employees.Find(1);
employee1.FirstName = "Gosho";
var ctx2 = new SoftUniEntities();
var employee2 = ctx2.Employees.Find(1);
employee2.FirstName = "Vanka";
ctx1.SaveChanges();
ctx2.SaveChanges();
}
public static void Main()
{
SoftUniEntities context = new SoftUniEntities();
using (context)
{
// Town townOne = new Town();
// townOne.Name = "Studentski Grad";
//
// context.Towns.Add(townOne);
// context.SaveChanges();
Town st = context.Towns.FirstOrDefault(town => town.Name == "Sofia");
context.Addresses.RemoveRange(st.Addresses);
context.Towns.Remove(st);
context.SaveChanges();
}
}
public static void PrintNamesWithNativeQuery()
{
var context = new SoftUniEntities();
string nativeQuery =
"SELECT e.FirstName FROM Employees e " +
"WHERE EXISTS(SELECT p.ProjectID FROM Projects p " +
"LEFT JOIN EmployeesProjects ep " +
"ON p.ProjectID = ep.ProjectID " +
"LEFT JOIN Employees em " +
"ON ep.EmployeeID = em.EmployeeID " +
"WHERE em.EmployeeID = e.EmployeeID " +
"AND YEAR(p.StartDate) = 2002)";
var queryResult = context.Database.SqlQuery <string>(nativeQuery);
List <string> employees = queryResult.ToList();
Console.WriteLine(string.Join(" | ", employees));
}
public static void Modify(Employee employee, string newName)
{
var context = new SoftUniEntities();
string[] name = Regex.Split(newName.Trim(), @"\s+");
string firstName = Regex.Split(newName.Trim(), @"\s+")[0];
employee.FirstName = firstName;
if (name.Length == 2)
{
string lastName = Regex.Split(newName.Trim(), @"\s+")[0];
employee.LastName = lastName;
}
var entry = context.Entry(employee);
entry.State = EntityState.Modified;
context.SaveChanges();
}
public static void FindAdressesWithEmployee()
{
var context = new SoftUniEntities();
var adresses = context.Addresses
.OrderByDescending(e => e.Employees.Count)
.ThenBy(e => e.Town.Name)
.Take(10)
.Select(e => new
{
AdressText = e.AddressText,
Town = e.Town.Name,
EmployeesCount = e.Employees.Count
});
foreach (var adress in adresses)
{
Console.WriteLine("{0}, {1} - {2} employees", adress.AdressText, adress.Town, adress.EmployeesCount);
}
}
public static void GetEmployeeById(int Id)
{
var context = new SoftUniEntities();
var employees = context.Employees
.Where(e => e.EmployeeID == Id)
.OrderBy(e => e.JobTitle)
.Select(e => new
{
FirstName = e.FirstName,
LastName = e.LastName,
JobTitle = e.JobTitle,
Project = e.Projects.Select(p => p.Name).FirstOrDefault()
});
foreach (var employee in employees)
{
Console.WriteLine("{0} {1}: Job Title: {2}, Project: {3}", employee.FirstName, employee.LastName, employee.JobTitle, employee.Project);
}
}
public static void FindEmployeesWithProjects()
{
var context = new SoftUniEntities();
var FirstDate = new DateTime(2000, 12, 31);
var secondDate2 = new DateTime(2003, 12, 31);
var employees = context.Employees
.Where(e => e.Projects
.Any(p => p.StartDate > FirstDate && p.StartDate <= secondDate2));
foreach (var employee in employees)
{
Console.WriteLine(employee.FirstName + ": ");
foreach (var project in employee.Projects)
{
Console.WriteLine(" Project: " + project.Name + " Start Date: " + project.StartDate.ToString("dd-MM-yyyy") + " End Date:" + project.EndDate);
}
Console.WriteLine();
}
}
public static void FindDepartmantWithMoreThan5Employees()
{
var context = new SoftUniEntities();
var departments = context.Departments
.Where(dep => dep.Employees.Count > 5)
.OrderBy(d => d.Employees.Count)
.Select(d => new
{
DepartmentName = d.Name,
Manager = d.Employee.FirstName + " " + d.Employee.LastName,
Employees = d.Employees.Count
}).ToList();
Console.WriteLine(departments.Count);
foreach (var dep in departments)
{
Console.WriteLine("--{0} - Manager: {1}, Employees: {2}",
dep.DepartmentName,
dep.Manager,
dep.Employees);
}
}
static void Main(string[] args)
{
var db = new SoftUniEntities();
db.GetProjectsOfEmployee("Rob", "Walters");
}
