MediatR for Beginners: From Controller to Database

ASP.NET Core

Entity Framework Core

.NET

MSSQL

Published on Nov 28, 2025

20 MIN READ

MediatR for Beginners: From Controller to Database
Prerequisites
Project Structure
Step 1: Create the Solution
- Create Solution and Projects
- Add Project References
Step 2: Install Required Packages
Step 3: Create the Database Model
- Create Entity
- Create DTOs
Step 4: Setup Database Context
Step 5: Create MediatR Queries
Step 6: Create MediatR Commands
Step 7: Configure Services
- Configure appsettings.json
- Configure Program.cs
Step 8: Create Controller
Step 9: Database Migration
- Add Initial Migration
- Verify Database
Step 10: Create Views
Step 11: Testing the Application
Best Practices
Common Pitfalls
Advanced Topics
Summary

A comprehensive step-by-step guide to implementing MediatR in an ASP.NET Core MVC project with Entity Framework Core and a real database.

.NET 10 SDK (or .NET 8+)
SQL Server (or LocalDB, SQLite, PostgreSQL)
Basic understanding of C# and ASP.NET Core MVC
IDE (Visual Studio, VS Code, or Rider)

YourSolution/
├── YourSolution.sln
├── YourApp.Web/                    # ASP.NET Core MVC Project
│   ├── Controllers/
│   ├── Views/
│   └── Program.cs
├── YourApp.Business/               # Business Logic Layer
│   ├── Commands/
│   ├── Queries/
│   └── Handlers/
└── YourApp.Data/                   # Data Access Layer
    ├── DbContext/
    ├── Entities/
    └── Repositories/

# Create solution
dotnet new sln -n EmployeeManagement

# Create MVC Web Project
dotnet new mvc -n EmployeeManagement.Web

# Create Business Layer (Class Library)
dotnet new classlib -n EmployeeManagement.Business

# Create Data Layer (Class Library)
dotnet new classlib -n EmployeeManagement.Data

# Add projects to solution
dotnet sln add EmployeeManagement.Web/EmployeeManagement.Web.csproj
dotnet sln add EmployeeManagement.Business/EmployeeManagement.Business.csproj
dotnet sln add EmployeeManagement.Data/EmployeeManagement.Data.csproj

# Web references Business
dotnet add EmployeeManagement.Web/EmployeeManagement.Web.csproj reference EmployeeManagement.Business/EmployeeManagement.Business.csproj

# Business references Data
dotnet add EmployeeManagement.Business/EmployeeManagement.Business.csproj reference EmployeeManagement.Data/EmployeeManagement.Data.csproj

# Install in Business Layer
cd EmployeeManagement.Business
dotnet add package MediatR

# Install in Web Layer
cd ../EmployeeManagement.Web
dotnet add package MediatR

# Install in Data Layer
cd ../EmployeeManagement.Data
dotnet add package Microsoft.EntityFrameworkCore
dotnet add package Microsoft.EntityFrameworkCore.SqlServer
dotnet add package Microsoft.EntityFrameworkCore.Design

# Install EF Core Tools in Web Layer
cd ../EmployeeManagement.Web
dotnet add package Microsoft.EntityFrameworkCore.Design

cd ..
dotnet restore

File: EmployeeManagement.Data/Entities/Employee.cs

namespace EmployeeManagement.Data.Entities;

public class Employee
{
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Department { get; set; } = string.Empty;
    public decimal Salary { get; set; }
    public DateTime HireDate { get; set; }
    public bool IsActive { get; set; } = true;
    public DateTime CreatedAt { get; set; } = DateTime.UtcNow;
    public DateTime? UpdatedAt { get; set; }
}

File: EmployeeManagement.Business/Models/EmployeeDto.cs

namespace EmployeeManagement.Business.Models;

public class EmployeeDto
{
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string FullName => $"{FirstName} {LastName}";
    public string Email { get; set; } = string.Empty;
    public string Department { get; set; } = string.Empty;
    public decimal Salary { get; set; }
    public DateTime HireDate { get; set; }
    public bool IsActive { get; set; }
}

File: EmployeeManagement.Data/AppDbContext.cs

using EmployeeManagement.Data.Entities;
using Microsoft.EntityFrameworkCore;

namespace EmployeeManagement.Data;

public class AppDbContext : DbContext
{
    public AppDbContext(DbContextOptions<AppDbContext> options) : base(options)
    {
    }

    public DbSet<Employee> Employees { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Employee>(entity =>
        {
            entity.HasKey(e => e.Id);
            
            entity.Property(e => e.FirstName)
                .IsRequired()
                .HasMaxLength(100);
            
            entity.Property(e => e.LastName)
                .IsRequired()
                .HasMaxLength(100);
            
            entity.Property(e => e.Email)
                .IsRequired()
                .HasMaxLength(200);
            
            entity.Property(e => e.Department)
                .IsRequired()
                .HasMaxLength(100);
            
            entity.Property(e => e.Salary)
                .HasPrecision(18, 2);
            
            entity.HasIndex(e => e.Email)
                .IsUnique();
        });

        modelBuilder.Entity<Employee>().HasData(
            new Employee
            {
                Id = 1,
                FirstName = "John",
                LastName = "Doe",
                Email = "john.doe@company.com",
                Department = "IT",
                Salary = 75000,
                HireDate = new DateTime(2020, 1, 15),
                IsActive = true,
                CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
            },
            new Employee
            {
                Id = 2,
                FirstName = "Jane",
                LastName = "Smith",
                Email = "jane.smith@company.com",
                Department = "HR",
                Salary = 65000,
                HireDate = new DateTime(2019, 3, 20),
                IsActive = true,
                CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
            },
            new Employee
            {
                Id = 3,
                FirstName = "Bob",
                LastName = "Johnson",
                Email = "bob.johnson@company.com",
                Department = "Finance",
                Salary = 80000,
                HireDate = new DateTime(2021, 7, 10),
                IsActive = true,
                CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
            }
        );
    }
}

