public static void Main()
{
var firstContext = new SoftUniEntities();
var secondContext = new SoftUniEntities();
var firstEmployee = firstContext.Employees.First();
var secondEmployee = secondContext.Employees.First();
firstEmployee.FirstName = "Petar";
secondEmployee.FirstName = "Lazar";
firstContext.SaveChanges();
secondContext.SaveChanges();
// Check final result
var context = new SoftUniEntities();
Console.WriteLine(context.Employees.First().FirstName);
/* To change Concurency Mode for column FirstName open DbContext.edmx, and in the data model
* designer, right-click the FirstName property in the Employees entity and then click Properties.
* In the Properties window, change the ConcurrencyMode property to Fixed.*/
/* When Concurrency Mode is set to "None" the FirstName will be "Lazar" after both changes.
* In other words Last write wins.
* When the mode is set to "Fixed" the FirstName will be "Petar".
* In other words the row is locked until the first context save the changes.*/
}
public static void Main()
{
var context = new SoftUniEntities();
var newEmployee = new Employee
{
FirstName = "Ivan",
LastName = "Ivanov",
JobTitle = "Database administrator",
DepartmentID = context.Departments
.Where(d => d.Name == "Research and Development")
.Select(a => a.DepartmentID)
.First(),
HireDate = DateTime.Now,
Salary = 15000
};
Console.WriteLine("1. Add new employee");
Console.WriteLine(" -- {0} with Id: {1}", DataAccessObject.Add(newEmployee), newEmployee.EmployeeID);
Console.WriteLine("2. Find employee by key");
var employee = DataAccessObject.FindByKey(newEmployee.EmployeeID);
Console.WriteLine(" -- {0} {1}", employee.FirstName, employee.LastName);
Console.WriteLine("3. Edit employee first name");
employee.FirstName = "Todor";
Console.WriteLine(" -- " + DataAccessObject.Modify(employee));
Console.WriteLine(" -- New name: {0} {1}", employee.FirstName, employee.LastName);
Console.WriteLine("4. Delete employee");
Console.WriteLine(" -- {0}", DataAccessObject.Delete(employee));
}
public static void ProjectsByStartYearRange(int startYearLowerRange, int startYearUpperRange)
{
var context = new SoftUniEntities();
var employees = context.Employees
.Select(e => new
{
e.FirstName,
e.LastName,
Manager = e.Employee1.FirstName,
e.Projects
})
.Where(e => e.Projects.Any(p => p.StartDate.Year >= startYearLowerRange && p.StartDate.Year <= startYearUpperRange))
.OrderBy(e => e.FirstName);
foreach (var e in employees)
{
Console.WriteLine(new string('*', 60));
Console.WriteLine("{0} {1} (Maneger: {2})\nProjects:", e.FirstName, e.LastName, e.Manager);
foreach (var p in e.Projects)
{
Console.WriteLine(" {0} - {1} : {2}",
p.Name,
p.StartDate.ToString("dd-MM-yyyy"),
p.EndDate.HasValue ? p.EndDate.Value.ToString("dd-MM-yyyy") : "not finished");
}
}
}
static void Main(string[] args)
{
var context = new SoftUniEntities();
var totalCount = context.Employees.Count();
var sw = new Stopwatch();
sw.Start();
PrintNamesWithNativeQuery();
Console.WriteLine("\nNative: {0}\n\n", sw.Elapsed);
sw.Restart();
PrintNamesWithLinqQuery_LINQtoEntities();
Console.WriteLine("\n\nLINQtoEntities: {0}\n\n", sw.Elapsed);
sw.Restart();
PrintNamesWithLinqQuery_WithExtensionMethods();
Console.WriteLine("\n\nLINQWithExtensionMethods: {0}", sw.Elapsed);
sw.Stop();
}
public static void Delete(Employee employee)
{
using (var ctx = new SoftUniEntities())
{
ctx.Entry(employee).State = EntityState.Deleted;
ctx.SaveChanges();
}
}
public static void Delete(Employee employee)
{
var context = new SoftUniEntities();
var deletedEmp = context.Employees.Remove(employee);
var name = deletedEmp.FirstName == "Guy";
context.SaveChanges();
Console.WriteLine("The deleted employee's name is {0}", name);
}
static void Main()
{
var context = new SoftUniEntities();
Add(new Employee());
FindByKey(10);
Modify(new Employee());
Delete(new Employee());
}
public static Employee FindByKey(int key)
{
var context = new SoftUniEntities();
var employee = context.Employees
.FirstOrDefault(e=>e.EmployeeID == key);
Console.WriteLine("The empployee's ID is {0}", employee.EmployeeID);
return employee;
}
public static void Modify(Employee employee)
{
var context = new SoftUniEntities();
var foundEmp = context.Employees.Find(3);
foundEmp.FirstName = "Vyara";
foundEmp.LastName = "Georgieva";
context.SaveChanges();
Console.WriteLine("The employee's with Id {0} name now is {1} {2}",foundEmp.EmployeeID, foundEmp.FirstName, foundEmp.LastName);
}
public static Employee FindByKey(object key)
{
using (var context = new SoftUniEntities())
{
var employee = context.Employees.Find(key);
return employee;
}
}
public static Employee FindByKey(object key)
{
using (var ctx = new SoftUniEntities())
{
Employee employee = ctx.Employees.Find(key);
return employee;
}
}
public static string Add(Employee employee)
{
using (var context = new SoftUniEntities())
{
context.Employees.Add(employee);
context.SaveChanges();
return "Employee added";
}
}
public static string Modify(Employee employee)
{
using (var context = new SoftUniEntities())
{
var original = context.Employees.Find(employee.EmployeeID);
context.Entry(original).CurrentValues.SetValues(employee);
context.SaveChanges();
return "Employee modified";
}
}
//a class for inserting, finding by key, modifying and deleting an entity
public static void Add(Employee employee)
{
using (var ctx = new SoftUniEntities())
{
var entry = ctx.Entry(employee);
entry.State = EntityState.Added;
ctx.Employees.Add(employee);
ctx.SaveChanges();
}
}
public static void PrintNamesWithLinqQuery()
{
var context = new SoftUniEntities();
var query = context.Employees
.Where(e => e.Projects.Any(p => p.StartDate.Year == 2002))
.Select(e => e.FirstName);
var employees = query.ToList();
Console.WriteLine(string.Join(" | ", employees));
}
public static void PrintNamesWithLinqQuery(int year)
{
var context = new SoftUniEntities();
var employees = context.Employees
.Where(e => e.Projects.Any(p => p.StartDate.Year == year))
.OrderBy(e => e.FirstName)
.Select(e => e.FirstName)
.Distinct();
Console.WriteLine("Linq count: {0}", employees.Count());
//Console.WriteLine(string.Join("\n", employees));
}
static void Main()
{
var context1 = new SoftUniEntities();
var employee1 = context1.Employees.Find(1);
employee1.FirstName = "Gegata";
var context2 = new SoftUniEntities();
var employee2 = context2.Employees.Find(2);
employee2.FirstName = "Manaf";
context1.SaveChanges();
context2.SaveChanges();
}
private static void PrintNamesWithLinqQuery_WithExtensionMethods()
{
using (var ctx = new SoftUniEntities())
{
var queryResult = ctx.Employees
.Where(emp => emp.Projects.Any(p => p.StartDate.Year == 2002))
.Select(emp => emp.FirstName);
var employees = queryResult.ToList();
Console.WriteLine(string.Join(" | ", employees));
}
}
private static void PrintNamesWithLinqQuery_LINQtoEntities()
{
using(var ctx = new SoftUniEntities())
{
var queryResult = (from e in ctx.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 Add(Employee employee)
{
var context = new SoftUniEntities();
context.Employees.Add(employee);
employee.FirstName = "Peter";
employee.LastName = "Petrov";
employee.JobTitle = "Senior JS";
employee.DepartmentID = 2;
employee.HireDate = DateTime.Now;
employee.Salary = 60000;
context.SaveChanges();
Console.WriteLine("Employee added: First Name: {0}, Last Name {1}", employee.FirstName, employee.LastName);
}
private static void PrintNamesWithLinqQuery(SoftUniEntities context)
{
var emplyeesByProject = context.Employees
.Where(e => e.Projects
.Any(p => p.StartDate >= new DateTime(2002, 1, 1) &&
p.StartDate <= new DateTime(2002, 12, 31)))
.OrderBy(e => e.FirstName)
.Select(e => new
{
EmpName = e.FirstName + " " + e.LastName
})
.ToList();
Console.WriteLine(emplyeesByProject.Count);
}
public static void Add(Employee employee)
{
var context = new SoftUniEntities();
context.Employees.Add(employee);
employee.FirstName = "Ivo";
employee.LastName = "Lekov";
employee.JobTitle = "SMD";
employee.DepartmentID = 2;
employee.HireDate = DateTime.Now;
employee.Salary = 60000;
context.SaveChanges();
Console.WriteLine($"Employee added: {employee.LastName}, {employee.FirstName}");
}
private static void PrintNamesNativeQuery(SoftUniEntities context)
{
var employees = context.Employees.SqlQuery(@"
SELECT *
FROM Employees e
JOIN EmployeesProjects ep
ON e.EmployeeID = ep.EmployeeID
JOIN Projects p
ON p.ProjectID = ep.ProjectID
WHERE p.StartDate >= '2002-1-1' AND p.StartDate <= '2002-12-31'
").ToList();
var employeesCount = employees.Count;
Console.WriteLine(employeesCount);
}
static void Main(string[] args)
{
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()
{
var context = new SoftUniEntities();
var employee = context.Employees.Find(1);
var newName = Console.ReadLine();
employee.FirstName = newName;
Console.ReadLine();
context.SaveChanges();
/* When concurrency mode is not fixed the second record is
* set in the database over the first change.
* When it is fixed an exception is raised - DbUpdateConcurrencyException
*/
}
public static void PrintNamesWithNativeQuery()
{
var context = new SoftUniEntities();
string query =
"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>(query);
List<string> employees = queryResult.ToList();
Console.WriteLine(string.Join(" | ", employees));
}
static void Main(string[] args)
{
var context = new SoftUniEntities();
Employee newEmployee = new Employee
{
FirstName = "Goshko",
LastName = "Dendimonov",
JobTitle = "Gamer",
DepartmentID = 2,
HireDate = DateTime.Now,
Salary = 10000
};
DAOClass.Add(newEmployee);
var primaryKey = newEmployee.EmployeeID;
Console.WriteLine("{0}'s ID Key is {1}", newEmployee.FirstName, primaryKey);
DAOClass.Modify(newEmployee, "Toshko");
Console.WriteLine("{0}'s ID Key is {1}", newEmployee.FirstName, primaryKey);
DAOClass.Delete(newEmployee);
}
static void Main()
{
var context = new SoftUniEntities();
var totalCount = context.Employees.Count();
var sw = new Stopwatch();
sw.Start();
PrintNamesWithNativeQuery();
Console.WriteLine("Native: {0}",sw.Elapsed);
sw.Reset();
PrintNamesWithLinqQuery();
Console.WriteLine("Linq: {0}",sw.Elapsed);
var emp = context.Employees.Where(e => e.LastName == "Gilbert").First();
emp.FirstName = "Guy";
context.SaveChanges();
}
public static string Delete(Employee employee)
{
using (var context = new SoftUniEntities())
{
//var itemToRemove = context.Employees.SingleOrDefault(e => e.EmployeeID == employee.EmployeeID);
context.Entry(employee).State = EntityState.Deleted;
context.SaveChanges();
//
// if (itemToRemove != null)
// {
// context.Employees.Remove(itemToRemove);
// context.SaveChanges();
return "Employee deleted";
//}
return "Delete failed!";
}
}
public static void PrintNamesWithNativeQuery(int year)
{
var context = new SoftUniEntities();
const string query = @"SELECT
e.FirstName
FROM Employees AS e
JOIN EmployeesProjects AS ep
ON e.EmployeeID = ep.EmployeeID
JOIN Projects AS p
ON ep.ProjectID = p.ProjectID
WHERE YEAR(p.StartDate) = {0}
GROUP BY e.FirstName
HAVING COUNT(p.ProjectID) > 0
ORDER BY e.FirstName";
var employees = context.Database.SqlQuery<string>(query, year);
Console.WriteLine("Navite count: {0}", employees.Count());
//Console.WriteLine(string.Join("\n", employees));
}
public static void AddressesByEmployeesCount()
{
var context = new SoftUniEntities();
var addresses = context.Addresses
.Select(a => new {
Address = a.AddressText,
Town = a.Town.Name,
EmployeeCount = a.Employees.Count
})
.OrderByDescending(a => a.EmployeeCount)
.ThenBy(a => a.Town)
.Take(10);
foreach (var address in addresses)
{
Console.WriteLine("{0}, {1} - {2} employees",
address.Address,
address.Town,
address.EmployeeCount);
}
}
static void Main(string[] args)
{
var context = new SoftUniEntities();
Add(new Employee());
}