Ultimate ASP.NET Full Course: Module 4 - Data Access with EF Core & Databases

 

Table of Contents

1. Module 4: Data Access with EF Core & Databases

   • 1.1 EF Core Overview

     • Code-First vs Database-First Workflows

   • 1.2 Migrations & Schema Evolution

   • 1.3 Querying

     • LINQ to Entities, Projections, Eager vs Lazy Loading

   • 1.4 CRUD Operations with Controllers

   • 1.5 Advanced Data Modeling

     • One-to-Many, Many-to-Many, Fluent API

   • 1.6 Performance

     • Query Optimization, No-Tracking Queries, Stored Procedures, Dapper Intro

   • 1.7 Security

     • Preventing SQL Injection, Secure Connection Strings

   • 1.8 Lab: Build a Blog System (Posts, Tags, Authors) with Full CRUD & Efficient LINQ Queries

     • Real-Life Example: Personal Blog Platform

     • Step-by-Step Code Implementation

     • Best Practices, Exception Handling, Pros/Cons, Alternatives

---

Introduction

Welcome to Module 4 of the Ultimate ASP.NET Full Course (2025 Edition)! This module is dedicated to mastering data access in ASP.NET Core using Entity Framework Core (EF Core), Microsoft's powerful ORM (Object-Relational Mapper). We'll explore how to persist and manage data efficiently, from basic setups to advanced modeling and performance tuning. Whether you're building a simple blog or a complex enterprise system, understanding EF Core is essential for handling databases like SQL Server, PostgreSQL, or SQLite.

We'll use real-life examples, such as a blogging platform where authors create posts tagged with categories, to make concepts relatable and practical. This guide is step-by-step, code-oriented, and data-focused, emphasizing user-friendly explanations, realistic scenarios, best practices, exception handling, pros/cons, and alternatives. The lab will guide you through building a full blog system with CRUD operations and optimized queries.

By the end of this module, you'll be equipped to design robust data layers for your web apps. Let's dive into EF Core and unlock the power of efficient data management!

---

1.1 EF Core Overview

Code-First vs Database-First Workflows

Entity Framework Core (EF Core) is an open-source ORM that bridges .NET objects and relational databases, allowing you to work with data using C# classes instead of raw SQL.

• Code-First Workflow: Define your data models in C# classes, and EF Core generates the database schema. Ideal for new projects or when domain logic drives the design.

  • Steps:

    1. Create POCO (Plain Old CLR Object) classes.

    2. Configure DbContext.

    3. Use migrations to create/update the database.

  • Example: Basic DbContext.

    using Microsoft.EntityFrameworkCore;
    
    public class BlogDbContext : DbContext
    {
        public DbSet<Post> Posts { get; set; }
    
        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseSqlServer("Server=localhost;Database=BlogDb;Trusted_Connection=True;");
        }
    }
    
    public class Post
    {
        public int Id { get; set; }
        public string Title { get; set; } = string.Empty;
        public string Content { get; set; } = string.Empty;
    }

• Database-First Workflow: Start with an existing database, and EF Core scaffolds models from it. Useful for legacy systems or when the database is designed first.

  • Steps:

    1. Install EF Core tools: dotnet tool install --global dotnet-ef.

    2. Scaffold: dotnet ef dbcontext scaffold "ConnectionString" Microsoft.EntityFrameworkCore.SqlServer -o Models.

  • Example: Scaffolding generates classes like the above from an existing DB.

Real-Life Analogy: Code-First is like building a house from blueprints (classes), while Database-First is renovating an existing house (DB) to fit your needs.

Real-Life Example: For a blog app, use Code-First to define Post, Author, and Tag classes, letting EF Core create tables automatically. In a corporate setting with an existing HR database, use Database-First to generate models for integration.

Best Practices:

• Use Code-First for greenfield projects to keep everything in code.

• Version control migrations scripts.

• Avoid mixing workflows in one project.

Exception Handling:

• Handle DbUpdateException for save failures.

  try
  {
      await _context.SaveChangesAsync();
  }
  catch (DbUpdateException ex)
  {
      // Log inner exception
      Console.WriteLine(ex.InnerException?.Message);
  }

Pros (Code-First):

• Full control over models.

• Easy testing with in-memory DB.

