Always disable lazy loading.
- The property in question must be marked ‘public virtual.’
- The DbContext’s ‘Configuration.LazyLoadingEnabled’ must be set to true.
public class OrganizationDbContext : DbContext { public OrganizationDbContext() : base("name=OrganizationDbContext") { //Uncomment this line to disable lazy loading //Configuration.LazyLoadingEnabled = false; } public virtual DbSet<Organization> Organizations { get; set; } public virtual DbSet<Department> Departments { get; set; } public virtual DbSet<Employee> Employees { get; set; } public virtual DbSet<Log> Logs { get; set; } } public class Organization { public int Id { get; set; } public string Name { get; set; } public virtual ICollection<Department> Departments { get; set; } //navigation property (note the 'virtual') } public class Department { public int Id { get; set; } public string Name { get; set; } [ForeignKey("Organization")] public int OrganizationId { get; set; } public virtual Organization Organization { get; set; } //navigation property (note the 'virtual') public virtual ICollection<Employee> Employees { get; set; } //navigation property (note the 'virtual') } public class Employee { public int Id { get; set; } public string Name { get; set; } public bool Salaried { get; set; } [ForeignKey("Department")] public int DepartmentId { get; set; } public virtual Department Department { get; set; } //navigation property (note the 'virtual') } public class Log { public int Id { get; set; } public string Severity { get; set; } public string Message { get; set; } }
public class OrganizationRepository { private readonly OrganizationDbContext _context; public OrganizationRepository(OrganizationDbContext context) { this._context = context; } public string GetDepartmentNameForEmployee(int id) { Employee emp = _context.Employees.Find(id); //SQL query executed here return emp.Department.Name; //SQL query also executed here since 'Department' was accessed } }
public class OrganizationRepository { private readonly OrganizationDbContext _context; public OrganizationRepository(OrganizationDbContext context) { this._context = context; } public string GetDeparmentNameForSalariedEmployee(int id) { Employee emp = _context.Employees.Find(id);//1. SQL query executed here if (emp.Salaried) return emp.Department.Name;//2. SQL query executed here since 'Department' was accessed else throw new InvalidOperationException("Employee is not salaried");//query '2' above was skipped (hooray!) } }
public class OrganizationRepository { private readonly OrganizationDbContext _context; public OrganizationRepository(OrganizationDbContext context) { this._context = context; } public List<string> GetDepartmentsWithSalariedEmployees() { //get all salaried employees (1 query) List<Employee> salariedEmployees = _context.Employees.Where(x => x.Salaried).ToList(); //return department names for these employees (1 query for each 'Department' access) return salariedEmployees.Select(x => x.Department.Name).Distinct().ToList(); } }
public static void Main() { Employee emp; using(OrganizationDbContext context = new OrganizationDbContext()) { OrganizationRepository respository = new OrganizationRepository(context); emp = respository.GetEmployee(1); } var department = emp.Department; //explosion -- context is already disposed }
public class OrganizationRepositoryNonLazy { private readonly OrganizationDbContext _context; public OrganizationRepositoryNonLazy(OrganizationDbContext context) { this._context = context; } public string GetDeparmentNameForSalariedEmployee(int id) { Employee emp = _context.Employees .Include(x => x.Department) //Make sure EF brings back department as part of this query .SingleOrDefault(x => x.Id == id); if (emp.Salaried) //lazy loading disabled so no nav-property query here //Department is not null since we 'Include'd it above return emp.Department.Name; else throw new InvalidOperationException("Employee is not salaried"); } public string GetDepartmentNameForEmployee(int id) { Employee emp = _context.Employees .Include(x => x.Department) //Make sure EF brings back department as part of this query .SingleOrDefault(x => x.Id == id); //lazy loading disabled so no nav-property query here //Department is not null since we 'Include'd it above return emp.Department.Name; } public List<string> GetDepartmentsWithSalariedEmployees() { //get all salaried employees (1 query) List<Employee> salariedEmployees = _context.Employees .Include(x=>x.Department)//Make sure EF brings back department for each employee .Where(x => x.Salaried).ToList(); //lazy loading disabled so no nav-property query here //Department is not null since we 'Include'd it above return salariedEmployees.Select(x => x.Department.Name).Distinct().ToList(); } }
Prefer projection over manual mapping.
public class OrganizationSummary { public string Name { get; set; } public List<DepartmentSummary> Departments { get; set; } } public class DepartmentSummary { public string Name { get; set; } public int EmployeeCount { get; set; } } public class OrganizationRepository { private readonly OrganizationDbContext _context; public OrganizationRepository(OrganizationDbContext context) { this._context = context; } //mapped after the fact public OrganizationSummary GetOrganizationSummary(int id) { //note: the 'Include' line below is VERY important //if lazy loading is on, 'Include' forces eager loading of Departments and Employees //if lazy loading is off, Departments and Employees will not load without 'Include' var org = _context.Organizations .Include(x => x.Departments.SelectMany(y => y.Employees)) .Where(x => x.Id == id).SingleOrDefault(); return new OrganizationSummary() { Name = org.Name, Departments = org.Departments.Select(y => new DepartmentSummary() { Name = y.Name, EmployeeCount = y.Employees.Count //note that all employees are loaded into memory }).ToList() }; } //using projection public OrganizationSummary GetOrganizationSummary2(int id) { return _context.Organizations.Where(x => x.Id == id) .Select(x => new OrganizationSummary() { Name = x.Name, Departments = x.Departments.Select(y => new DepartmentSummary() { Name = y.Name, EmployeeCount = y.Employees.Count //here, Employees do *not* get loaded into memory. }).ToList() }).SingleOrDefault(); } }
DbContext, SaveChanges and dependency injection
What's wrong with the 'usual' architecture?
Nonweb contexts
In nonweb projects, such as services or console apps, there’s no web request — so configuring your DI to use 'InstancePerRequest' for your DbContexts will throw an exception. As a result, we must configure DbContext's lifetime to be singleton or transient. Singleton won't work since DbContext isn't thread-safe and the cache will quickly go stale (it's generally advised that DbContext's lifetime be kept short).- Atomicity is lost since the DbContexts' units of work are separate.
- Entities returned from Repository1 and passed to Repository2 won’t be 'attached' to Repository2's DbContext, leading to strange lost updates/inserts/deletes.
- Two connections to the database are opened, prompting escalation to a distributed transaction if TransactionScope is used (more on that later).
Web contexts
In this scenario, all repositories work on the same instance of DbContext — the one associated with the web request. There are two main issues in this scenario:- Implicitly using a shared DbContext leads to confusion over who needs to call SaveChanges.
- Implicitly reusing a shared DbContext across multiple calls to service methods can lead to unexpected side effects.
A better way
- Repositories must use a shared DbContext within a given service method so joint operations can be atomic.
- Repeated calls to the service class must use separate DbContexts in order to avoid the 'partial completion' bug described in the above section (#2).
- Repositories must not call SaveChanges (so their methods are composable).
- The service class must work in web and nonweb contexts.
- There must be no hard-coded dependencies.
public class OrganizationSummary public class ServiceClass { private readonly OrganizationDbContextFactory _contextFactory; private readonly OrganizationRepositoryFactory _orgFactory; private readonly LogRepositoryFactory _logFactory; //factories for the DbContext and each needed repository. //This enables us to explicitly control creation of a per-operation context that's used by all repositories //without relying on 'Singleton' or 'Instance per Request' public ServiceClass(OrganizationDbContextFactory contextFactory, OrganizationRepositoryFactory orgFactory, LogRepositoryFactory logFactory) { _contextFactory = contextFactory; _orgFactory = orgFactory; _logFactory = logFactory; } public void Save() { using(var context = _contextFactory.Create()) { //each repository works on the same context, and only within this block var logRepo = _logFactory.Create(context); var orgRepo = _orgFactory.Create(context); //neither of these calls SaveChanges, so they're safe to compose orgRepo.SetEmployeeSalaried(1); logRepo.LogMessage("INFO", "Employee 1 is now salaried"); //one and only one call to SaveChanges //note that if an exception is thrown in this block, //SaveChanges is not called and the DbContext is disposed, safely rejecting the in-progress changes context.SaveChanges(); } } } public class OrganizationRepository : IOrganizationRepository { private readonly OrganizationDbContext _context; private readonly OtherDependencies _dependencies; public OrganizationRepository(OrganizationDbContext context, OtherDependencies dependencies) { this._context = context; this._dependencies = dependencies; } //note there is *no* call to SaveChanges, so this method is composable with others public void SetEmployeeSalaried(int id) { var employee = _context.Employees.Find(id); employee.Salaried = true; } } public class LogRepository : ILogRepository { private readonly OrganizationDbContext _context; private readonly OtherDependencies _dependencies; public LogRepository(OrganizationDbContext context, OtherDependencies dependencies) { this._context = context; this._dependencies = dependencies; } //note there is *no* call to SaveChanges, so this method is composable with others public void LogMessage(string severity, string message) { Log log = new Log(); log.Severity = severity; log.Message = message; _context.Logs.Add(log); } } //factories for mockability, partial injection into repositories public class OrganizationDbContextFactory : IOrganizationDbContextFactory { public IOrganizationDbContext Create() { return new OrganizationDbContext(); } } public class OrganizationRepositoryFactory : IOrganizationRepositoryFactory { private readonly OtherDependencies _otherDependencies; public OrganizationRepositoryFactory(OtherDependencies otherDependencies) { _otherDependencies = otherDependencies; } public IOrganizationRepository Create(OrganizationDbContext context) { return new OrganizationRepository(context, _otherDependencies); } } public class LogRepositoryFactory : ILogRepositoryFactory { private readonly OtherDependencies _otherDependencies; public LogRepositoryFactory(OtherDependencies otherDependencies) { _otherDependencies = otherDependencies; } public ILogRepository Create(OrganizationDbContext context) { return new LogRepository(context, _otherDependencies); } }
Avoid TransactionScope.
public class OrganizationRepository { private readonly OrganizationDbContext _context; public OrganizationRepository(OrganizationDbContext context) { this._context = context; } public void DoSomething() { //the following two calls to SaveChanges are wrapped in a transaction using(TransactionScope scope = new TransactionScope()) { _context.Employees.Find(1).Salaried = true; _context.SaveChanges(); _context.Employees.Find(2).Salaried = false; _context.SaveChanges(); scope.Complete(); } } }
public class OrganizationRepository { private readonly OrganizationDbContext _context; public OrganizationRepository(OrganizationDbContext context) { this._context = context; } public void DoSomething() { //the following two calls to SaveChanges are wrapped in a transaction using (var trans = _context.Database.BeginTransaction()) { _context.Employees.Find(1).Salaried = true; _context.SaveChanges(); _context.Employees.Find(2).Salaried = false; _context.SaveChanges(); trans.Commit(); } } }
Wrapping up
Explore more Digital Innovation insights →
This article originally appeared on Jan. 10, 2017.