static void PrintEmployeesGroupedByDeparmtmentAndTown(TelerikAcademyEntities db) { var groups = db.Employees .OrderBy(e => e.FirstName) .ThenBy(e => e.LastName) .GroupBy(e => new { Department = e.Department.Name, Town = e.Address.Town.Name }) .OrderBy(g => new { Count = -g.Count(), g.Key.Department, g.Key.Town }) .ToList(); foreach (var departmentGroup in groups) { Console.WriteLine("Department: {0}\nTown: {1}\nEmployees: {2}", departmentGroup.Key.Department, departmentGroup.Key.Town, departmentGroup.Count()); Console.WriteLine("====================\n"); var sortedDepartmentGroup = departmentGroup //.OrderBy(e => e.FirstName) .ToList(); foreach (var employee in sortedDepartmentGroup) { Console.WriteLine("{0} {1}",employee.FirstName, employee.LastName); //Console.WriteLine("{0} {1} - Bonus: {2}", employee.FirstName, employee.LastName); } Console.WriteLine("\n\n"); } }
static void Main() { using(var db = new TelerikAcademyEntities()) { PrintEmployeesGroupedByDeparmtmentAndTown(db); //PrintEmployeesGroupedByDeparmtmentAndTown2(db); } }
static void PrintEmployeesGroupedByDeparmtmentAndTown2(TelerikAcademyEntities db) { var employees = db.Employees .Select(e =>new { FirstName = e.FirstName, LastName = e.LastName, Department = e.Department.Name, Address = e.Address.AddressText, Town = e.Address.Town.Name, Projects = e.Projects.Count }) .Where(e => e.Projects < 4 ) .OrderBy(e => e.Projects) .ToList(); foreach (var employee in employees) { Console.WriteLine("=============\nName: {0} {1}\nDepartment: {2}\nAddress: {3}, {4}\nProjects: {5}\n", employee.FirstName,employee.LastName,employee.Department,employee.Address,employee.Town, employee.Projects); } }
static void Main(string[] args) { var db = new TelerikAcademyEntities(); using (db) { //db.Database.ExecuteSqlCommand("SELECT * FROM Employees"); ////// select all towns ////var allTowns = db.Towns.ToList(); ////foreach (var town in allTowns) ////{ //// Console.WriteLine(town.Name); ////} //var addressess = db.Addresses.Where(a => a.AddressText.StartsWith("1")); //foreach (var address in addressess) //{ // Console.WriteLine(address.AddressText); //} //var addresses = db.Addresses // .Where(a => a.AddressText.StartsWith("2")) // .OrderBy(a => a.AddressText) // .Select(a => // new // { // TownName = a.Town.Name, // AddressText = a.AddressText // }); //foreach (var address in addresses) //{ // Console.WriteLine(address.AddressText + " : " + address.TownName); //} //var address = db.Addresses.Find(3); //Console.WriteLine(address.AddressText); //address = db.Addresses // .Where(a => a.AddressID == 3) // .FirstOrDefault(); //Console.WriteLine(address.AddressText); //address = db.Addresses // .FirstOrDefault(a => a.AddressID == 3); //Console.WriteLine(address.AddressText); //var address = db.Addresses // .OrderBy(a => a.AddressText) // .ThenBy(a => a.Employees.Count()) // .Select(a => // new // { // Employess = a.Employees // .AsQueryable() // .Where(e => e.DepartmentID == 15) // }); //Console.WriteLine(address); //var town = new Town //{ // Name = "PlovdiV" //}; //db.Towns.Add(town); //db.SaveChanges(); //var savedTown = db.Towns.FirstOrDefault(t => t.Name == "PlovdiV"); //var address = new Address //{ // AddressText = "Bul Bulgaria", // Town = savedTown //}; //db.Addresses.Add(address); //db.SaveChanges(); ////db.Addresses.Add(new Address //// { //// AddressText = "Ivan", //// Town = new Town //// { //// Name = "Dimitrovgrad" //// } //// }); ////db.SaveChanges(); //var town = db.Towns.FirstOrDefault(t => t.Name == "PlovdiV"); //town.Name = "PLOVDIV"; //db.SaveChanges(); //////var towns = db.Towns.Where(t => t.Name.StartsWith("N")); //////foreach (var currentTown in towns) //////{ ////// currentTown.Name = "VARNA"; //////} //////db.SaveChanges(); //////// partial class Project in folder Partials //////db.Projects.First().TimeSinceBeginning(); //var result = db.Database.SqlQuery<Town>("SELECT * FROM Towns"); //foreach (var town in result) //{ // Console.WriteLine(town.Name); //} //var result = db.Database.SqlQuery<int>("SELECT Count(*) FROM Towns"); //Console.WriteLine(result.FirstOrDefault()); //////var queryFormat = string.Format("SELECT * FROM {0}", "Towns"); //////var result = db.Database.SqlQuery<Town>(queryFormat); //////foreach (var t in result) //////{ ////// Console.WriteLine(t.Name); //////} //var addresses = db.Addresses.Join( // db.Towns, // a => a.TownID, // t => t.TownID, // (a, t) => new // { // Address = a.AddressText, // Town = t.Name // }); //foreach (var ad in addresses) //{ // Console.WriteLine(ad.Address + " " + ad.Town); //} //////var groupedByDep = db.Employees.GroupBy(e => e.DepartmentID); //////foreach (var group in groupedByDep) //////{ ////// Console.WriteLine("+" + group.First().Department.Name + "+"); ////// foreach (var emp in group) ////// { ////// Console.WriteLine(emp.FirstName); ////// } //////} // DETACHE OR MODIFY //var someTown = db.Towns.FirstOrDefault(t => t.Name == "SOFIA"); //var townEntry = db.Entry(someTown); //townEntry.State = EntityState.Detached; //someTown.Name = "PLOVDIV"; //townEntry.State = EntityState.Modified; //db.SaveChanges(); ////var townToAdd = new Town ////{ //// Name = "BURGAS" ////}; ////db.Entry(townToAdd).State = EntityState.Added; ////db.SaveChanges(); } }
static void Main() { //селектира от таблицата това, което е с ID=1 using (var db = new TelerikAcademyEntities()) { var project = db.Projects .Where(pr => pr.ProjectID == 1) .FirstOrDefault(); Console.WriteLine(project.Name); var projectCount = db.Projects.ToList(); Console.WriteLine(projectCount.Count); } using (var db = new TelerikAcademyEntities()) { var project = db.Projects.Find(2); Console.WriteLine(project); } //Create, Update, Delete using Entity Framework //Add using (var db = new TelerikAcademyEntities()) { var town = new Town { Name = "London" }; var address = new Address { AddressText = "Mladost 4", Town = town }; //db.Towns.Add(town); db.Addresses.Add(address); db.SaveChanges(); } //Delete using (var db = new TelerikAcademyEntities()) { //Add some town var town = new Town { Name = "Town going to be deleted" }; db.Towns.Add(town); db.SaveChanges(); //Remove var townToDelete = db.Towns.Where(t => t.Name.Contains("deleted")).FirstOrDefault(); db.Towns.Remove(townToDelete); db.SaveChanges(); } //view all information - бърз начин с .Select(t => new {..}), което прави //една заявка с join и е много по-добре от към performance using (var db = new TelerikAcademyEntities()) { var towns = db.Towns .Where(t => t.Addresses.Any()) .Select(t => new { t.Name, Addresses = t.Addresses.Select(a => a.AddressText) }) .ToList(); foreach (var town in towns) { Console.WriteLine(town.Name); foreach (var address in town.Addresses) { Console.WriteLine(address); } Console.WriteLine("-----------------------"); } } //view all information - тромав начин прави много заявки и прави същото, като горната заявкабн using (var db = new TelerikAcademyEntities()) { var towns = db.Towns.Where(t => t.Addresses.Any()).ToList(); Console.WriteLine(towns.Count); foreach (var town in towns) { Console.WriteLine(town.Name); var addresses = town.Addresses; foreach (var address in addresses) { Console.WriteLine(address.AddressText); } Console.WriteLine("-----------------------"); } } //use Extended partial class, to add new functionallity using (var db = new TelerikAcademyEntities()) { var employee = db.Employees.ToList().Select(e => e.FullName); foreach (var empl in employee) { Console.WriteLine(empl); } } var dbo = new TelerikAcademyEntities(); //Executing Native SQL Queries var empls = dbo.Database.SqlQuery(typeof(Project), "SELECT * FROM dbo.Projects"); //Joining Tables in EF //чрез анонимен обект dbo.Towns .Select(t => new { Name = t.Name, Address = t.Addresses.Select(a => a.AddressText) }); //или може да е мапнат към конкретен клас var result = dbo.Towns .Select(t => new TownDataModel { Name = t.Name, Address = t.Addresses.Select(a => a.AddressText) }); Console.WriteLine(result); //when we use JOIN var secondResult = dbo.Towns .Join( dbo.Addresses, t => t.TownID, a => a.TownID, (t, a) => new { TownName = t.Name, AddressTexts = a.AddressText }) .ToList(); //Join with linq var employe = dbo.Employees .Select(e => new { FullName = e.FirstName + " " + e.LastName, Town = e.Address.Town.Name, Address = e.Address.AddressText, Project = e.Projects.Select(pr => pr.Name).FirstOrDefault(), Department = e.Department.Name }); Console.WriteLine(employe); //Group var emplGroups = dbo.Employees .GroupBy(e => e.Department.Name) .ToList(); foreach (var empplGr in emplGroups) { Console.WriteLine(empplGr.Key); foreach (var emp in empplGr) { Console.WriteLine(emp.FirstName); } Console.WriteLine("============================"); } //Groupby more than 1 thing var emplsGroups = dbo.Employees .GroupBy(e => new { e.Department.Name, TownName = e.Address.Town.Name }) .ToList(); //UPDATE // за целта трябва да се ползва оригиналния обект var updateEmpl = dbo.Employees.FirstOrDefault(); //може да правим колкото искаме промени updateEmpl.FirstName = "Pesho"; dbo.SaveChanges(); //Attaching Detached Objects var em = dbo.Employees.FirstOrDefault(); var dbEntry = dbo.Entry(em); //нов обект, токущо добавен dbEntry.State = EntityState.Added; //изтрии този обект dbEntry.State = EntityState.Deleted; //спри да се занимаваш с този обект dbEntry.State = EntityState.Detached; //променен този обект dbEntry.State = EntityState.Modified; //непроменен обект, скипни промените и го следи отново dbEntry.State = EntityState.Unchanged; dbo.SaveChanges(); //Attaching - ръчно //ако имаме праймъри кй, то тогава променяме наличното в базата //ако нямаме добавяме нов обект var e = new Employee { EmployeeID = 5, JobTitle = "Dev" }; dbo.Employees.Attach(e); }