Cons:

• Manual schema management.

Pros (Database-First):

• Quick integration with existing DBs.

Cons:

• Generated code may need tweaks.

Alternatives:

• Dapper: Micro-ORM for raw SQL (lighter, faster).

• NHibernate: Another ORM with XML mappings.

To expand: EF Core supports providers for various DBs (e.g., UseMySql, UseSqlite). In .NET 9, it includes JSON column mapping for NoSQL-like features. Code-First allows data annotations ([Key], [Required]) or Fluent API for configurations. Database-First preserves DB constraints in models. For hybrid approaches, reverse-engineer DB then switch to Code-First. Performance-wise, Code-First migrations can be scripted for CI/CD. Exception handling includes optimistic concurrency with DbUpdateConcurrencyException. Pros overall: Productivity boost; cons: Overhead for simple queries. Alternatives like ADO.NET for low-level control.

In detailed scenarios, consider shadow properties in Code-First for fields not in models, or owned types for value objects. Realistics: A startup uses Code-First for rapid iteration, while a bank uses Database-First for compliance.

---

1.2 Migrations & Schema Evolution

Migrations in EF Core manage database schema changes over time, ensuring your DB evolves with your code.

• Creating Migrations:

  • Add: dotnet ef migrations add InitialCreate.

  • Applies code changes to DB schema.

• Applying Migrations:

  • Update DB: dotnet ef database update.

  • Or programmatically: context.Database.Migrate();.

• Schema Evolution:

  • Handle additions (new columns), modifications (data types), deletions (with caution).

  • Use seed data in migrations for initial population.

Example Migration: After adding a DatePublished property to Post:

public partial class AddDatePublished : Migration
{
    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.AddColumn<DateTime>(
            name: "DatePublished",
            table: "Posts",
            nullable: false,
            defaultValue: new DateTime(1, 1, 1, 0, 0, 0, 0, DateTimeKind.Unspecified));
    }

    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.DropColumn(
            name: "DatePublished",
            table: "Posts");
    }
}

Real-Life Analogy: Migrations are like versioned blueprints—each update adds/changes rooms without rebuilding the house.

Real-Life Example: In a blog evolving from simple posts to including authors, add a migration for the AuthorId foreign key, ensuring data integrity during deployment.

Best Practices:

• Name migrations descriptively (e.g., AddAuthorTable).

• Test migrations in staging environments.

• Use --idempotent for script generation.

Exception Handling:

• Catch MigrationException during application.

• Rollback with dotnet ef database update PreviousMigration.

Pros:

• Automated schema tracking.

• Reversible changes.

Cons:

• Complex conflicts in team environments.

• Performance impact on large DBs.

Alternatives:

• FluentMigrator: Code-based migrations without ORM.

• Manual SQL Scripts: For fine control.

Details: Migrations support custom SQL via migrationBuilder.Sql("SQL here");. In .NET 9, better support for temporal tables. For production, bundle migrations in app startup. Handle data loss in Down methods carefully. Realistics: Deploy via Azure DevOps pipelines. Pros: Version control integration; cons: Learning curve for custom ops.

---

1.3 Querying

LINQ to Entities, Projections, Eager vs Lazy Loading

Querying in EF Core uses LINQ for type-safe, composable queries translated to SQL.

• LINQ to Entities: Query DbSets like collections.

  • Example:

    var recentPosts = await _context.Posts
        .Where(p => p.DatePublished > DateTime.Now.AddDays(-30))
        .OrderByDescending(p => p.DatePublished)
        .ToListAsync();

• Projections: Select specific fields to reduce data transfer.

  • Example:

    var postTitles = await _context.Posts
        .Select(p => new { p.Title, p.Author.Name })
        .ToListAsync();

• Eager Loading: Load related data upfront with Include.

  • Example: _context.Posts.Include(p => p.Author).Include(p => p.Tags)

• Lazy Loading: Load on access (requires proxies or explicit loading).

  • Enable: optionsBuilder.UseLazyLoadingProxies();

  • Example: Access post.Author loads automatically.

Real-Life Analogy: LINQ is like a smart shopping list—Where filters, Select picks items, Include grabs extras.

Real-Life Example: Query blog posts with authors and tags eagerly for a dashboard, use projections for search results to show titles only.

