public static void TestOperations()
{
var context = new SoftUniEntities();
Employee employee = new Employee()
{
FirstName = "Denis1",
LastName = "Neychev",
JobTitle = "Zidaro-mazach",
DepartmentID = 1,
ManagerID = 6,
HireDate = DateTime.Now,
Salary = 999
};
Employee.Add(employee);
context.SaveChanges();
Console.WriteLine(employee.EmployeeID);
employee.FirstName = employee.FirstName + '.';
Employee.Modify(employee);
context.SaveChanges();
Employee.Delete(employee);
context.SaveChanges();
}
public static void TestOperations()
{
var context = new SoftUniEntities();
Employee employee = new Employee()
{
FirstName = "Denis1",
LastName = "Neychev",
JobTitle = "Zidaro-mazach",
DepartmentID = 1,
ManagerID = 6,
HireDate = DateTime.Now,
Salary = 999
};
Employee.Add(employee);
context.SaveChanges();
Console.WriteLine(employee.EmployeeID);
employee.FirstName = employee.FirstName + '.';
Employee.Modify(employee);
context.SaveChanges();
Employee.Delete(employee);
context.SaveChanges();
}
public static void Add(Employee employee)
{
var context = new SoftUniEntities();
context.Employees.Add(employee);
context.SaveChanges();
}
public static void Add(Employee employee)
{
var context = new SoftUniEntities();
context.Employees.Add(employee);
context.SaveChanges();
}
public static void Modify(Employee employee)
{
var context = new SoftUniEntities();
Employee EmpModify = context.Employees.Find(employee.EmployeeID);
context.Entry(EmpModify).CurrentValues.SetValues(employee);
context.SaveChanges();
}
public static void Modify(Employee employee)
{
var context = new SoftUniEntities();
Employee EmpModify = context.Employees.Find(employee.EmployeeID);
context.Entry(EmpModify).CurrentValues.SetValues(employee);
context.SaveChanges();
}
static void Main(string[] args)
{
var context = new SoftUniEntities();
//var employeesNames = context.Employees.Where(e => e.Salary > 50000).Select(e => e.FirstName);
//foreach (var employeeName in employeesNames)
//{
// Console.WriteLine(employeeName);
//}
//var employees =
// context.Employees.Where(e => e.Department.Name == "Research and Development")
// .Select(e => new { e.FirstName, e.LastName, Department = e.Department.Name, e.Salary })
// .OrderBy(e => e.Salary)
// .ThenByDescending(e => e.FirstName);
//foreach (var employee in employees)
//{
// Console.WriteLine("{0} {1} from {2} - ${3:F2}", employee.FirstName, employee.LastName, employee.Department, employee.Salary);
//}
//var address = new Address()
// {
// AddressText = "Vitoshka 15",
// TownID = 4
// };
//Employee employee = null;
//employee = context.Employees.First(e => e.LastName == "Nakov");
//employee.Address = address;
//context.SaveChanges();
//var nakovAddress = context.Employees.First(e => e.LastName == "Nakov");
//Console.WriteLine(nakovAddress.Address.AddressText);
var projectToDelete = context.Projects.Find(2);
foreach (var employee in projectToDelete.Employees) // taka setvame zavisimostta na FK na null i chak sled tova moje da iztriem
{
employee.Projects = null;
}
context.Projects.Remove(projectToDelete);
context.SaveChanges();
}
public static void Delete(Employee employee)
{
var context = new SoftUniEntities();
var entry = context.Entry(employee);
if (entry.State == EntityState.Detached)
{
context.Employees.Attach(employee);
}
context.Employees.Remove(employee);
context.SaveChanges();
}
public static void Delete(Employee employee)
{
var context = new SoftUniEntities();
var entry = context.Entry(employee);
if (entry.State == EntityState.Detached)
{
context.Employees.Attach(employee);
}
context.Employees.Remove(employee);
context.SaveChanges();
}
public static Employee FindByKey(object key)
{
var context = new SoftUniEntities();
return context.Employees.Find(key);
}
public static Employee FindByKey(object key)
{
var context = new SoftUniEntities();
return(context.Employees.Find(key));
}
private static void Main()
{
//Problem 1
var context = new SoftUniEntities();
//Problem 2
TestClass.TestOperations();
//Problem 3.1
var employees = context.Employees.Where(e => e.Projects
.Any(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003))
.Select(e => new
{
e.FirstName,
e.LastName,
Projects = e.Projects
.Where(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003)
.Select(p => new
{
p.Name,
p.StartDate,
p.EndDate
}),
ManagerName = e.Employee1.FirstName + " " + e.Employee1.LastName
});
foreach (var employee in employees)
{
Console.WriteLine("Name: {0} {1}", employee.FirstName, employee.LastName);
Console.WriteLine("\tManager: {0}", employee.ManagerName);
Console.WriteLine("\tProjects:");
foreach (var project in employee.Projects)
{
Console.WriteLine("\t\tProject Name: {0}\n\t\tStart Date:" +
" {1}\n\t\tEnd Date{2}\n",
project.Name,
project.StartDate,
project.EndDate);
}
}
//Problem 3.2
var addresses = context.Addresses.Select(a => new
{
Address = a.AddressText,
a.Town.Name,
NumberOfEmployees = a.Employees.Count()
}).OrderByDescending(a => a.NumberOfEmployees).ThenBy(a => a.Name).Take(10);
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2}",
address.Address,
address.Name,
address.NumberOfEmployees);
}
//Problem 3.3
var emp = context.Employees.Where(e => e.EmployeeID == 1).Select(e => new
{
e.FirstName,
e.LastName,
e.JobTitle,
Projects = e.Projects
});
foreach (var e in emp)
{
string prj = "";
foreach (var proj in e.Projects)
{
prj += proj.Name + "\n ";
}
Console.WriteLine("First Name: {0}\nLast Name: {1}\nJob Title: {2}\nProjects: {3}",
e.FirstName,
e.LastName,
e.JobTitle,
prj);
}
////Problem 3.4
var deps = context.Departments.Where(d => d.Employees.Count > 5).Select(d => new
{
d.Name,
Manager = context.Employees.Where(e => e.EmployeeID == d.ManagerID).Select(e => new { e.LastName }),
Employees = context.Employees.Where(em => em.DepartmentID == d.DepartmentID).Select(em => new
{
em.FirstName,
em.LastName,
em.HireDate,
em.JobTitle
})
}).OrderBy(d => d.Employees.Count());
foreach (var dep in deps)
{
string Man = "";
foreach (var m in dep.Manager)
{
Man = m.LastName;
}
Console.WriteLine("-- {0} - Manager: {1}, Employees: {2}",
dep.Name,
Man,
dep.Employees.ToList().Count()
);
}
// Problem 4.1
string NativeQuery =
"SELECT e.FirstName FROM employees e " +
"INNER JOIN EmployeesProjects ep ON e.EmployeeID = ep.EmployeeID " +
"INNER JOIN Projects p ON ep.ProjectID = p.ProjectID";
var names = context.Database.SqlQuery<string>(NativeQuery).ToList();
foreach (var name in names)
{
Console.WriteLine(name);
}
//Problem 4.2
//Find all employees who have projects with start date in the year 2002. Select only their first name.
var EmpPr = context.Employees.Where(e => e.Projects
.Any(p => p.StartDate.Year == 2002))
.Select(e => e.FirstName);
foreach (var empl in EmpPr)
{
Console.WriteLine(empl);
}
//Problem 5
var context2 = new SoftUniEntities();
var Cont1Change = context.Employees.Find(1).FirstName = "gosho2";
var Cont2Change = context2.Employees.Find(1).FirstName = "ivan3";
// When the concurency control is changed to fixed it goes boom.
context.SaveChanges();
// context2.SaveChanges();
//Problem 6
var ProjectsList = context.usp_Projects4Employee("Roberto", "Tamburello");
foreach (var project in ProjectsList)
{
Console.WriteLine("Name: {0}\nDescription: {1}\nStarting Date: {2}\n",
project.Name,
project.Description,
project.StartDate);
}
}
private static void Main()
{
//Problem 1
var context = new SoftUniEntities();
//Problem 2
TestClass.TestOperations();
//Problem 3.1
var employees = context.Employees.Where(e => e.Projects
.Any(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003))
.Select(e => new
{
e.FirstName,
e.LastName,
Projects = e.Projects
.Where(p => p.StartDate.Year >= 2001 && p.StartDate.Year <= 2003)
.Select(p => new
{
p.Name,
p.StartDate,
p.EndDate
}),
ManagerName = e.Employee1.FirstName + " " + e.Employee1.LastName
});
foreach (var employee in employees)
{
Console.WriteLine("Name: {0} {1}", employee.FirstName, employee.LastName);
Console.WriteLine("\tManager: {0}", employee.ManagerName);
Console.WriteLine("\tProjects:");
foreach (var project in employee.Projects)
{
Console.WriteLine("\t\tProject Name: {0}\n\t\tStart Date:" +
" {1}\n\t\tEnd Date{2}\n",
project.Name,
project.StartDate,
project.EndDate);
}
}
//Problem 3.2
var addresses = context.Addresses.Select(a => new
{
Address = a.AddressText,
a.Town.Name,
NumberOfEmployees = a.Employees.Count()
}).OrderByDescending(a => a.NumberOfEmployees).ThenBy(a => a.Name).Take(10);
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2}",
address.Address,
address.Name,
address.NumberOfEmployees);
}
//Problem 3.3
var emp = context.Employees.Where(e => e.EmployeeID == 1).Select(e => new
{
e.FirstName,
e.LastName,
e.JobTitle,
Projects = e.Projects
});
foreach (var e in emp)
{
string prj = "";
foreach (var proj in e.Projects)
{
prj += proj.Name + "\n ";
}
Console.WriteLine("First Name: {0}\nLast Name: {1}\nJob Title: {2}\nProjects: {3}",
e.FirstName,
e.LastName,
e.JobTitle,
prj);
}
////Problem 3.4
var deps = context.Departments.Where(d => d.Employees.Count > 5).Select(d => new
{
d.Name,
Manager = context.Employees.Where(e => e.EmployeeID == d.ManagerID).Select(e => new { e.LastName }),
Employees = context.Employees.Where(em => em.DepartmentID == d.DepartmentID).Select(em => new
{
em.FirstName,
em.LastName,
em.HireDate,
em.JobTitle
})
}).OrderBy(d => d.Employees.Count());
foreach (var dep in deps)
{
string Man = "";
foreach (var m in dep.Manager)
{
Man = m.LastName;
}
Console.WriteLine("-- {0} - Manager: {1}, Employees: {2}",
dep.Name,
Man,
dep.Employees.ToList().Count()
);
}
// Problem 4.1
string NativeQuery =
"SELECT e.FirstName FROM employees e " +
"INNER JOIN EmployeesProjects ep ON e.EmployeeID = ep.EmployeeID " +
"INNER JOIN Projects p ON ep.ProjectID = p.ProjectID";
var names = context.Database.SqlQuery <string>(NativeQuery).ToList();
foreach (var name in names)
{
Console.WriteLine(name);
}
//Problem 4.2
//Find all employees who have projects with start date in the year 2002. Select only their first name.
var EmpPr = context.Employees.Where(e => e.Projects
.Any(p => p.StartDate.Year == 2002))
.Select(e => e.FirstName);
foreach (var empl in EmpPr)
{
Console.WriteLine(empl);
}
//Problem 5
var context2 = new SoftUniEntities();
var Cont1Change = context.Employees.Find(1).FirstName = "gosho2";
var Cont2Change = context2.Employees.Find(1).FirstName = "ivan3";
// When the concurency control is changed to fixed it goes boom.
context.SaveChanges();
// context2.SaveChanges();
//Problem 6
var ProjectsList = context.usp_Projects4Employee("Roberto", "Tamburello");
foreach (var project in ProjectsList)
{
Console.WriteLine("Name: {0}\nDescription: {1}\nStarting Date: {2}\n",
project.Name,
project.Description,
project.StartDate);
}
}