Entity Framework 6 Code-First patterns for .NET Framework 4.7. DbContext, migrations, querying, relationships, performance, bulk operations, Dapper coexistence. NOT EF Core.
CRITICAL: This skill covers Entity Framework 6 on .NET Framework. NOT EF Core. Key differences:
System.Data.Entity (not Microsoft.EntityFrameworkCore)DbModelBuilder in OnModelCreating (not ModelBuilder)Database.SetInitializer<T>() exists (EF Core has no equivalent)CompiledQuery only works with ObjectContext, not DbContextInclude() cannot filter (EF Core 5+ can)// Convention: uses class name as database name
public class BlogContext : DbContext { }
// Named connection string from Web.config
public class BlogContext : DbContext
{
public BlogContext() : base("name=BloggingDatabase") { }
}
// Existing connection (EF6 allows open connections)
public class BlogContext : DbContext
{
public BlogContext(DbConnection conn, bool ownsConnection)
: base(conn, ownsConnection) { }
}
public class MyContext : DbContext
{
public MyContext()
{
Configuration.LazyLoadingEnabled = false; // Default: true
Configuration.ProxyCreationEnabled = false; // Default: true
Configuration.AutoDetectChangesEnabled = true; // Default: true
}
public DbSet<Product> Products { get; set; }
protected override void OnModelCreating(DbModelBuilder modelBuilder)
{
modelBuilder.Conventions.Remove<PluralizingTableNameConvention>();
modelBuilder.HasDefaultSchema("dbo");
}
}
using. NOT thread-safe.Enable-Migrations # Creates Migrations/Configuration.cs
Add-Migration AddBlogUrl # Generates Up()/Down() migration
Update-Database # Apply pending migrations
Update-Database -Verbose # Show SQL
Update-Database -TargetMigration: AddBlogUrl # Migrate to specific version
Update-Database -TargetMigration: $InitialDatabase # Roll back to empty
Update-Database -Script # Generate SQL script only
public partial class AddBlogUrl : DbMigration
{
public override void Up()
{
AddColumn("dbo.Blogs", "Url", c => c.String());
// Raw SQL in migrations
Sql("UPDATE dbo.Blogs SET Url = 'http://default' WHERE Url IS NULL");
}
public override void Down()
{
DropColumn("dbo.Blogs", "Url");
}
}
internal sealed class Configuration : DbMigrationsConfiguration<MyContext>
{
public Configuration()
{
AutomaticMigrationsEnabled = false; // Explicit migrations recommended
}
protected override void Seed(MyContext context)
{
context.Blogs.AddOrUpdate(
b => b.Name, // Match on this property
new Blog { Name = "Default" } // Insert or update
);
}
}
Database.SetInitializer(new MigrateDatabaseToLatestVersion<MyContext, Configuration>());
| State | SaveChanges | How to set |
|---|---|---|
| Added | INSERT | context.Set.Add(entity) or Entry(e).State = Added |
| Unchanged | Skip | After SaveChanges() or Attach() |
| Modified | UPDATE | Change tracked property or Entry(e).State = Modified |
| Deleted | DELETE | context.Set.Remove(entity) or Entry(e).State = Deleted |
| Detached | Ignore | Before Add/Attach, or after AsNoTracking() |
public void InsertOrUpdate(Blog blog)
{
using (var context = new MyContext())
{
context.Entry(blog).State = blog.BlogId == 0
? EntityState.Added
: EntityState.Modified;
context.SaveChanges();
}
}
public class Blog
{
public int BlogId { get; set; }
public virtual ICollection<Post> Posts { get; set; } // virtual = lazy loaded
}
// N+1 BUG:
var blogs = context.Blogs.ToList(); // 1 query
foreach (var blog in blogs)
foreach (var post in blog.Posts) // N queries!
Console.WriteLine(post.Title);
// Single level
var blogs = context.Blogs.Include(b => b.Posts).ToList();
// Multi-level
var blogs = context.Blogs
.Include(b => b.Posts.Select(p => p.Comments))
.ToList();
// String-based (for dynamic includes)
var blogs = context.Blogs.Include("Posts.Comments").ToList();
LIMITATION: Include() loads ALL related entities. Cannot filter. Unlike EF Core 5+.
var blog = context.Blogs.Find(1);
// Load collection
context.Entry(blog).Collection(b => b.Posts).Load();
// Load with filter
context.Entry(blog).Collection(b => b.Posts)
.Query()
.Where(p => p.IsPublished)
.Load();
// Count without loading
var count = context.Entry(blog).Collection(b => b.Posts).Query().Count();
// Load reference
context.Entry(post).Reference(p => p.Blog).Load();
var products = context.Products
.AsNoTracking()
.Where(p => p.IsActive)
.ToList();
28% less memory, ~16% faster than tracked queries.
modelBuilder.Entity<Course>()
.HasRequired(c => c.Department) // Required FK
.WithMany(d => d.Courses) // Collection on other side
.HasForeignKey(c => c.DepartmentID) // FK property
.WillCascadeOnDelete(false);
// Optional FK
modelBuilder.Entity<Course>()
.HasOptional(c => c.Department)
.WithMany(d => d.Courses)
.HasForeignKey(c => c.DepartmentID);
modelBuilder.Entity<Course>()
.HasMany(c => c.Instructors)
.WithMany(i => i.Courses)
.Map(m =>
{
m.ToTable("CourseInstructor");
m.MapLeftKey("CourseID");
m.MapRightKey("InstructorID");
});
modelBuilder.Entity<OfficeAssignment>()
.HasRequired(o => o.Instructor)
.WithOptional(i => i.OfficeAssignment);
[Key] // Primary key
[Column(Order = 0)] // Composite key ordering
[Required] // Non-nullable
[MaxLength(50)] // Max length
[Table("MyTable")] // Table name
[Column("ColName")] // Column name
[NotMapped] // Exclude from DB
[ForeignKey("NavigationProp")] // Foreign key
[InverseProperty("PropertyName")] // Disambiguate multiple relationships
[Index("IX_Name", IsUnique = true)] // Index (EF6.1+)
[ConcurrencyCheck] // Optimistic concurrency
[Timestamp] // Row version byte[]
[DatabaseGenerated(DatabaseGeneratedOption.Identity)] // Auto-increment
// SqlQuery on DbSet — entities ARE tracked
var blogs = context.Blogs.SqlQuery("SELECT * FROM dbo.Blogs").ToList();
// SqlQuery on Database — NOT tracked
var names = context.Database.SqlQuery<string>("SELECT Name FROM dbo.Blogs").ToList();
// Parameterized (ALWAYS parameterize, never concatenate)
var blog = context.Blogs.SqlQuery("dbo.GetBlogById @p0", blogId).Single();
// Non-query
context.Database.ExecuteSqlCommand(
"UPDATE dbo.Blogs SET Name = @p0 WHERE BlogId = @p1", newName, blogId);
// AddRange — reduces DetectChanges overhead but still individual INSERTs
context.Products.AddRange(listOfProducts);
context.SaveChanges();
// EntityFramework.BulkInsert (uses SqlBulkCopy)
using EntityFramework.BulkInsert.Extensions;
context.BulkInsert(hugeListOfEntities);
// Manual SqlBulkCopy
using (var bulkCopy = new SqlBulkCopy((SqlConnection)context.Database.Connection))
{
bulkCopy.DestinationTableName = "dbo.Products";
bulkCopy.WriteToServer(dataTable);
}
// Bulk update/delete — use raw SQL
context.Database.ExecuteSqlCommand(
"DELETE FROM Logs WHERE CreatedDate < @p0", cutoffDate);
try
{
context.Configuration.AutoDetectChangesEnabled = false;
context.Products.AddRange(products);
context.SaveChanges();
}
finally
{
context.Configuration.AutoDetectChangesEnabled = true;
}
| Scenario | Use |
|---|---|
| CRUD with change tracking | EF6 |
| Complex read queries, reporting | Dapper |
| Bulk reads | Dapper or EF6 AsNoTracking |
| Stored procedures | Dapper (Query) or EF6 (SqlQuery) |
| Migrations, schema | EF6 |
using Dapper;
using (var context = new MyContext())
{
var connection = context.Database.Connection;
// Dapper query on same connection
var results = connection.Query<ProductDto>(
"SELECT ProductId, Name, Price FROM Products WHERE CategoryId = @CategoryId",
new { CategoryId = 5 });
// EF6 still works on same context
var category = context.Categories.Find(5);
context.SaveChanges();
}
using (var context = new MyContext())
{
context.Database.Connection.Open();
using (var transaction = context.Database.BeginTransaction())
{
try
{
// EF6 operation
context.Products.Add(new Product { Name = "New" });
context.SaveChanges();
// Dapper operation — same connection + transaction
context.Database.Connection.Execute(
"INSERT INTO AuditLog (Action) VALUES (@Action)",
new { Action = "ProductCreated" },
transaction.UnderlyingTransaction);
transaction.Commit();
}
catch
{
transaction.Rollback();
throw;
}
}
}
| Pitfall | Solution |
|---|---|
| N+1 queries from lazy loading | Use Include() for eager loading or disable lazy loading |
ObjectDisposedException accessing nav props after context disposed | Eager load before disposing, or project to DTO inside using |
| Serializer triggers lazy load cascade (loads entire DB) | Disable lazy loading before serialization, use DTOs, use [JsonIgnore] |
DetectChanges slow on large graphs | AddRange/RemoveRange, disable AutoDetectChanges for bulk ops |
| Dynamic queries fill query plan cache (800 limit) | Use parameterized lambdas: .Skip(() => i) not .Skip(i) |
Include() loads ALL related entities | Use explicit loading with .Query().Where() for filtered loads |
EF6 CompiledQuery only works with ObjectContext | Don't try to use it with DbContext |
| Composing over compiled queries bypasses cache | .ToList() the compiled query result before further LINQ |
MARS=True missing causes "already an open DataReader" | Add MultipleActiveResultSets=True to connection string |
Include() used for all known eager loading pathsAsNoTracking() used for read-only queriesMARS=True in connection string if lazy loading enabledAddRange/RemoveRange used instead of loops for bulk operations@p0, never string concatenation)AutomaticMigrationsEnabled = false)<connectionStrings> section