File: EmployeeManagement.Business/Queries/GetAllEmployeesQuery.cs

using EmployeeManagement.Business.Models;
using MediatR;

namespace EmployeeManagement.Business.Queries;

public class GetAllEmployeesQuery : IRequest<List<EmployeeDto>>
{
    public bool IncludeInactive { get; set; } = false;
}

File: EmployeeManagement.Business/Handlers/GetAllEmployeesQueryHandler.cs

using EmployeeManagement.Business.Models;
using EmployeeManagement.Business.Queries;
using EmployeeManagement.Data;
using MediatR;
using Microsoft.EntityFrameworkCore;

namespace EmployeeManagement.Business.Handlers;

public class GetAllEmployeesQueryHandler : IRequestHandler<GetAllEmployeesQuery, List<EmployeeDto>>
{
    private readonly AppDbContext _context;

    public GetAllEmployeesQueryHandler(AppDbContext context)
    {
        _context = context;
    }

    public async Task<List<EmployeeDto>> Handle(GetAllEmployeesQuery request, CancellationToken cancellationToken)
    {
        var query = _context.Employees.AsQueryable();

        if (!request.IncludeInactive)
        {
            query = query.Where(e => e.IsActive);
        }

        var employees = await query
            .OrderBy(e => e.LastName)
            .ThenBy(e => e.FirstName)
            .Select(e => new EmployeeDto
            {
                Id = e.Id,
                FirstName = e.FirstName,
                LastName = e.LastName,
                Email = e.Email,
                Department = e.Department,
                Salary = e.Salary,
                HireDate = e.HireDate,
                IsActive = e.IsActive
            })
            .ToListAsync(cancellationToken);

        return employees;
    }
}

File: EmployeeManagement.Business/Queries/GetEmployeeByIdQuery.cs

using EmployeeManagement.Business.Models;
using MediatR;

namespace EmployeeManagement.Business.Queries;

public class GetEmployeeByIdQuery : IRequest<EmployeeDto?>
{
    public int EmployeeId { get; set; }

    public GetEmployeeByIdQuery(int employeeId)
    {
        EmployeeId = employeeId;
    }
}

File: EmployeeManagement.Business/Handlers/GetEmployeeByIdQueryHandler.cs

using EmployeeManagement.Business.Models;
using EmployeeManagement.Business.Queries;
using EmployeeManagement.Data;
using MediatR;
using Microsoft.EntityFrameworkCore;

namespace EmployeeManagement.Business.Handlers;

public class GetEmployeeByIdQueryHandler : IRequestHandler<GetEmployeeByIdQuery, EmployeeDto?>
{
    private readonly AppDbContext _context;

    public GetEmployeeByIdQueryHandler(AppDbContext context)
    {
        _context = context;
    }

    public async Task<EmployeeDto?> Handle(GetEmployeeByIdQuery request, CancellationToken cancellationToken)
    {
        var employee = await _context.Employees
            .Where(e => e.Id == request.EmployeeId)
            .Select(e => new EmployeeDto
            {
                Id = e.Id,
                FirstName = e.FirstName,
                LastName = e.LastName,
                Email = e.Email,
                Department = e.Department,
                Salary = e.Salary,
                HireDate = e.HireDate,
                IsActive = e.IsActive
            })
            .FirstOrDefaultAsync(cancellationToken);

        return employee;
    }
}

File: EmployeeManagement.Business/Queries/SearchEmployeesQuery.cs

using EmployeeManagement.Business.Models;
using MediatR;

namespace EmployeeManagement.Business.Queries;

public class SearchEmployeesQuery : IRequest<List<EmployeeDto>>
{
    public string? SearchTerm { get; set; }
    public string? Department { get; set; }
}

File: EmployeeManagement.Business/Handlers/SearchEmployeesQueryHandler.cs

using EmployeeManagement.Business.Models;
using EmployeeManagement.Business.Queries;
using EmployeeManagement.Data;
using MediatR;
using Microsoft.EntityFrameworkCore;

namespace EmployeeManagement.Business.Handlers;

public class SearchEmployeesQueryHandler : IRequestHandler<SearchEmployeesQuery, List<EmployeeDto>>
{
    private readonly AppDbContext _context;

    public SearchEmployeesQueryHandler(AppDbContext context)
    {
        _context = context;
    }