Best Practices:

• Use AsNoTracking() for read-only queries.

• Avoid Cartesian explosions in includes.

• Profile SQL with logging.

Exception Handling:

• InvalidOperationException for invalid LINQ.

• Handle nulls with ? or checks.

Pros:

• Readable, compile-time checked queries.

• Flexible loading strategies.

Cons:

• N+1 problem in lazy loading.

• Complex LINQ can generate inefficient SQL.

Alternatives:

• Raw SQL: _context.Posts.FromSqlRaw("SELECT * FROM Posts")

• Dapper Queries: For performance.

Expand: LINQ supports joins, group by, aggregations (Sum, Average). Projections to DTOs for API responses. Eager vs Lazy: Eager for known needs, Lazy for optional. In .NET 9, improved split queries to avoid Cartesian. Realistics: Optimize for high-traffic blogs. Pros: Abstraction; cons: Overhead.

---

1.4 CRUD Operations with Controllers

CRUD (Create, Read, Update, Delete) integrates EF Core with MVC controllers for data manipulation.

• Read: Fetch data.

  public async Task<IActionResult> Index()
  {
      var posts = await _context.Posts.ToListAsync();
      return View(posts);
  }

• Create: Add new entities.

  [HttpPost]
  public async Task<IActionResult> Create(Post post)
  {
      if (ModelState.IsValid)
      {
          _context.Add(post);
          await _context.SaveChangesAsync();
          return RedirectToAction(nameof(Index));
      }
      return View(post);
  }

• Update: Modify existing.

  [HttpPost]
  public async Task<IActionResult> Edit(int id, Post post)
  {
      if (id != post.Id) return NotFound();
      if (ModelState.IsValid)
      {
          _context.Update(post);
          await _context.SaveChangesAsync();
          return RedirectToAction(nameof(Index));
      }
      return View(post);
  }

• Delete: Remove entities.

  [HttpPost]
  public async Task<IActionResult> DeleteConfirmed(int id)
  {
      var post = await _context.Posts.FindAsync(id);
      if (post != null)
      {
          _context.Posts.Remove(post);
          await _context.SaveChangesAsync();
      }
      return RedirectToAction(nameof(Index));
  }

Real-Life Analogy: CRUD is like managing a library—Read (browse), Create (add book), Update (edit details), Delete (remove).

Real-Life Example: Blog admin controller for managing posts: Create new articles, update drafts, delete spam.

Best Practices:

• Use async for scalability.

• Validate with ModelState.

• Use DTOs for input to prevent overposting.

Exception Handling:

• NotFound for missing IDs.

• Concurrency checks with row versions.

Pros:

• Seamless ORM integration.

• Transactional saves.

Cons:

• Attached entity issues in updates.

Alternatives:

• Repository Pattern: Abstract EF.

• MediatR: For CQRS.

Details: Inject DbContext via DI. Use FindAsync for PK lookups. In APIs, return ActionResults like CreatedAtAction.

---

1.5 Advanced Data Modeling

One-to-Many, Many-to-Many, Fluent API

Model relationships for complex data.

• One-to-Many: E.g., Author has many Posts.

  public class Author
  {
      public int Id { get; set; }
      public string Name { get; set; } = string.Empty;
      public List<Post> Posts { get; set; } = new List<Post>();
  }
  
  public class Post
  {
      public int Id { get; set; }
      public int AuthorId { get; set; }
      public Author? Author { get; set; }
  }

• Many-to-Many: E.g., Posts have many Tags.

  • EF Core 5+: Implicit join table.

    public class Tag
    {
        public int Id { get; set; }
        public string Name { get; set; } = string.Empty;
        public List<Post> Posts { get; set; } = new List<Post>();
    }
    
    // In Post: public List<Tag> Tags { get; set; } = new List<Tag>();

• Fluent API: Configure in OnModelCreating.

  protected override void OnModelCreating(ModelBuilder modelBuilder)
  {
      modelBuilder.Entity<Post>()
          .HasOne(p => p.Author)
          .WithMany(a => a.Posts)
          .HasForeignKey(p => p.AuthorId)
          .OnDelete(DeleteBehavior.Cascade);
  
      modelBuilder.Entity<Post>()
          .HasMany(p => p.Tags)
          .WithMany(t => t.Posts)
          .UsingEntity(j => j.ToTable("PostTags"));
  }

