Entity Framework Core: ORM, Database Operations & LINQ

Core Concepts

What is Entity Framework Core (EF Core)?

Entity Framework Core is a lightweight, cross-platform Object-Relational Mapper (ORM) that simplifies database interactions by mapping .NET objects to relational database tables. It eliminates manual SQL writing for common operations like Create, Read, Update, Delete (CRUD).

classDiagram
    class DbContext {
        +DbSet~TEntity~ Entities
        +SaveChanges()
    }
    class Entity {
        <<abstract>>
        +Id
    }
    class DbSet~TEntity~ {
        +Add(TEntity)
        +Remove(TEntity)
        +Find(params object[] keyValues)
    }
    DbContext "1" --> "*" DbSet~TEntity~ : contains
    DbSet~TEntity~ "1" --> "*" Entity : manages

Key Features:

• LINQ Support: Query databases using C# syntax.
• Migrations: Version-control database schema changes.
• Cross-Platform: Works on Windows, Linux, and macOS.
• Performance: Optimized for high-throughput applications.

Database Operations with EF Core

1. CRUD Operations

EF Core automates database operations through DbContext and DbSet<T>.

Example: Student Management System (eSewa-like)

// Define the model
public class Student {
    public int Id { get; set; }
    public string Name { get; set; }
    public string Email { get; set; }
}

// DbContext class
public class SchoolDbContext : DbContext {
    public DbSet<Student> Students { get; set; }
    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) {
        optionsBuilder.UseSqlServer("Server=...;Database=SchoolDB;Trusted_Connection=True;");
    }
}

CRUD Workflow:

Worked Example: Adding a Student (eSewa User Registration)

using (var context = new SchoolDbContext()) {
    var student = new Student { Name = "Ramesh", Email = "ramesh@example.com" };
    context.Students.Add(student);
    context.SaveChanges(); // Executes INSERT
}

LINQ Queries

EF Core translates LINQ queries into SQL. Example: Fetch all students with email containing "@kathmandu.edu.np".

var students = context.Students
    .Where(s => s.Email.Contains("@kathmandu.edu.np"))
    .ToList();

Generated SQL:

SELECT * FROM Students WHERE Email LIKE '%@kathmandu.edu.np%'

Migrations

Migrations track database schema changes (e.g., adding a PhoneNumber column to Student).

sequenceDiagram
    participant Developer
    participant EFCore
    participant Database
    Developer->>EFCore: Add-Migration "AddPhoneNumber"
    EFCore->>Database: Create Migration Script
    Developer->>EFCore: Update-Database
    EFCore->>Database: Apply Changes

Example: Adding a Phone Field

dotnet ef migrations add AddPhoneNumber
dotnet ef database update

Database-First vs. Code-First

Real-World Applications

1. eSewa: Transaction Logging

• Idea Used: Code-First EF Core for Transaction and User entities.
• How: EF Core maps C# classes to SQL tables, storing user payments and service records.
• Example Query:

  var transactions = context.Transactions
      .Where(t => t.UserId == currentUser.Id && t.Status == "Completed")
      .OrderByDescending(t => t.Date);
  

2. Daraz: Inventory Management

• Idea Used: LINQ for Stock Queries.
• How: EF Core queries product stock levels in real-time:

  var lowStock = context.Products
      .Where(p => p.Stock < 10)
      .ToList();
  

3. NEPSE: Stock Market Data

• Idea Used: Migrations for Schema Updates.
• How: NEPSE’s database schema evolves (e.g., adding Dividend table) via EF Core migrations.

Exam Tip

• Focus Areas:
  • Differentiate DbContext, DbSet<T>, and Entity.
  • Write LINQ queries for filtering/sorting (e.g., Where(), OrderBy()).
  • Explain migrations with Add-Migration and Update-Database.
  • Compare Database-First vs. Code-First with pros/cons.
• Common Pitfalls:
  • Forgetting SaveChanges() after Add()/Remove().
  • Misusing Include() for lazy loading (use Eager Loading explicitly).
• Past Exam Patterns:
  • Trace a CRUD operation step-by-step (e.g., adding a Student).
  • Write a LINQ query for a given scenario (e.g., "Find all orders over $100").
  • Describe how EF Core prevents SQL injection (parameterized queries).