    public async Task<List<EmployeeDto>> Handle(SearchEmployeesQuery request, CancellationToken cancellationToken)
    {
        var query = _context.Employees.Where(e => e.IsActive);

        if (!string.IsNullOrWhiteSpace(request.SearchTerm))
        {
            var searchTerm = request.SearchTerm.ToLower();
            query = query.Where(e => 
                e.FirstName.ToLower().Contains(searchTerm) ||
                e.LastName.ToLower().Contains(searchTerm) ||
                e.Email.ToLower().Contains(searchTerm));
        }

        if (!string.IsNullOrWhiteSpace(request.Department))
        {
            query = query.Where(e => e.Department == request.Department);
        }

        var employees = await query
            .OrderBy(e => e.LastName)
            .Select(e => new EmployeeDto
            {
                Id = e.Id,
                FirstName = e.FirstName,
                LastName = e.LastName,
                Email = e.Email,
                Department = e.Department,
                Salary = e.Salary,
                HireDate = e.HireDate,
                IsActive = e.IsActive
            })
            .ToListAsync(cancellationToken);

        return employees;
    }
}

File: EmployeeManagement.Business/Commands/CreateEmployeeCommand.cs

using EmployeeManagement.Business.Models;
using MediatR;

namespace EmployeeManagement.Business.Commands;

public class CreateEmployeeCommand : IRequest<EmployeeDto>
{
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Department { get; set; } = string.Empty;
    public decimal Salary { get; set; }
    public DateTime HireDate { get; set; } = DateTime.Today;
}

File: EmployeeManagement.Business/Handlers/CreateEmployeeCommandHandler.cs

using EmployeeManagement.Business.Commands;
using EmployeeManagement.Business.Models;
using EmployeeManagement.Data;
using EmployeeManagement.Data.Entities;
using MediatR;

namespace EmployeeManagement.Business.Handlers;

public class CreateEmployeeCommandHandler : IRequestHandler<CreateEmployeeCommand, EmployeeDto>
{
    private readonly AppDbContext _context;

    public CreateEmployeeCommandHandler(AppDbContext context)
    {
        _context = context;
    }

    public async Task<EmployeeDto> Handle(CreateEmployeeCommand request, CancellationToken cancellationToken)
    {
        var employee = new Employee
        {
            FirstName = request.FirstName,
            LastName = request.LastName,
            Email = request.Email,
            Department = request.Department,
            Salary = request.Salary,
            HireDate = request.HireDate,
            IsActive = true,
            CreatedAt = DateTime.UtcNow
        };

        _context.Employees.Add(employee);
        await _context.SaveChangesAsync(cancellationToken);

        return new EmployeeDto
        {
            Id = employee.Id,
            FirstName = employee.FirstName,
            LastName = employee.LastName,
            Email = employee.Email,
            Department = employee.Department,
            Salary = employee.Salary,
            HireDate = employee.HireDate,
            IsActive = employee.IsActive
        };
    }
}

File: EmployeeManagement.Business/Commands/UpdateEmployeeCommand.cs

using EmployeeManagement.Business.Models;
using MediatR;

namespace EmployeeManagement.Business.Commands;

public class UpdateEmployeeCommand : IRequest<EmployeeDto?>
{
    public int Id { get; set; }
    public string FirstName { get; set; } = string.Empty;
    public string LastName { get; set; } = string.Empty;
    public string Email { get; set; } = string.Empty;
    public string Department { get; set; } = string.Empty;
    public decimal Salary { get; set; }
}

File: EmployeeManagement.Business/Handlers/UpdateEmployeeCommandHandler.cs

using EmployeeManagement.Business.Commands;
using EmployeeManagement.Business.Models;
using EmployeeManagement.Data;
using MediatR;
using Microsoft.EntityFrameworkCore;

namespace EmployeeManagement.Business.Handlers;

public class UpdateEmployeeCommandHandler : IRequestHandler<UpdateEmployeeCommand, EmployeeDto?>
{
    private readonly AppDbContext _context;

    public UpdateEmployeeCommandHandler(AppDbContext context)
    {
        _context = context;
    }

    public async Task<EmployeeDto?> Handle(UpdateEmployeeCommand request, CancellationToken cancellationToken)
    {
        var employee = await _context.Employees
            .FirstOrDefaultAsync(e => e.Id == request.Id, cancellationToken);

        if (employee == null)
        {
            return null;
        }

        employee.FirstName = request.FirstName;
        employee.LastName = request.LastName;
        employee.Email = request.Email;
        employee.Department = request.Department;
        employee.Salary = request.Salary;
        employee.UpdatedAt = DateTime.UtcNow;

        await _context.SaveChangesAsync(cancellationToken);

        return new EmployeeDto
        {
            Id = employee.Id,
            FirstName = employee.FirstName,
            LastName = employee.LastName,
            Email = employee.Email,
            Department = employee.Department,
            Salary = employee.Salary,
            HireDate = employee.HireDate,
            IsActive = employee.IsActive
        };
    }
}

File: EmployeeManagement.Business/Commands/DeleteEmployeeCommand.cs

using MediatR;

namespace EmployeeManagement.Business.Commands;

public class DeleteEmployeeCommand : IRequest<bool>
{
    public int EmployeeId { get; set; }

    public DeleteEmployeeCommand(int employeeId)
    {
        EmployeeId = employeeId;
    }
}

File: EmployeeManagement.Business/Handlers/DeleteEmployeeCommandHandler.cs

