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