Real-Life Analogy: Relationships are like family trees—One-to-Many (parent-children), Many-to-Many (cousins).

Real-Life Example: Blog: Authors (one) write Posts (many), Posts tagged with multiple Tags.

Best Practices:

• Use cascade delete judiciously.

• Index foreign keys.

Exception Handling:

• Foreign key violations in saves.

Pros:

• Expressive models.

• Automatic joins.

Cons:

• Complex configs for inheritance.

Alternatives:

• Manual Joins: In SQL.

• GraphQL: For flexible querying.

Expand: Fluent for shadow FKs, owned entities. .NET 9: Better JSON support.

---

1.6 Performance

Query Optimization, No-Tracking Queries, Stored Procedures, Dapper Intro

Optimize EF Core for speed.

• Query Optimization: Use indexes, avoid Select *.

  • Log SQL: optionsBuilder.LogTo(Console.WriteLine);

• No-Tracking Queries: For read-only.

  var posts = await _context.Posts.AsNoTracking().ToListAsync();

• Stored Procedures: Execute via FromSql.

  var posts = await _context.Posts.FromSqlRaw("EXEC GetRecentPosts").ToListAsync();

• Dapper Intro: Lightweight ORM for SQL.

  • Install Dapper, example:

    using Dapper;
    using System.Data.SqlClient;
    
    using var connection = new SqlConnection(connectionString);
    var posts = await connection.QueryAsync<Post>("SELECT * FROM Posts");

Real-Life Analogy: Optimization is tuning a car—indexes are better roads, no-tracking lightens load.

Real-Life Example: High-traffic blog uses no-tracking for home page, stored procs for reports.

Best Practices:

• Profile with EF Core Power Tools.

• Batch updates.

Exception Handling:

• Timeout exceptions.

Pros:

• EF balances ease and perf.

• Dapper for raw speed.

Cons:

• EF overhead vs ADO.NET.

Alternatives:

• ADO.NET: Manual SQL.

• RepoDB: Hybrid ORM.

Details: Compiled queries in .NET 9. Dapper extensions for types.

---

1.7 Security

Preventing SQL Injection, Secure Connection Strings

Secure data access.

• Preventing SQL Injection: EF uses parameterized queries.

  • Avoid raw SQL with concatenation; use interpolation.

    var title = "Test"; // User input
    var posts = _context.Posts.FromSqlInterpolated($"SELECT * FROM Posts WHERE Title = {title}");

• Secure Connection Strings: Use User Secrets or environment vars.

  • In appsettings.json: "ConnectionStrings": { "Default": "..." }

  • Secrets: dotnet user-secrets set "ConnectionStrings:Default" "secure conn"

Real-Life Analogy: Parameterization is locking doors—injection can't break in.

Real-Life Example: Blog search uses parameterized LINQ to prevent attacks.

Best Practices:

• Always validate input.

• Use HTTPS for conn strings in config.

Exception Handling:

• SqlException for DB errors.

Pros:

• Built-in protections.

Cons:

• Misused raw SQL risks.

Alternatives:

• Stored Procs: Inherent params.

---

1.8 Lab: Build a Blog System (Posts, Tags, Authors) with Full CRUD & Efficient LINQ Queries

Real-Life Example: Personal Blog Platform

Build a blog app with Authors creating Posts, Posts having multiple Tags. Use EF Core Code-First, migrations, CRUD in MVC, optimized queries.

Step-by-Step Implementation

1. Setup Project:

   • dotnet new mvc -n BlogApp

   • Add EF Core: dotnet add package Microsoft.EntityFrameworkCore.SqlServer

   • dotnet add package Microsoft.EntityFrameworkCore.Tools

2. Models:

   • Author, Post, Tag as above.

3. DbContext:

   • With Fluent API.

4. Migrations:

   • Add and update.

5. Controllers/Views:

   • PostsController with CRUD.

6. Queries:

   • Efficient LINQ with includes, projections.

Code Example

BlogDbContext.cs

using Microsoft.EntityFrameworkCore;