using EmployeeManagement.Business.Commands;
using EmployeeManagement.Data;
using MediatR;
using Microsoft.EntityFrameworkCore;

namespace EmployeeManagement.Business.Handlers;

public class DeleteEmployeeCommandHandler : IRequestHandler<DeleteEmployeeCommand, bool>
{
    private readonly AppDbContext _context;

    public DeleteEmployeeCommandHandler(AppDbContext context)
    {
        _context = context;
    }

    public async Task<bool> Handle(DeleteEmployeeCommand request, CancellationToken cancellationToken)
    {
        var employee = await _context.Employees
            .FirstOrDefaultAsync(e => e.Id == request.EmployeeId, cancellationToken);

        if (employee == null)
        {
            return false;
        }

        // Soft delete
        employee.IsActive = false;
        employee.UpdatedAt = DateTime.UtcNow;

        await _context.SaveChangesAsync(cancellationToken);
        return true;
    }
}

File: EmployeeManagement.Web/appsettings.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "AllowedHosts": "*",
  "ConnectionStrings": {
    "DefaultConnection": "Server=localhost,1433;Database=EmployeeManagementDb;User Id=sa;Password=YourStrong!Passw0rd;TrustServerCertificate=True;Encrypt=True;"
  }
}

For SQLite (alternative):

{
  "ConnectionStrings": {
    "DefaultConnection": "Data Source=employees.db"
  }
}

File: EmployeeManagement.Web/Program.cs

using EmployeeManagement.Business.Queries;
using EmployeeManagement.Data;
using Microsoft.EntityFrameworkCore;

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllersWithViews();

// Register DbContext
builder.Services.AddDbContext<AppDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("DefaultConnection")));

// Register MediatR
builder.Services.AddMediatR(cfg => 
    cfg.RegisterServicesFromAssemblyContaining<GetAllEmployeesQuery>());

var app = builder.Build();

if (!app.Environment.IsDevelopment())
{
    app.UseExceptionHandler("/Home/Error");
    app.UseHsts();
}

app.UseHttpsRedirection();
app.UseRouting();
app.UseAuthorization();

app.MapStaticAssets();

app.MapControllerRoute(
    name: "default",
    pattern: "{controller=Home}/{action=Index}/{id?}")
    .WithStaticAssets();

app.Run();

File: EmployeeManagement.Web/Controllers/EmployeeController.cs

using EmployeeManagement.Business.Commands;
using EmployeeManagement.Business.Queries;
using MediatR;
using Microsoft.AspNetCore.Mvc;

namespace EmployeeManagement.Web.Controllers;

public class EmployeeController : Controller
{
    private readonly IMediator _mediator;
    private readonly ILogger<EmployeeController> _logger;

    public EmployeeController(IMediator mediator, ILogger<EmployeeController> logger)
    {
        _mediator = mediator;
        _logger = logger;
    }

    public async Task<IActionResult> Index()
    {
        try
        {
            var query = new GetAllEmployeesQuery();
            var employees = await _mediator.Send(query);
            return View(employees);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error retrieving employees");
            return View("Error");
        }
    }

    public async Task<IActionResult> Details(int id)
    {
        try
        {
            var query = new GetEmployeeByIdQuery(id);
            var employee = await _mediator.Send(query);

            if (employee == null)
            {
                return NotFound();
            }

            return View(employee);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error retrieving employee {EmployeeId}", id);
            return View("Error");
        }
    }

    public IActionResult Create()
    {
        return View();
    }

    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> Create(CreateEmployeeCommand command)
    {
        if (!ModelState.IsValid)
        {
            return View(command);
        }

        try
        {
            var employee = await _mediator.Send(command);
            TempData["SuccessMessage"] = $"Employee {employee.FullName} created successfully!";
            return RedirectToAction(nameof(Details), new { id = employee.Id });
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error creating employee");
            ModelState.AddModelError("", "An error occurred while creating the employee.");
            return View(command);
        }
    }

    public async Task<IActionResult> Edit(int id)
    {
        try
        {
            var query = new GetEmployeeByIdQuery(id);
            var employee = await _mediator.Send(query);

            if (employee == null)
            {
                return NotFound();
            }

            var command = new UpdateEmployeeCommand
            {
                Id = employee.Id,
                FirstName = employee.FirstName,
                LastName = employee.LastName,
                Email = employee.Email,
                Department = employee.Department,
                Salary = employee.Salary
            };

            return View(command);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error loading employee {EmployeeId} for edit", id);
            return View("Error");
        }
    }

    [HttpPost]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> Edit(int id, UpdateEmployeeCommand command)
    {
        if (id != command.Id)
        {
            return BadRequest();
        }

        if (!ModelState.IsValid)
        {
            return View(command);
        }

        try
        {
            var employee = await _mediator.Send(command);

            if (employee == null)
            {
                return NotFound();
            }

            TempData["SuccessMessage"] = $"Employee {employee.FullName} updated successfully!";
            return RedirectToAction(nameof(Details), new { id = employee.Id });
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error updating employee {EmployeeId}", id);
            ModelState.AddModelError("", "An error occurred while updating the employee.");
            return View(command);
        }
    }

