static void Main(string[] args)
{
// set database initializer
Database.SetInitializer<CompanyContext>(new CompanyContextInitializer());
// initialise EF Profiler
//HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
CompanyContext db = new CompanyContext();
#region COMPLEX TYPE
var query = db.Employees // don't need to Include Address as it is stored in same table and loaded with entity
.ToList();
#endregion
#region ENTITY
//var query = db.Employees // need to Include Address as it is stored in separate table
// //.Include(e => e.Address)
// .ToList();
#endregion
#region TABLE SPLITTING
//var query = db.Employees
// .Include(e => e.Address)
// //.Include(e => e.Address.PostCode) // need to Include Address.PostCode as postcode is not loaded even though it is in same table
// .ToList();
#endregion
#region ENTITY SPLITTING
//var query = db.Employees
// .Include(e => e.Address)
// .ToList();
#endregion
#region INHERITANCE
//var query = db.Employees
// //var query = db.SalariedEmployees
// //.OfType<SalariedEmployee>()
// .Include(e => e.Address)
// .ToList();
#endregion
#region END
// view DDL Script
string script = ((IObjectContextAdapter)db).ObjectContext.CreateDatabaseScript();
// Get EDMX
XmlWriterSettings settings = new XmlWriterSettings();
settings.Indent = true;
using (XmlWriter writer = XmlWriter.Create(@"Model.edmx", settings))
{
EdmxWriter.WriteEdmx(db, writer);
}
Console.ReadLine();
#endregion
}
static void Main(string[] args)
{
// set database initializer
Database.SetInitializer<CompanyContext>(new CompanyContextInitializer());
// initialise EF Profiler
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
CompanyContext db = new CompanyContext();
#region EMPLOYEE SELF-RELATIONSHIPS
//var query = db.Employees
// .Include(e => e.Address)
// .Include(e => e.Supervisor)
// .Include(e => e.Buddies)
// .ToList();
#endregion
#region ONE-TO-ONE
//var query = db.Employees
// .Include(e => e.Address)
// .ToList();
//var lefts = db.Lefts
// .Include(l => l.Right)
// .ToList();
//var rights = db.Rights
// .Include(r => r.Left)
// .ToList();
#endregion
#region ONE-TO-MANY
var query = db.Departments
.Include(d => d.Employees)
.ToList();
var depToDelete = db.Departments.Find(1);
db.Departments.Remove(depToDelete);
db.SaveChanges();
var newQuery = db.Employees
.ToList();
#endregion
#region MANY-TO-MANY JOIN TABLE
//var query = db.Employees
// .Include(e => e.Projects)
// .ToList();
#endregion
#region END
string script = ((IObjectContextAdapter)db).ObjectContext.CreateDatabaseScript();
Console.ReadLine();
#endregion
}
static void Main(string[] args)
{
// set database initializer
Database.SetInitializer<CompanyContext>(new CompanyContextInitializer());
CompanyContext db = new CompanyContext();
db.Configuration.LazyLoadingEnabled = false;
db.Configuration.ProxyCreationEnabled = true;
db.Configuration.AutoDetectChangesEnabled = false;
// new entity
var newEmp = new Employee { Name = "Kimi" };
var newEmpEntity = db.Entry(newEmp);
Console.WriteLine("NEW ENTITY NOT ATTACHED");
Console.WriteLine(string.Format("Employee {0}, state = {1}", newEmp.Name, newEmpEntity.State));
PrintTrackedEmployees(db);
// attach new entity
db.Entry(newEmp).State = System.Data.EntityState.Added;
Console.WriteLine("\nNEW ENTITY ATTACHED");
PrintTrackedEmployees(db);
#region GET DATA
// get some objects, eager load so we can access all necessary properties with LL disabled
var jenson = db.Employees
.Include(e => e.Projects)
.Include(e => e.Department)
.Where(e => e.EmployeeId==1)
.First();
var fernando = db.Employees
.Include(e => e.Projects)
.Include(e => e.Department)
.Where(e => e.EmployeeId == 3)
.First();
var marketing = db.Departments
.Include(d => d.Employees)
.Where(d => d.DepartmentId == 1)
.First();
var sales = db.Departments
.Include(d => d.Employees)
.Where(d => d.DepartmentId == 2)
.First();
var adCampaign = db.Projects
.Include(p => p.Employees)
.Where(p => p.ProjectId == 1)
.First();
var website = db.Projects
.Include(p => p.Employees)
.Where(p => p.ProjectId == 2)
.First();
#endregion
Console.WriteLine("\nRETRIEVED FROM DATABASE");
PrintDetails(jenson);
PrintTrackedEmployees(db);
// change Department for Employee
jenson.Department = sales; // was marketing
// jenson in sales, sales has 4 employees including jenson
db.ChangeTracker.DetectChanges(); // will fix-up always
Console.WriteLine("\nJENSON CHANGED DEPARTMENT TO SALES");
PrintDetails(jenson);
// remove Employee from Project and add Project to Employee
jenson.Projects.Remove(adCampaign); // was on adcampaign and website
fernando.Projects.Add(website); // was not on any projects
// jenson on website only, website includes fernando
db.Employees.Add(new Employee { Name = "new" }); // will fix-up if AutoDetectChangesEnabled
//db.ChangeTracker.DetectChanges();
Console.WriteLine("\nJENSON REMOVED ADCAMPAIGN, FERNANDO ADDED WEBSITE");
PrintDetails(jenson);
marketing.Employees.Add(jenson); // was in sales
// jenson in marketing, marketing has 2 employees including jenson
db.SaveChanges(); // will fix-up if AutoDetectChangesEnabled
//db.ChangeTracker.DetectChanges();
Console.WriteLine("\nMARKETING ADDED JENSON");
PrintDetails(jenson);
Console.WriteLine("Press any key to finish...");
Console.ReadLine();
}
static void Main(string[] args)
{
// set database initializer
Database.SetInitializer<CompanyContext>(new CompanyContextInitializer());
// initialise EF Profiler
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
CompanyContext db = new CompanyContext();
// Create Address objects
Address ad1 = new Address
{
AddressId = 3,
PropertyName = "LINQ Tower",
PropertyNumber = 99,
PostCode = "G1 1XX"
};
Address ad2 = new Address
{
AddressId = 4,
PropertyName = "EF House",
PropertyNumber = 101,
PostCode = "G4 0XX"
};
// Create Employee objects and associate with Addresses
Employee emp1 = new Employee
{
Name = "Fernando",
Username = "******",
PhoneNumber = "1234",
Address = ad1
};
SalariedEmployee emp2 = new SalariedEmployee
{
Name = "Felipe",
Username = "******",
PhoneNumber = "5678",
Address = ad2,
DepartmentName = "Sales", // set FK property
PayGrade = 3
};
// Create Department object
Department dep1 = new Department
{
DepartmentName = "Sales"
};
// Add Employee objects to Department
dep1.Employees.Add(emp1);
//dep1.Employees.Add(emp2);
db.Departments.Add(dep1);
//db.Employees.Add(emp1);
db.Employees.Add(emp2);
db.SaveChanges();
// dispose context and open a new context
db.Dispose();
db = new CompanyContext();
// reload entity from database
//db.Entry(dep1).Reload();
db.Configuration.LazyLoadingEnabled = false;
db.Configuration.ProxyCreationEnabled = false;
List<Employee> employees = db.Employees
.Include(e => e.Address)
.Include(e => e.Department)
.ToList<Employee>();
Address address = employees[0].Address;
var query = from d in db.Departments
where d.DepartmentName == "Marketing"
select d;
//string command = query.ToString();
//Console.WriteLine(command + "\n");
Department department = query.FirstOrDefault();
// set break point here and inspect objects with debugger
Console.WriteLine("Press any key to finish...");
Console.ReadLine();
}
static void Main(string[] args)
{
// set database initializer
Database.SetInitializer<CompanyContext>(new CompanyContextInitializer());
// initialise EF Profiler
HibernatingRhinos.Profiler.Appender.EntityFramework.EntityFrameworkProfiler.Initialize();
CompanyContext db = new CompanyContext();
#region QUERIES AND EXPRESSIONS
var query = from d in db.Departments
where d.DepartmentName == "Marketing" && d.Employees.Count() > 0
select d;
var result = query.ToList();
var singleQuery = db.Departments
.Where(d => d.DepartmentName == "Marketing")
.Single();
var extensionsQuery = db.Departments
.Where(d => d.DepartmentName == "Marketing" && d.Employees.Count() > 0)
.ToList();
var projectQuery = db.Departments
.Where(d => d.DepartmentName == "Marketing" && d.Employees.Count() > 0)
.SelectMany(d => d.Employees, (d,e) => new{ Name = e.Name })
.ToList();
Expression<Func<Department, bool>> filter1 =
d => d.DepartmentName == "Marketing";
Expression<Func<Department, bool>> filter2 =
d => d.Employees.Count() > 0;
var filterQuery = db.Departments
.Where(filter1)
.Where(filter2)
.Select(d => d.Employees)
.ToList();
#endregion
#region QUERY OBJECT
//var employeesWithPostCode = new EmployeesWithPostCodeQuery
//{
// PostCode = "G1 1XX",
//};
//var query = db.Employees
// .Where(employeesWithPostCode.AsExpression());
//var result = query.ToList();
#endregion
#region COMPOSING QUERIES
//var employeesInDepartment = new EmployeesInDepartmentQuery
//{
// DepartmentName = "Marketing",
//};
//var employeesWithPostCode = new EmployeesWithPostCodeQuery
//{
// PostCode = "G1 1XX"
//};
//var exactlyTheEmployeesWeWant = PredicateBuilder
// .False<Employee>()
// .Or(employeesInDepartment.AsExpression())
// .And(employeesWithPostCode.AsExpression());
//var query = db.Employees.AsExpandable()
// .Where(exactlyTheEmployeesWeWant);
//var result = query.ToList();
#endregion
#region COMPOSED QUERY OBJECT
//var exactlyTheEmployeesWeWant = new CustomEmployeeQuery
//{
// PostCode = "G1 1XX",
// DepartmentName = "Marketing"
//}.AsExpression();
//var query = db.Employees.AsExpandable()
// .Where(exactlyTheEmployeesWeWant);
//var result = query.ToList();
#endregion
#region LINQ TO ENTITIES QUERY FETCH STRATEGY
//var query = db.Departments
// .Include(d => d.Employees.Select(e => e.Address));
////var query = (from d in db.Departments
//// select d)
//// .Include(d => d.Employees)
//var result = query.ToList();
#endregion
#region TRANSACTIONS
//CompanyContext db2 = new CompanyContext(); // second context for multicontext transactions
//Address goodAddress = new Address
//{
// AddressId = 5,
// PropertyName = "Persistence Centre",
// PropertyNumber = 101,
// PostCode = "G4 2QQ"
//};
//Address badAddress = new Address
//{
// AddressId = 6,
// PropertyName = "DbContext Campus"
//};
//var scope = new
// TransactionScope(TransactionScopeOption.RequiresNew,
// new TransactionOptions()
// {
// IsolationLevel = IsolationLevel.ReadUncommitted
// });
//try
//{
// using (scope)
// {
// db.Addresses.Add(goodAddress);
// db.SaveChanges();
// db2.Addresses.Add(badAddress);
// db2.SaveChanges();
// }
//}
//catch (Exception e)
//{
// Console.WriteLine(e.Message);
//}
//var add = db.Addresses.ToList();
#endregion
#region FIND
////find by PK
//Employee emp = db.Employees.Find(3);
//// find by composite PK
//Address addr = db.Addresses.Find(1, "G2 1YY");
//// find again - returns same instance without hitting database
//Employee empAgain = db.Employees.Find(3);
//// add object to context and find, even though it is not in database yet
//db.Departments.Add(new Department { DepartmentName = "Purchasing" });
//Department dep2 = db.Departments.Find("Purchasing");
//// alternative way of accessing a DbSet
//var add = db.Set<Address>().ToList();
#endregion
#region RAW SQL
//var entityResult = db.Departments.SqlQuery(
// @"SELECT * FROM Departments WHERE DepartmentName='Marketing'")
// .ToList();
//var stringResult = db.Database.SqlQuery<string>(
// @"SELECT DepartmentName FROM Departments")
// .ToList();
//db.Database.ExecuteSqlCommand(
// @"UPDATE Locations SET BuildingName = 'SQL Ranch' WHERE AddressId = 1");
//var storedProcResult = db.Departments.SqlQuery(
// "dbo.GetDeptByName @p0", "Marketing");
#endregion
#region UNDERLYING OBJECTCONTEXT
//var ctx = ((IObjectContextAdapter)db).ObjectContext;
//ctx.DeleteDatabase();
#endregion
#region END
Console.ReadLine();
#endregion
}