public class BlogDbContext : DbContext
{
    public DbSet<Author> Authors { get; set; }
    public DbSet<Post> Posts { get; set; }
    public DbSet<Tag> Tags { get; set; }

    public BlogDbContext(DbContextOptions<BlogDbContext> options) : base(options) { }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Post>()
            .HasOne(p => p.Author)
            .WithMany(a => a.Posts)
            .HasForeignKey(p => p.AuthorId);

        modelBuilder.Entity<Post>()
            .HasMany(p => p.Tags)
            .WithMany(t => t.Posts);
    }
}

Models

public class Author
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public List<Post> Posts { get; set; } = new List<Post>();
}

public class Post
{
    public int Id { get; set; }
    public string Title { get; set; } = string.Empty;
    public string Content { get; set; } = string.Empty;
    public DateTime DatePublished { get; set; }
    public int AuthorId { get; set; }
    public Author? Author { get; set; }
    public List<Tag> Tags { get; set; } = new List<Tag>();
}

public class Tag
{
    public int Id { get; set; }
    public string Name { get; set; } = string.Empty;
    public List<Post> Posts { get; set; } = new List<Post>();
}

Program.cs (Partial)

builder.Services.AddDbContext<BlogDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

appsettings.json

{
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost;Database=BlogDb;Trusted_Connection=True;"
  }
}

PostsController.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using System.Threading.Tasks;

[Route("posts")]
public class PostsController : Controller
{
    private readonly BlogDbContext _context;

    public PostsController(BlogDbContext context)
    {
        _context = context;
    }

    // Read: Index with efficient query
    [HttpGet("")]
    public async Task<IActionResult> Index()
    {
        var posts = await _context.Posts
            .AsNoTracking()
            .Include(p => p.Author)
            .Include(p => p.Tags)
            .OrderByDescending(p => p.DatePublished)
            .ToListAsync();
        return View(posts);
    }

    // Create
    [HttpGet("create")]
    public IActionResult Create()
    {
        return View();
    }

    [HttpPost("create")]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> Create(Post post)
    {
        if (ModelState.IsValid)
        {
            _context.Add(post);
            await _context.SaveChangesAsync();
            return RedirectToAction(nameof(Index));
        }
        return View(post);
    }

    // Update (similar to Create, with FindAsync)

    // Delete
    [HttpPost("delete/{id}")]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> Delete(int id)
    {
        var post = await _context.Posts.FindAsync(id);
        if (post == null) return NotFound();
        _context.Posts.Remove(post);
        await _context.SaveChangesAsync();
        return RedirectToAction(nameof(Index));
    }

    // Efficient Search Query
    [HttpGet("search")]
    public async Task<IActionResult> Search(string query)
    {
        var results = await _context.Posts
            .Where(p => p.Title.Contains(query) || p.Content.Contains(query))
            .Select(p => new { p.Title, p.DatePublished }) // Projection
            .ToListAsync();
        return View(results);
    }
}

Views/Posts/Index.cshtml (Partial)

@model IEnumerable<Post>

<h2>Blog Posts</h2>
<table class="table">
    @foreach (var post in Model)
    {
        <tr>
            <td>@post.Title</td>
            <td>@post.Author?.Name</td>
            <td>@string.Join(", ", post.Tags.Select(t => t.Name))</td>
        </tr>
    }
</table>

How to Run

1. Setup project, add models/DbContext.

2. Migrations: dotnet ef migrations add Initial, dotnet ef database update.

3. Run dotnet run.

4. Access /posts for CRUD.

Explanation

• Models/Relationships: One-to-Many (Author-Posts), Many-to-Many (Posts-Tags).

• Queries: Eager loading, no-tracking, projections.

• CRUD: Async, validated.

Best Practices

• DI for DbContext (Scoped).

• Seed data in migrations.

Exception Handling

• DbUpdateException in saves.

Pros

• Full-featured blog data layer.

Cons

• In-memory alternatives for testing.

Alternatives

• MongoDB with NoSQL ORM.

This lab is comprehensive; extend with auth, paging.

---

Conclusion

Module 4 has covered EF Core in depth, from basics to advanced, with a practical blog lab.

📘Free Job Preparation Zone 🎯 Visit Free Learning Zone 📘Master ASP.NET: Complete Course Outline from Beginner to Advanced