    public async Task<IActionResult> Delete(int id)
    {
        try
        {
            var query = new GetEmployeeByIdQuery(id);
            var employee = await _mediator.Send(query);

            if (employee == null)
            {
                return NotFound();
            }

            return View(employee);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error loading employee {EmployeeId} for delete", id);
            return View("Error");
        }
    }

    [HttpPost, ActionName("Delete")]
    [ValidateAntiForgeryToken]
    public async Task<IActionResult> DeleteConfirmed(int id)
    {
        try
        {
            var command = new DeleteEmployeeCommand(id);
            var result = await _mediator.Send(command);

            if (!result)
            {
                return NotFound();
            }

            TempData["SuccessMessage"] = "Employee deleted successfully!";
            return RedirectToAction(nameof(Index));
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error deleting employee {EmployeeId}", id);
            TempData["ErrorMessage"] = "An error occurred while deleting the employee.";
            return RedirectToAction(nameof(Index));
        }
    }

    public async Task<IActionResult> Search(string searchTerm, string department)
    {
        try
        {
            var query = new SearchEmployeesQuery
            {
                SearchTerm = searchTerm,
                Department = department
            };

            var employees = await _mediator.Send(query);
            ViewBag.SearchTerm = searchTerm;
            ViewBag.Department = department;
            
            return View("Index", employees);
        }
        catch (Exception ex)
        {
            _logger.LogError(ex, "Error searching employees");
            return View("Error");
        }
    }
}

# Navigate to the Web project
cd EmployeeManagement.Web

# Add migration
dotnet ef migrations add InitialCreate --project ../EmployeeManagement.Data --context AppDbContext

# Apply migration to database
dotnet ef database update --project ../EmployeeManagement.Data --context AppDbContext

The database should now be created with:

Employees table
3 seeded employee records
All indexes and constraints

The controller expects views for each action. Create the following Razor views:

File: Views/Employee/Index.cshtml

@model List<EmployeeManagement.Business.Models.EmployeeDto>
@{
    ViewData["Title"] = "Employees";
}

<div class="container">
    <div class="row mb-3">
        <div class="col">
            <h2>Employee List</h2>
        </div>
        <div class="col text-end">
            <a asp-action="Create" class="btn btn-primary">
                Create New Employee
            </a>
        </div>
    </div>

    @if (TempData["SuccessMessage"] != null)
    {
        <div class="alert alert-success alert-dismissible fade show" role="alert">
            @TempData["SuccessMessage"]
            <button type="button" class="btn-close" data-bs-dismiss="alert"></button>
        </div>
    }

    <div class="card">
        <div class="card-body">
            <form asp-action="Search" method="get" class="row g-3 mb-3">
                <div class="col-md-5">
                    <input type="text" name="searchTerm" class="form-control" 
                           placeholder="Search by name or email..." 
                           value="@ViewBag.SearchTerm" />
                </div>
                <div class="col-md-4">
                    <select name="department" class="form-select">
                        <option value="">All Departments</option>
                        <option value="IT">IT</option>
                        <option value="HR">HR</option>
                        <option value="Finance">Finance</option>
                        <option value="Sales">Sales</option>
                    </select>
                </div>
                <div class="col-md-3">
                    <button type="submit" class="btn btn-secondary w-100">Search</button>
                </div>
            </form>

            <div class="table-responsive">
                <table class="table table-striped table-hover">
                    <thead>
                        <tr>
                            <th>ID</th>
                            <th>Full Name</th>
                            <th>Email</th>
                            <th>Department</th>
                            <th>Salary</th>
                            <th>Status</th>
                            <th>Actions</th>
                        </tr>
                    </thead>
                    <tbody>
                        @foreach (var employee in Model)
                        {
                            <tr>
                                <td>@employee.Id</td>
                                <td>@employee.FullName</td>
                                <td>@employee.Email</td>
                                <td><span class="badge bg-info">@employee.Department</span></td>
                                <td>@employee.Salary.ToString("C")</td>
                                <td>
                                    @if (employee.IsActive)
                                    {
                                        <span class="badge bg-success">Active</span>
                                    }
                                    else
                                    {
                                        <span class="badge bg-secondary">Inactive</span>
                                    }
                                </td>
                                <td>
                                    <a asp-action="Details" asp-route-id="@employee.Id" 
                                       class="btn btn-sm btn-info">Details</a>
                                    <a asp-action="Edit" asp-route-id="@employee.Id" 
                                       class="btn btn-sm btn-warning">Edit</a>
                                    <a asp-action="Delete" asp-route-id="@employee.Id" 
                                       class="btn btn-sm btn-danger">Delete</a>
                                </td>
                            </tr>
                        }
                    </tbody>
                </table>
            </div>

            @if (!Model.Any())
            {
                <div class="alert alert-info">No employees found.</div>
            }
        </div>
    </div>
</div>

File: Views/Employee/Details.cshtml

@model EmployeeManagement.Business.Models.EmployeeDto
@{
    ViewData["Title"] = "Employee Details";
}

