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); }