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 }