<div class="container">
    <div class="row mb-3">
        <div class="col">
            <h2>Employee Details</h2>
        </div>
        <div class="col text-end">
            <a asp-action="Index" class="btn btn-secondary">Back to List</a>
        </div>
    </div>

    <div class="card">
        <div class="card-header bg-primary text-white">
            <h4>@Model.FullName</h4>
        </div>
        <div class="card-body">
            <div class="row">
                <div class="col-md-6">
                    <dl class="row">
                        <dt class="col-sm-4">Employee ID:</dt>
                        <dd class="col-sm-8">@Model.Id</dd>

                        <dt class="col-sm-4">First Name:</dt>
                        <dd class="col-sm-8">@Model.FirstName</dd>

                        <dt class="col-sm-4">Last Name:</dt>
                        <dd class="col-sm-8">@Model.LastName</dd>

                        <dt class="col-sm-4">Email:</dt>
                        <dd class="col-sm-8">@Model.Email</dd>
                    </dl>
                </div>
                <div class="col-md-6">
                    <dl class="row">
                        <dt class="col-sm-4">Department:</dt>
                        <dd class="col-sm-8">
                            <span class="badge bg-info">@Model.Department</span>
                        </dd>

                        <dt class="col-sm-4">Salary:</dt>
                        <dd class="col-sm-8">@Model.Salary.ToString("C")</dd>

                        <dt class="col-sm-4">Hire Date:</dt>
                        <dd class="col-sm-8">@Model.HireDate.ToString("MMM dd, yyyy")</dd>

                        <dt class="col-sm-4">Status:</dt>
                        <dd class="col-sm-8">
                            @if (Model.IsActive)
                            {
                                <span class="badge bg-success">Active</span>
                            }
                            else
                            {
                                <span class="badge bg-secondary">Inactive</span>
                            }
                        </dd>
                    </dl>
                </div>
            </div>
        </div>
        <div class="card-footer">
            <a asp-action="Edit" asp-route-id="@Model.Id" class="btn btn-warning">Edit</a>
            <a asp-action="Delete" asp-route-id="@Model.Id" class="btn btn-danger">Delete</a>
            <a asp-action="Index" class="btn btn-secondary">Back to List</a>
        </div>
    </div>
</div>

File: Views/Employee/Create.cshtml

@model EmployeeManagement.Business.Commands.CreateEmployeeCommand
@{
    ViewData["Title"] = "Create Employee";
}

<div class="container">
    <div class="row mb-3">
        <div class="col">
            <h2>Create New Employee</h2>
        </div>
        <div class="col text-end">
            <a asp-action="Index" class="btn btn-secondary">Back to List</a>
        </div>
    </div>

    <div class="card">
        <div class="card-body">
            <form asp-action="Create" method="post">
                <div asp-validation-summary="ModelOnly" class="alert alert-danger"></div>
                
                <div class="row">
                    <div class="col-md-6 mb-3">
                        <label asp-for="FirstName" class="form-label"></label>
                        <input asp-for="FirstName" class="form-control" />
                        <span asp-validation-for="FirstName" class="text-danger"></span>
                    </div>
                    <div class="col-md-6 mb-3">
                        <label asp-for="LastName" class="form-label"></label>
                        <input asp-for="LastName" class="form-control" />
                        <span asp-validation-for="LastName" class="text-danger"></span>
                    </div>
                </div>

                <div class="mb-3">
                    <label asp-for="Email" class="form-label"></label>
                    <input asp-for="Email" type="email" class="form-control" />
                    <span asp-validation-for="Email" class="text-danger"></span>
                </div>

                <div class="row">
                    <div class="col-md-6 mb-3">
                        <label asp-for="Department" class="form-label"></label>
                        <select asp-for="Department" class="form-select">
                            <option value="">-- Select Department --</option>
                            <option value="IT">IT</option>
                            <option value="HR">HR</option>
                            <option value="Finance">Finance</option>
                            <option value="Sales">Sales</option>
                        </select>
                        <span asp-validation-for="Department" class="text-danger"></span>
                    </div>
                    <div class="col-md-6 mb-3">
                        <label asp-for="Salary" class="form-label"></label>
                        <input asp-for="Salary" type="number" step="0.01" class="form-control" />
                        <span asp-validation-for="Salary" class="text-danger"></span>
                    </div>
                </div>

                <div class="mb-3">
                    <label asp-for="HireDate" class="form-label"></label>
                    <input asp-for="HireDate" type="date" class="form-control" />
                    <span asp-validation-for="HireDate" class="text-danger"></span>
                </div>

                <div class="mt-4">
                    <button type="submit" class="btn btn-primary">Create Employee</button>
                    <a asp-action="Index" class="btn btn-secondary">Cancel</a>
                </div>
            </form>
        </div>
    </div>
</div>

@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

File: Views/Employee/Edit.cshtml

@model EmployeeManagement.Business.Commands.UpdateEmployeeCommand
@{
    ViewData["Title"] = "Edit Employee";
}

