Data Access with EF6

This section documents ASP.NET MVC 5.3.x, ASP.NET Web API 2.2, ASP.NET Web Pages 3, OWIN/Katana, SignalR 2, and ASP.NET Identity 2 — all running on .NET Framework 4.8.1 — the System.Web-hosted MVC framework, its routing, Razor views, HTML helpers, model binding, filters, and the OWIN-based authentication/Identity stack — as described by the official documentation at Microsoft Learn (plus Web API, Web Pages, SignalR, and Identity), which are the reference these pages are written and verified against.

This is not ASP.NET Core MVC. ASP.NET MVC 5 is the pre-Core, System.Web-hosted MVC framework; it is functionally frozen and receives only security fixes. For the current, cross-platform MVC framework see MVC Controllers and Views under ASP.NET Core (Blazor).

This content was generated with the assistance of AI and should be verified against the official documentation before being relied on in production.

This section’s bibliography lists the reference material consulted while preparing these pages.

This page documents Entity Framework 6 as used from ASP.NET MVC 5.3.x on .NET Framework 4.8.1 — not EF Core (see Data Access with EF Core under ASP.NET Core).

`DbContext` in an MVC app

public class ApplicationDbContext : DbContext
{
    public ApplicationDbContext() : base("DefaultConnection") { }   // named connection string in web.config

    public DbSet<Product> Products { get; set; }
    public DbSet<Category> Categories { get; set; }
}

<!-- web.config -->
<connectionStrings>
  <add name="DefaultConnection" connectionString="Data Source=.;Initial Catalog=Shop;Integrated Security=True"
       providerName="System.Data.SqlClient" />
</connectionStrings>

A controller typically creates and disposes its own context per request (using or an IDisposable Controller.Dispose override), or one is injected via a DI container wired through a custom IControllerFactory (see The MVC Pattern and Request Life Cycle):

public class ProductsController : Controller
{
    private readonly ApplicationDbContext _db = new ApplicationDbContext();

    protected override void Dispose(bool disposing)
    {
        if (disposing) _db.Dispose();
        base.Dispose(disposing);
    }
}

Code First, Database First, Model First

Code First — C# classes define the model; EF6 generates the schema (and, with migrations, evolves it). The default and most common approach for new MVC 5 projects.
Database First — an existing database is reverse-engineered into an .edmx model and generated entity classes.
Model First — a visual .edmx designer defines the model first, then generates the database schema. Database First and Model First both center on the .edmx designer, which is largely legacy even within the EF6 era; Code First is what current MVC 5 guidance recommends.

Migrations

Enable-Migrations                          # Package Manager Console -- scaffolds Migrations/Configuration.cs
Add-Migration AddProductDescription        # scaffolds a migration comparing the model to the last snapshot
Update-Database                            # applies pending migrations to the configured connection string

// Migrations/Configuration.cs
internal sealed class Configuration : DbMigrationsConfiguration<ApplicationDbContext>
{
    public Configuration() => AutomaticMigrationsEnabled = false;

    protected override void Seed(ApplicationDbContext context)
    {
        context.Categories.AddOrUpdate(c => c.Name, new Category { Name = "Uncategorized" });
    }
}

Update-Database can also run automatically on application start via a Database.SetInitializer (e.g. MigrateDatabaseToLatestVersion<TContext, TConfiguration>), though explicit Update-Database in a deployment pipeline is generally preferred for production. See Code First Migrations.

LINQ queries, loading strategies, and the N+1 trap

// Eager loading -- one query, via Include
var products = db.Products.Include(p => p.Category).Where(p => p.IsActive).ToList();

// Lazy loading -- a virtual navigation property triggers a query on first access
public class Product { public virtual Category Category { get; set; } }
foreach (var p in db.Products.ToList()) { var name = p.Category.Name; }   // N+1: one query per product!

// Explicit loading -- load a navigation property on demand for an already-fetched entity
db.Entry(product).Reference(p => p.Category).Load();

The N+1 problem above is lazy loading’s classic trap: one query fetches N products, then N further queries fetch each product’s category individually. Include (eager loading) collapses this back to a single query and should be the default choice for any collection a view will iterate and dereference navigation properties on.

Async EF6 in async actions

public async Task<ActionResult> Index()
    => View(await _db.Products.Where(p => p.IsActive).ToListAsync());

public async Task<ActionResult> Details(int id)
{
    var product = await _db.Products.FindAsync(id);
    return product == null ? (ActionResult)HttpNotFound() : View(product);
}

Pairing EF6’s *Async methods with async Task<ActionResult> actions (see Controllers and Actions) frees the IIS request thread during I/O instead of blocking it — see Caching and Performance for why this matters under thread-pool pressure.

Connection resiliency

public class ApplicationDbConfiguration : DbConfiguration
{
    public ApplicationDbConfiguration()
        => SetExecutionStrategy("System.Data.SqlClient",
            () => new SqlAzureExecutionStrategy(maxRetryCount: 5, maxDelay: TimeSpan.FromSeconds(30)));
}

SetExecutionStrategy registers automatic retry-on-transient-failure (connection drops, Azure SQL throttling) around EF6 operations; it must not be combined with a manually managed TransactionScope spanning multiple SaveChanges calls without care, since a retried operation must itself be safely repeatable.

Repository / unit-of-work over `DbContext`

DbContext (via DbSet<T>) already is a unit of work and a set of repositories — a hand-rolled IRepository<T>/IUnitOfWork wrapper is common in MVC 5-era codebases (often to ease unit testing controllers without a real database) but adds a layer of indirection EF6 arguably already provides:

public interface IProductRepository
{
    IEnumerable<Product> GetAll();
    Product Find(int id);
    void Add(Product product);
    void SaveChanges();
}

Reach for this pattern when a genuine seam is needed (swapping EF6 for a different store in tests, sharing data logic across MVC and Web API controllers); avoid it as a default — wrapping DbContext 1:1 in a repository interface mostly just duplicates `DbSet<T>’s own API.

Scaffolded CRUD controllers and `SelectList`

Visual Studio’s Add Controller scaffolding, given a model class and a DbContext, generates a full CRUD controller plus matching views, including a SelectList for any foreign-key dropdown:

public ActionResult Create()
{
    ViewBag.CategoryId = new SelectList(db.Categories, "Id", "Name");
    return View();
}

See HTML Helpers and Forms for Html.DropDownListFor consuming that SelectList.

EF6 vs. EF Core for a migrating application

EF6 continues to receive bug fixes but no new features, and does not run on .NET (only .NET Framework); an application migrating to ASP.NET Core (see Migrating to ASP.NET Core) must eventually move to EF Core, which has a different (though conceptually similar) Fluent API, no .edmx designer support at all, and its own migration tooling (dotnet ef migrations add). EF6 can, however, keep running on .NET Core/.NET 5+ if it is never fully retired — Microsoft ships an EntityFramework package compatible with modern .NET specifically for this incremental-migration case, trading EF Core’s performance/feature improvements for not having to rewrite the data layer on day one. See Entity Framework 6 and EF Core vs. EF6.

Next: Caching and Performance covers output caching and the thread-pool implications of the async patterns above.