📒 EF CORE & DATABASE
QUICK REVISION NOTES
Short & Crispy Notes for Last-Minute Interview Prep
By Logic Lense
© Copyright by Logic Lense
SECTION 1 – EF CORE BASICS
1. What is Entity Framework Core?
Concept Summary:
• ORM (Object-Relational Mapping) framework for .NET that abstracts database access
• Maps database tables → C# classes, rows → objects
• Successor to Entity Framework 6, lightweight, cross-platform
Key Points:
• Supports SQL Server, PostgreSQL, MySQL, SQLite, Oracle
• LINQ-to-Entities: Write C# queries that translate to SQL
• Change tracking: Knows which objects are added/modified/deleted
• SaveChanges(): Batches changes and executes in transaction
Remember This: EF Core = LINQ queries → SQL. Don't think ORMs = magic, understand the
translation!
2. EF Core vs [Link]
Aspect EF Core [Link]
Abstraction High-level ORM Low-level, SQL-first
LINQ Support Full LINQ-to-Entities DataSets only
Performance Good (some overhead) Fastest (minimal overhead)
Boilerplate Minimal High (manual queries)
Best For Most scenarios Complex/bulk ops
3. Code First vs Database First
CODE FIRST:
• Write C# models → Migrations create database schema
• Best for: New projects, domain-driven design
• Tools: add-migration, update-database
DATABASE FIRST:
• Database exists → Scaffold models from existing schema
• Best for: Legacy systems, reverse-engineering
• Tools: dotnet ef dbcontext scaffold
4. DbContext and DbSet Explained
DbContext = Session + Unit of Work
© Copyright by Logic Lense
public class ApplicationDbContext : DbContext
{
public DbSet<User> Users { get; set; }
public DbSet<Order> Orders { get; set; }
protected override void OnConfiguring(DbContextOptionsBuilder options)
{
[Link]("connection_string");
}
}
DbContext responsibility:
• Track object state (Added, Modified, Deleted, Unchanged)
• Manage database connection
• SaveChanges() → batches changes into transaction
• Query builders (LINQ)
DbSet<T> = Table abstraction
• DbSet<User> Users = represents Users table
• Methods: .Add(), .Remove(), .Find(), .AsQueryable()
• DbSet is IQueryable → LINQ queries translate to SQL
5. What are Migrations in EF Core?
Definition:
Versioned snapshots of your DbContext model. Track database schema changes over time.
// Create migration
dotnet ef migrations add AddUserTable
// Apply to database
dotnet ef database update
// Revert last migration
dotnet ef migrations remove
Why migrations?
• Version control for database schema
• Rollback capability
• Deploy schema across environments consistently
• Zero downtime deployments (idempotent)
Common Mistakes:
• Deleting migrations in source control
• Running update-database on production without backup
© Copyright by Logic Lense
• Leaving auto-generated migration names vague (rename them!)
6. How does EF Core Track Changes?
Change Tracking Process:
• 1. Load entity → EF Core takes snapshot
• 2. Modify properties
• 3. SaveChanges() → Compares current state to snapshot
• 4. Generates UPDATE/INSERT/DELETE SQL
var user = [Link](1); // Snapshot taken
[Link] = "John"; // Modified
[Link](); // Detects change, generates UPDATE
Entity States:
• Detached: Unknown to context
• Added: Marked for INSERT
• Unchanged: Loaded, no changes
• Modified: Changed, marked for UPDATE
• Deleted: Marked for DELETE
© Copyright by Logic Lense
SECTION 2 – LOADING & QUERYING
1. Lazy Loading vs Eager Loading vs Explicit Loading
Strategy What Happens Best For Watch Out
Lazy Loading Query fires when Simple queries N+1 problem!
accessing Nav Prop
Eager Loading Include() upfront, Complex data Cartesian explosion
single query
Explicit Loading Load() when needed Conditional Manual control
// LAZY: Query fires when accessing Orders
var user = [Link](1);
var orders = [Link]; // ← Separate query!
// EAGER: All data in one query
var user = [Link](u => [Link]).First();
// EXPLICIT: Load only when needed
var user = [Link](1);
[Link](user).Collection(u => [Link]).Load();
🔴 Common Mistake:
• Lazy loading in loop = N+1 queries. Use Include()!
2. What is Include() and ThenInclude()?
Eager Load Relations in Single Query
// Include related table
[Link]
.Include(u => [Link])
.ToList();
// Include nested relations (ThenInclude)
[Link]
.Include(u => [Link])
.ThenInclude(o => [Link])
.ToList();
// Multiple includes
[Link]
.Include(u => [Link])
.Include(u => [Link])
.ToList();
Key Points:
• Include() = JOIN in generated SQL
• ThenInclude() = chain for nested includes
© Copyright by Logic Lense
• Cartesian explosion: Too many includes can duplicate rows
3. AsNoTracking() – Why and When?
AsNoTracking() = Read-only queries
// WITHOUT tracking (slower)
var users = [Link]();
// WITH AsNoTracking (faster read-only)
var users = [Link]().ToList();
// Use case: Reports, dashboards
var report = [Link]
.AsNoTracking()
.Where(o => [Link] > [Link](-1))
.GroupBy(o => [Link])
.Select(g => new { Status = [Link], Count = [Link]() })
.ToList();
When to use AsNoTracking():
• Read-only queries (no SaveChanges planned)
• Large result sets (less memory)
• Reports/dashboards
• Performance-critical queries
4. LINQ-to-Entities vs LINQ-to-Objects
Feature LINQ-to-Entities LINQ-to-Objects
Execution Translated to SQL In-memory .NET code
Data Source Database IEnumerable<T>
Performance Server-side filtering Client-side filtering
When Triggered ToList/FirstOrDefault Enumerated
Best For Large datasets Small collections
// LINQ-to-Entities (executes on SQL Server)
var users = [Link]
.Where(u => [Link] > 25) // ← SQL WHERE
.ToList(); // ← Executed now
// LINQ-to-Objects (executes in memory)
var userList = [Link]();
var filtered = userList
.Where(u => [Link] > 25) // ← .NET filter
.ToList(); // ← Already loaded
🔴 Major Mistake:
• ToList() before Where() = loads entire table!
© Copyright by Logic Lense
5. Projection vs Entity Loading
// Entity Loading: Full objects
var users = [Link](); // 1000 rows × 20 columns = huge
// Projection: Only needed columns
var usernames = [Link]
.Select(u => new { [Link], [Link] }) // Only 2 columns
.ToList(); // Smaller result set
// Anonymous projection
var dto = [Link]
.Select(u => new UserDTO {
Id = [Link],
Name = [Link],
OrderCount = [Link]
})
.ToList();
Why Project?
• Smaller payload (network/memory)
• Faster queries
• Better for APIs/DTOs
• Aggregations (Count, Sum, Avg) execute on server
6. Raw SQL Queries in EF Core
// FromSqlInterpolated (parameterized, safe)
var minAge = 18;
var users = [Link]
.FromSqlInterpolated($"SELECT * FROM Users WHERE Age > {minAge}")
.ToList();
// Raw ExecuteSql for non-query
[Link](
$"UPDATE Users SET IsActive = 0 WHERE LastLogin < {cutoffDate}"
);
When to use Raw SQL?
• Complex stored procedures
• Bulk operations
• SQL injection: ALWAYS use parameterized queries!
© Copyright by Logic Lense
SECTION 3 – PERFORMANCE & OPTIMIZATION
1. N+1 Problem in EF Core
The Problem:
// ❌ BAD: N+1 queries
var users = [Link](); // 1 query
foreach (var user in users)
{
var orders = [Link]; // N queries! (1 per user)
}
// ✅ GOOD: Single query with Include
var users = [Link]
.Include(u => [Link])
.ToList();
Root Cause:
• Lazy loading in loop = database query per iteration
Solutions:
• Include() for eager loading
• AsNoTracking() for read-only
• Projection to avoid loading full objects
2. Indexes – When & Why?
Create Indexes on:
• WHERE clause columns
• Foreign keys
• JOIN columns
• ORDER BY columns
• Frequently searched columns
// Fluent API indexing
[Link]<User>()
.HasIndex(u => [Link])
.IsUnique();
// Composite index
[Link]<Order>()
.HasIndex(o => new { [Link], [Link] });
// Data Annotations
[Index(nameof(Email), IsUnique = true)]
public class User { ... }
© Copyright by Logic Lense
Cost vs Benefit:
• Faster SELECT queries
• Slower INSERT/UPDATE (index maintenance)
• More disk space
3. Query Performance Best Practices
• Use AsNoTracking() for read-only queries
• Project instead of loading full entities
• Avoid lazy loading in loops
• Use indexes on WHERE/JOIN/FK columns
• Pagination for large datasets
• Filter on server (Where), not client
• Use raw SQL for complex queries
4. Connection Pooling
// Connection pooling (enabled by default)
[Link](
"Server=.;Database=MyDb;",
options => [Link]());
What is pooling?
• Reuses database connections instead of opening/closing
• Huge performance gain
• Enabled by default in EF Core
5. Transactions in EF Core
// SaveChanges auto-wraps in transaction
[Link](user);
[Link](); // Auto-commit/rollback
// Explicit transaction
using (var transaction = [Link]())
{
try
{
[Link](user);
[Link]();
[Link]();
}
catch
{
[Link]();
}
}
© Copyright by Logic Lense
Key Points:
• ACID compliance: All-or-nothing
• SaveChanges() = Implicit transaction
• Use explicit for multi-step operations
6. Handling Large Datasets
// PAGINATION (Best practice)
var pageNumber = 1;
var pageSize = 20;
var users = [Link]
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.AsNoTracking()
.ToList();
// BATCH DELETE
[Link]
.Where(u => [Link])
.ExecuteDelete(); // Bulk delete
Best Practices:
• Always paginate
• Use AsNoTracking() for reporting
• ExecuteDelete/ExecuteUpdate for bulk operations
• Batch processing in loops
© Copyright by Logic Lense
SECTION 4 – DATABASE & SQL INTERVIEW
1. Primary Key vs Foreign Key
Feature Primary Key Foreign Key
Purpose Uniquely identify row Reference another table
Uniqueness Always unique Not always unique
Nullability Cannot be NULL Can be NULL
Constraint 1 per table Multiple per table
Index Auto-indexed Can be indexed
References Referenced by FK References PK
Example UserId = 1 (unique) Order's UserId FK
2. Clustered vs Non-Clustered Index
Aspect Clustered Non-Clustered
How Many 1 per table Up to 999
Physical Order Defines table order Separate structure
Leaf Node Actual data pages Index key + pointer
Speed Fastest Fast
Create On PK by default Any column
Range Queries Excellent Good
Example PK (UserId) Email index
3. Joins (Inner, Left, Right)
-- INNER JOIN: Only matching rows
SELECT [Link], [Link]
FROM Users u
INNER JOIN Orders o ON [Link] = [Link];
-- LEFT JOIN: All from left, matching from right
SELECT [Link], [Link]
FROM Users u
LEFT JOIN Orders o ON [Link] = [Link];
-- RIGHT JOIN: All from right, matching from left
SELECT [Link], [Link]
FROM Users u
RIGHT JOIN Orders o ON [Link] = [Link];
© Copyright by Logic Lense
4. Stored Procedures vs EF Core Queries
Factor EF Core Stored Procedure
Reusability Code reusable Database-bound
Performance Good (optimized) Potentially faster
Maintenance With code In database
Version Control Git Database migrations
Testability Easy mocking Requires DB
Complex Logic C# + SQL T-SQL only
Parameterization Built-in Manual
When to use Stored Procedure?
• Very complex business logic
• Audit/compliance requirements
• Performance critical bulk operations
• Legacy systems
5. Normalization vs Denormalization
Aspect Normalization Denormalization
Goal Reduce redundancy Improve query speed
Duplication Minimized Intentional
Joins More joins Fewer/no joins
Write Performance Slower Faster
Read Performance Slower Faster
Storage Minimal More
ACID Easier Complex
Use Case OLTP (Transactional) OLAP (Analytics)
6. Deadlock – What & How to Avoid
What is Deadlock?
• Two transactions wait for each other indefinitely
// DEADLOCK SCENARIO
Transaction A: Lock Table A → Lock Table B
Transaction B: Lock Table B → Lock Table A (DEADLOCK!)
// SOLUTION: Consistent ordering
Transaction A: Lock Table A → Lock Table B
Transaction B: Lock Table A → Lock Table B (OK!)
Prevention:
© Copyright by Logic Lense
• Access resources in same order
• Keep transactions short
• Use appropriate isolation levels
• Retry failed transactions
• Use row-level locking, not table
© Copyright by Logic Lense
SECTION 5 – REAL-WORLD SCENARIOS
1. Repository Pattern with EF Core
public interface IRepository<T> where T : class
{
T GetById(int id);
IEnumerable<T> GetAll();
void Add(T entity);
void Update(T entity);
void Delete(T entity);
void SaveChanges();
}
public class Repository<T> : IRepository<T> where T : class
{
private readonly ApplicationDbContext _context;
public Repository(ApplicationDbContext context) => _context = context;
public T GetById(int id) => _context.Set<T>().Find(id);
public IEnumerable<T> GetAll() => _context.Set<T>().ToList();
public void Add(T entity) => _context.Set<T>().Add(entity);
public void Delete(T entity) => _context.Set<T>().Remove(entity);
public void SaveChanges() => _context.SaveChanges();
}
Benefits:
• Abstraction of data access
• Easy to mock for testing
• Centralized data logic
2. Unit of Work Pattern
The Unit of Work Pattern in C# groups one or more operations (usually database CRUD operations)
into a single transaction and executes them by applying the principle of do everything or do
nothing.
That means if any of the transaction’s operations fail, it will roll back the transaction. If all the
operations are successful, then it will commit the transaction. So, it will execute all the database
operations as one unit.
public interface IUnitOfWork : IDisposable
{
IRepository<User> Users { get; }
IRepository<Order> Orders { get; }
void SaveChanges();
}
© Copyright by Logic Lense
// Usage: Atomic operation
using (var uow = new UnitOfWork(context))
{
[Link](user);
[Link](order);
[Link](); // All-or-nothing
}
Why UnitOfWork?
• Atomic transactions
• Single SaveChanges() call
• Consistency across repositories
3. Soft Delete vs Hard Delete
Aspect Soft Delete Hard Delete
Definition Mark deleted, keep data Remove permanently
Recovery Easy, update flag Impossible
Compliance Audit trail preserved Data gone
Queries Filter IsDeleted = 0 Natural
Performance Slightly slower Faster
Space Uses more Frees space
Use Case Business records Temporary data
// SOFT DELETE
public class User
{
public int UserId { get; set; }
public string Name { get; set; }
public bool IsDeleted { get; set; } // Soft delete flag
}
var user = [Link](1);
[Link] = true;
[Link]();
4. Pagination in EF Core
var pageNumber = 1;
var pageSize = 20;
var users = [Link]
.Skip((pageNumber - 1) * pageSize)
.Take(pageSize)
.AsNoTracking()
.ToList();
5. Auditing (CreatedDate, UpdatedDate)
© Copyright by Logic Lense
public abstract class BaseEntity
{
public int Id { get; set; }
public DateTime CreatedDate { get; set; } = [Link];
public DateTime UpdatedDate { get; set; } = [Link];
}
// Auto-update on SaveChanges
public override int SaveChanges()
{
foreach (var entry in [Link]<BaseEntity>())
{
if ([Link] == [Link])
[Link] = [Link];
if ([Link] != [Link])
[Link] = [Link];
}
return [Link]();
}
6. Handling Concurrency in EF Core
public class User
{
public int UserId { get; set; }
[Timestamp]
public byte[] RowVersion { get; set; }
}
// Update with conflict detection
try
{
var user = [Link](1);
[Link] = "John";
[Link]();
}
catch (DbUpdateConcurrencyException ex)
{
var entry = [Link]();
var databaseValues = [Link]();
[Link](databaseValues);
}
© Copyright by Logic Lense
SECTION 6 – COMMON MISTAKES
❌ MISTAKE 1: Calling ToList() Early
• Loads all data first, then filters in memory
// WRONG: Loads all 1M rows!
var users = [Link]();
var activeUsers = [Link](u => [Link]).ToList();
// RIGHT: Filters on SQL server
var activeUsers = [Link]
.Where(u => [Link])
.ToList();
❌ MISTAKE 2: Loading Full Tables
// WRONG: Gets all columns
var users = [Link]();
// RIGHT: Get only needed columns
var usernames = [Link]
.Select(u => new { [Link], [Link] })
.ToList();
❌ MISTAKE 3: No Indexes on Filters
• WHERE Email = X → Index Email column
❌ MISTAKE 4: Multiple SaveChanges in Loop
// WRONG: 1000 SaveChanges = 1000 DB round trips
foreach (var item in items)
{
[Link](item);
[Link](); // Slow!
}
// RIGHT: Single SaveChanges
foreach (var item in items)
{
[Link](item);
}
[Link](); // Fast!
❌ MISTAKE 5: Lazy Loading in Loop (N+1)
// WRONG: N+1 queries
var users = [Link]();
foreach (var user in users)
var orders = [Link]; // N queries!
© Copyright by Logic Lense
// RIGHT: Include eagerly
var users = [Link]
.Include(u => [Link])
.ToList();
❌ MISTAKE 6: Not Using Migrations
• Manual SQL changes = lose version control and rollback capability
❌ MISTAKE 7: No Pagination
• Loading 1M rows = OOM, timeout
❌ MISTAKE 8: SQL Injection
// WRONG: String interpolation
var users = [Link]
.FromSql($"SELECT * FROM Users WHERE Email = {email}")
.ToList();
// RIGHT: Parameterized
var users = [Link]
.FromSqlInterpolated($"SELECT * FROM Users WHERE Email = {email}")
.ToList();
❌ MISTAKE 9: Ignoring Query Logs
[Link]([Link])
.EnableSensitiveDataLogging();
❌ MISTAKE 10: Tracking Disabled Globally
• Changes won't be detected unless re-tracked
© Copyright by Logic Lense
📋 LAST-MINUTE CHECKLIST
Review these 10 points before the interview:
• EF Core = ORM that translates C# LINQ to SQL
• DbContext = session + unit of work
• Always Include() related data, never lazy load in loops
• N+1 problem = death by 1000 queries, fix with Include
• Use AsNoTracking() for read-only, Projection for DTO
• Migrations = version control for schema
• Indexes on WHERE/FK/JOIN columns only
• Pagination + AsNoTracking for large datasets
• ToList() early = loads entire table (BAD)
• SaveChanges = implicit transaction, all-or-nothing
Good luck! 🚀
© Copyright by Logic Lense
RECOMMENDED EBOOKS YOU WILL LOVE
© Copyright by Logic Lense
STAY CONNECTED & LEARN DAILY
Follow Logic Lense for daily content, tips, jobs, projects, and
coding knowledge:
Share your feedback at: - [Link]
A FINAL NOTE
The tech world rewards consistent learners.
Stay committed. Stay curious. Keep building.
Your dream job is closer than you think.
See you in the next product.
Keep growing with Logic Lense.
© Copyright by Logic Lense
© Copyright by Logic Lense