<div class="container">
    <div class="row mb-3">
        <div class="col">
            <h2>Edit Employee</h2>
        </div>
        <div class="col text-end">
            <a asp-action="Index" class="btn btn-secondary">Back to List</a>
        </div>
    </div>

    <div class="card">
        <div class="card-body">
            <form asp-action="Edit" method="post">
                <input asp-for="Id" type="hidden" />
                <div asp-validation-summary="ModelOnly" class="alert alert-danger"></div>
                
                <div class="row">
                    <div class="col-md-6 mb-3">
                        <label asp-for="FirstName" class="form-label"></label>
                        <input asp-for="FirstName" class="form-control" />
                        <span asp-validation-for="FirstName" class="text-danger"></span>
                    </div>
                    <div class="col-md-6 mb-3">
                        <label asp-for="LastName" class="form-label"></label>
                        <input asp-for="LastName" class="form-control" />
                        <span asp-validation-for="LastName" class="text-danger"></span>
                    </div>
                </div>

                <div class="mb-3">
                    <label asp-for="Email" class="form-label"></label>
                    <input asp-for="Email" type="email" class="form-control" />
                    <span asp-validation-for="Email" class="text-danger"></span>
                </div>

                <div class="row">
                    <div class="col-md-6 mb-3">
                        <label asp-for="Department" class="form-label"></label>
                        <select asp-for="Department" class="form-select">
                            <option value="">-- Select Department --</option>
                            <option value="IT">IT</option>
                            <option value="HR">HR</option>
                            <option value="Finance">Finance</option>
                            <option value="Sales">Sales</option>
                        </select>
                        <span asp-validation-for="Department" class="text-danger"></span>
                    </div>
                    <div class="col-md-6 mb-3">
                        <label asp-for="Salary" class="form-label"></label>
                        <input asp-for="Salary" type="number" step="0.01" class="form-control" />
                        <span asp-validation-for="Salary" class="text-danger"></span>
                    </div>
                </div>

                <div class="mt-4">
                    <button type="submit" class="btn btn-primary">Save Changes</button>
                    <a asp-action="Details" asp-route-id="@Model.Id" 
                       class="btn btn-secondary">Cancel</a>
                </div>
            </form>
        </div>
    </div>
</div>

@section Scripts {
    @{await Html.RenderPartialAsync("_ValidationScriptsPartial");}
}

File: Views/Employee/Delete.cshtml

@model EmployeeManagement.Business.Models.EmployeeDto
@{
    ViewData["Title"] = "Delete Employee";
}

<div class="container">
    <div class="row mb-3">
        <div class="col">
            <h2>Delete Employee</h2>
        </div>
    </div>

    <div class="alert alert-danger">
        <h4>Are you sure you want to delete this employee?</h4>
        <p>This action cannot be undone.</p>
    </div>

    <div class="card">
        <div class="card-header bg-danger text-white">
            <h4>@Model.FullName</h4>
        </div>
        <div class="card-body">
            <dl class="row">
                <dt class="col-sm-3">Employee ID:</dt>
                <dd class="col-sm-9">@Model.Id</dd>

                <dt class="col-sm-3">Full Name:</dt>
                <dd class="col-sm-9">@Model.FullName</dd>

                <dt class="col-sm-3">Email:</dt>
                <dd class="col-sm-9">@Model.Email</dd>

                <dt class="col-sm-3">Department:</dt>
                <dd class="col-sm-9">
                    <span class="badge bg-info">@Model.Department</span>
                </dd>

                <dt class="col-sm-3">Salary:</dt>
                <dd class="col-sm-9">@Model.Salary.ToString("C")</dd>

                <dt class="col-sm-3">Hire Date:</dt>
                <dd class="col-sm-9">@Model.HireDate.ToString("MMM dd, yyyy")</dd>
            </dl>
        </div>
        <div class="card-footer">
            <form asp-action="DeleteConfirmed" method="post" class="d-inline">
                <input type="hidden" name="id" value="@Model.Id" />
                <button type="submit" class="btn btn-danger">Confirm Delete</button>
            </form>
            <a asp-action="Details" asp-route-id="@Model.Id" 
               class="btn btn-secondary">Cancel</a>
        </div>
    </div>
</div>

cd EmployeeManagement.Web
dotnet run

Navigate to http://localhost:5000/Employee to see your application in action.

List All: http://localhost:5000/Employee
View Details: http://localhost:5000/Employee/Details/1
Create New: http://localhost:5000/Employee/Create
Edit: http://localhost:5000/Employee/Edit/1
Delete: http://localhost:5000/Employee/Delete/1
Search: http://localhost:5000/Employee/Search?searchTerm=john

Each action should:

Send a request/command through MediatR
Handler processes it and interacts with database
Results returned to controller
View displays the data

1. Separation of Concerns

Commands for write operations (Create, Update, Delete)
Queries for read operations (Get, List, Search)
Handlers for business logic and data access
Controllers for request/response handling only

2. Use DTOs

Never expose database entities directly to views
Map entities to DTOs in handlers
Protects against over-posting attacks

3. Async/Await Pattern

Always use async operations with database
Improves scalability
Use CancellationToken for long-running operations

4. Error Handling

try
{
    var result = await _mediator.Send(command);
    return View(result);
}
catch (DbUpdateException ex)
{
    _logger.LogError(ex, "Database error");
    ModelState.AddModelError("", "Database error occurred");
}
catch (Exception ex)
{
    _logger.LogError(ex, "Unexpected error");
    return View("Error");
}

5. Validation

public class CreateEmployeeCommand : IRequest<EmployeeDto>
{
    [Required]
    [MaxLength(100)]
    public string FirstName { get; set; } = string.Empty;
    
    [Required]
    [EmailAddress]
    public string Email { get; set; } = string.Empty;
    
