public static int SelectEmployeesCount()
{
SoftUniEntities northwindEntities = new SoftUniEntities();
string nativeSqlQuery = "SELECT count(*) FROM dbo.Employees";
var queryResult = northwindEntities.Database.SqlQuery<int>(nativeSqlQuery);
int customersCount = queryResult.FirstOrDefault();
return customersCount;
}
public static void UpdateProject(Project product, string newName)
{
using (SoftUniEntities northwindEntities = new SoftUniEntities())
{
northwindEntities.Projects.Attach(product); // This line is required!
product.Name = newName;
northwindEntities.SaveChanges();
}
}
public static Project GetProject(int id)
{
using (SoftUniEntities northwindEntities = new SoftUniEntities())
{
Project product = northwindEntities.Projects
.First(p => p.ProjectID == id);
return product;
}
}
public static void Main()
{
var softUniContext = new SoftUniEntities();
var customer = new Town() { TownID = 3 };
softUniContext.Entry(customer).State = EntityState.Deleted;
softUniContext.SaveChanges();
SelectFromSingleTable();
SelectFromMultipleTables();
}
private static IEnumerable<string> SelectEmployeeNamesByJobTitle(string jobTitle)
{
SoftUniEntities northwindEntities = new SoftUniEntities();
string nativeSqlQuery =
"SELECT FirstName + ' ' + LastName " +
"FROM dbo.Employees " +
"WHERE JobTitle = {0}";
object[] parameters = { jobTitle };
var employees = northwindEntities.Database.SqlQuery<string>(nativeSqlQuery, parameters);
return employees;
}
public static IEnumerable<ProjectIdAndName> SelectTop5ProjectsIdAndName()
{
SoftUniEntities northwindEntities = new SoftUniEntities();
string nativeSqlQuery =
"SELECT TOP 5 ProjectID as Id, Name as Name " +
"FROM dbo.Projects " +
"ORDER BY ProjectID";
var products =
northwindEntities.Database.SqlQuery<ProjectIdAndName>(nativeSqlQuery);
return products;
}
public static void GroupEmployeesByJobTitleExtentedMethods(SoftUniEntities softUniEntities)
{
var groupedEmployees = softUniEntities.Employees
.GroupBy(customer => customer.JobTitle);
Logger.PrintQueries(groupedEmployees);
foreach (var employeesGroup in groupedEmployees)
{
Console.WriteLine(employeesGroup.Key);
foreach (var employee in employeesGroup)
{
Console.WriteLine(employee.FirstName + " " + employee.LastName);
}
Console.WriteLine(Logger.SeparatorLine);
}
}
private static void SelectFromMultipleTables()
{
var softUniContext = new SoftUniEntities();
// Perform database SELECT from Orders joined to Customers
var employees =
from e in softUniContext.Employees
where e.Department.Name == "Engineering"
select e;
Logger.PrintQueries(employees);
foreach (var employee in employees)
{
Console.WriteLine(employee);
}
}
public static void GroupEmployeesByJobTitle(SoftUniEntities softUniEntities)
{
var groupedEmployees =
from employee in softUniEntities.Employees
group employee by employee.JobTitle;
Logger.PrintQueries(groupedEmployees);
foreach (var employeesGroup in groupedEmployees)
{
Console.WriteLine(employeesGroup.Key);
foreach (var employee in employeesGroup)
{
Console.WriteLine(employee.FirstName + " " + employee.LastName);
}
Console.WriteLine(Logger.SeparatorLine);
}
}
private static void SelectFromSingleTable()
{
var softUniContext = new SoftUniEntities();
// Select data from sinlge table
IQueryable<Employee> employees =
from c in softUniContext.Employees
where c.JobTitle == "Production Supervisor"
select c;
Logger.PrintQueries(employees);
Console.WriteLine("The query is still not generated and executed.");
foreach (var employee in employees)
{
Console.WriteLine(employee);
}
}
public static void JoinEmployeesWithDepartmentsExtentedMethods(SoftUniEntities softUniEntities)
{
var employees =
softUniEntities.Employees.Join(
softUniEntities.Departments,
employee => employee.DepartmentID,
department => department.DepartmentID,
(employee, department) => new
{
CustomerName = employee.FirstName + " " + employee.LastName,
Department = department.Name,
Salary = employee.Salary
});
Logger.PrintQueries(employees);
foreach (var employee in employees)
{
Console.WriteLine(employee);
}
}
public static void JoinEmployeesWithDepartments(SoftUniEntities softUniEntities)
{
var employees =
from employee in softUniEntities.Employees
join department in softUniEntities.Departments
on employee.DepartmentID equals department.DepartmentID
select new
{
EmployeeName = employee.FirstName + " " + employee.LastName,
Department = department.Name,
Salary = employee.Salary
};
Logger.PrintQueries(employees);
foreach (var employee in employees)
{
Console.WriteLine(employee);
}
}
public static void PlayWithDetatch()
{
SoftUniEntities softUniEntities = new SoftUniEntities();
Employee newEmployee = new Employee
{
FirstName = "Vladimir",
LastName = "Georgiev",
JobTitle = "Technical Trainer",
DepartmentID = 1,
HireDate = new DateTime(2013, 12, 16),
Salary = 10000
};
softUniEntities.Employees.Add(newEmployee);
softUniEntities.SaveChanges();
// Now the employee is stored in the database. Let's print its department
Console.WriteLine(newEmployee.Department); // prints "null"
// Find the employee by primary key --> returns the same object (unmodified)
// Still prints "null" (due to caching and identity resolution)
var employeeById = softUniEntities.Employees.Find(newEmployee.EmployeeID);
Console.WriteLine(employeeById.Department); // null (due to caching)
// Find the product by query still uses "identity resolution" (caching)
var employeeFromDb =
(from emp in softUniEntities.Employees
where emp.EmployeeID == newEmployee.EmployeeID
select emp).FirstOrDefault();
Console.WriteLine(employeeFromDb.Department); // null (due to caching)
// Detach the object from the context --> remove it from the cache
((IObjectContextAdapter)softUniEntities).ObjectContext.Detach(newEmployee);
// This change will not be tracked by the context
newEmployee.FirstName = "Vlado";
// This will make no changes in the DB (detatched objects are not tracked)
softUniEntities.SaveChanges();
// Now find the product by primary key (detached entities are not cached)
var detachedEmployee = softUniEntities.Employees.Find(newEmployee.EmployeeID);
Console.WriteLine(detachedEmployee.Department); // works (no caching)
}
public static void Main()
{
SoftUniEntities softUniEntities = new SoftUniEntities();
JoinEmployeesWithDepartments(softUniEntities);
JoinEmployeesWithDepartmentsExtentedMethods(softUniEntities);
GroupEmployeesByJobTitle(softUniEntities);
GroupEmployeesByJobTitleExtentedMethods(softUniEntities);
}