    [Range(0, 1000000)]
    public decimal Salary { get; set; }
}

1. Forgetting to Register MediatR

// ❌ Wrong
builder.Services.AddControllersWithViews();

// ✅ Correct
builder.Services.AddMediatR(cfg => 
    cfg.RegisterServicesFromAssemblyContaining<GetAllEmployeesQuery>());

2. Not Calling SaveChangesAsync

// ❌ Wrong
_context.Employees.Add(employee);
return employee;

// ✅ Correct
_context.Employees.Add(employee);
await _context.SaveChangesAsync(cancellationToken);
return employee;

3. Exposing Entities Instead of DTOs

// ❌ Wrong
public class GetEmployeeQuery : IRequest<Employee> { }

// ✅ Correct
public class GetEmployeeQuery : IRequest<EmployeeDto> { }

4. Not Using Async/Await Properly

// ❌ Wrong
var employees = _context.Employees.ToList();

// ✅ Correct
var employees = await _context.Employees.ToListAsync(cancellationToken);

5. Using DateTime.UtcNow in Seed Data

// ❌ Wrong - Causes migration issues
modelBuilder.Entity<Employee>().HasData(
    new Employee
    {
        Id = 1,
        CreatedAt = DateTime.UtcNow  // This changes every time!
    }
);

// ✅ Correct - Use static dates
modelBuilder.Entity<Employee>().HasData(
    new Employee
    {
        Id = 1,
        CreatedAt = new DateTime(2024, 1, 1, 0, 0, 0, DateTimeKind.Utc)
    }
);

6. Injecting DbContext in Controller

// ❌ Wrong - Controller knows about database
public class EmployeeController : Controller
{
    private readonly AppDbContext _context;
    
    public async Task<IActionResult> Index()
    {
        var employees = await _context.Employees.ToListAsync();
        return View(employees);
    }
}

// ✅ Correct - Controller uses MediatR
public class EmployeeController : Controller
{
    private readonly IMediator _mediator;
    
    public async Task<IActionResult> Index()
    {
        var query = new GetAllEmployeesQuery();
        var employees = await _mediator.Send(query);
        return View(employees);
    }
}

1. Pipeline Behaviors

Add cross-cutting concerns like logging, validation, caching:

public class LoggingBehavior<TRequest, TResponse> : IPipelineBehavior<TRequest, TResponse>
    where TRequest : IRequest<TResponse>
{
    private readonly ILogger<LoggingBehavior<TRequest, TResponse>> _logger;

    public LoggingBehavior(ILogger<LoggingBehavior<TRequest, TResponse>> logger)
    {
        _logger = logger;
    }

    public async Task<TResponse> Handle(TRequest request, RequestHandlerDelegate<TResponse> next, CancellationToken cancellationToken)
    {
        _logger.LogInformation("Handling {RequestName}", typeof(TRequest).Name);
        var response = await next();
        _logger.LogInformation("Handled {RequestName}", typeof(TRequest).Name);
        return response;
    }
}

// Register in Program.cs
builder.Services.AddTransient(typeof(IPipelineBehavior<,>), typeof(LoggingBehavior<,>));

2. Notifications (Domain Events)

public class EmployeeCreatedNotification : INotification
{
    public int EmployeeId { get; set; }
    public string FullName { get; set; }
}

public class SendWelcomeEmailHandler : INotificationHandler<EmployeeCreatedNotification>
{
    public Task Handle(EmployeeCreatedNotification notification, CancellationToken cancellationToken)
    {
        // Send welcome email
        return Task.CompletedTask;
    }
}

3. CQRS with Separate Read/Write Models

Different models for reads and writes
Optimize each for its purpose
Consider Event Sourcing for audit trails

You now have a complete MediatR implementation from MVC to Database!

What we achieved:

✅ Separation of concerns (Web → Business → Data)
✅ MediatR for request/response handling
✅ Entity Framework Core for database access
✅ CRUD operations with proper architecture
✅ Async/await throughout
✅ Error handling and logging
✅ Best practices and common pitfalls

Next steps:

Add FluentValidation for complex validation
Implement AutoMapper for entity-to-DTO mapping
Add unit tests for handlers
Implement pipeline behaviors
Add authentication/authorization
Consider CQRS patterns for complex scenarios

Happy coding! 🚀

Table of contents

MediatR for Beginners: From Controller to Database

Prerequisites

Project Structure

Step 1: Create the Solution

Create Solution and Projects

Add Project References

Step 2: Install Required Packages

Install MediatR

Install Entity Framework Core

Restore Packages

Step 3: Create the Database Model

Create Entity

Create DTOs

Step 4: Setup Database Context

Step 5: Create MediatR Queries

Get All Employees Query

Get Employee By ID Query

Search Employees Query

Step 6: Create MediatR Commands

Create Employee Command

Update Employee Command

Delete Employee Command

Step 7: Configure Services

Configure appsettings.json

Configure Program.cs

Step 8: Create Controller

Step 9: Database Migration

Add Initial Migration

Verify Database

Step 10: Create Views

Index View

Details View

Create View

Edit View

Delete View

Step 11: Testing the Application

Run the Application

Test Endpoints

Verify Database Operations

Best Practices

Common Pitfalls

Advanced Topics

Summary