C# Repository Design Pattern for Database Operations in a .NET Core 3.1 MVC Web App

Introduction

When building applications, it is important to consider how and where you're conducting database operations.

Entity Framework Database Context (DbContext) and the Controller

Building a basic template for a .NET Core 3.1 application using a scaffolding approach like the one from this Microsoft tutorial is a great starting point. Firstly, let's have a look at a small code snippet generated from the scaffolding.

In this example, the PusheenController class has actions for CRUD (Create, Read, Update and Delete) operations against the database. Here, we are directly interacting with the Entity Framework DbContext class called PusheenCustomExportCsvContext and retrieving data about Pusheens from the database. The PusheenCustomExportCsvContext is injected as a dependency into the PusheenController. In this web app, dependencies are added to the service container in the ConfigureServices method in Startup.cs.

However, it is easy to end up with big controllers; big in the sense that there's a lot of database operations logic built into the controller. Since the DbContext is a dependency of the controller, a further issue faced is if you were to test this, you would have to mock the DbSet and DbContext. It is definitely achieveable to mock we like; but we would have to mock the Provider, Expression, ElementType properties and GetEnumerator() method.

In larger applications, we would like to separate the concerns out into layers that are responsible for the business logic, presentation, database etc.

Example 1: DbContext and PusheenController

//Code omitted for brevity :)
namespace PusheenCustomExportCsv.Web.Controllers
{
    public class PusheenController : Controller
    {
        private readonly PusheenCustomExportCsvContext _context;

        public PusheenController(PusheenCustomExportCsvContext context)
        {
            _context = context;
        }

        //Code omitted for brevity :)

        // GET: Pusheen/Details/5
        public async Task<IActionResult> Details(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var pusheen = await _context.Pusheens
                .SingleOrDefaultAsync(m => m.ID == id);
            if (pusheen == null)
            {
                return NotFound();
            }

            return View(pusheen);
        }

        // GET: Pusheen/Create
        public IActionResult Create()
        {
            return View();
        }

        // POST: Pusheen/Create
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create([Bind("Id,Name,FavouriteFood,SuperPower")] Pusheen pusheen)
        {
            if (ModelState.IsValid)
            {
                _context.Add(pusheen);
                await _context.SaveChangesAsync();
                return RedirectToAction("Index");
            }
            return View(pusheen);
        }

What is the goal of the Repository Design Pattern and why is it useful?

Let's assume we would like a presentation layer made up of controllers and views, and a service layer for the business logic and database operations. We can create a repository (in this case lumped into the service for simplicity) where our database operations and logic can sit.

The repository is in charge of interacting with the Entity Framework DbContext class, so the controller doesn't have to.

Repository: Defining and Implementing the Interface

In this interface, we define an IPusheenService and implement the interface in the PusheenService.

Example 2: IPusheenService

//Code omitted for brevity :)
namespace PusheenCustomExportCsv.Web.Services
{
    public interface IPusheenService
    {
        Task<List<Pusheen>> GetAllAsync();
        Task<Pusheen> Create(Pusheen pusheen);
        Task<Pusheen> Update(Pusheen pusheen);
        Task<Pusheen> Delete(Pusheen pusheen);
        Task<Pusheen> FindPusheenAsync(int? id);
        Task<Pusheen> FindPusheenById(int? id);
        bool PusheenExists(int id);

    }
}

Below is an example of how PusheenService implements FindPusheenAsync and FindPusheenById. These database operations were originally coded directly into the controller as we saw in Example 1.

Example 3: PusheenService

//Code omitted for brevity :)

        public async Task<Pusheen> FindPusheenAsync(int? id)
        {
            var pusheen = await _context.Pusheens.FindAsync(id);
            return pusheen;
        }

//Code omitted for brevity :)

        public async Task<Pusheen> FindPusheenById(int? id)
        {
            var pusheen = await _context.Pusheens
                .FirstOrDefaultAsync(m => m.Id == id);
            return pusheen;
        }

//Code omitted for brevity :)

Let's see what our controller looks like now. The key difference is that the PusheenController is a lot slimmer and we don't need to interact with the DbContext directly anymore; that's the job of the repository now! :)

Example 4: PusheenController

//Code omitted for brevity :)
namespace PusheenCustomExportCsv.Web.Controllers
{
    public class PusheenController : Controller
    {
        private readonly IPusheenService _pusheenService;

        public PusheenController(IPusheenService pusheenService)
        {
            _pusheenService = pusheenService;
        }

//Code omitted for brevity :)

        // GET: Pusheen/Details/5
        public async Task<IActionResult> Details(int? id)
        {
            if (id == null)
            {
                return NotFound();
            }

            var pusheen = await _pusheenService.FindPusheenById(id);

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

            return View(pusheen);
        }

        // GET: Pusheen/Create
        public IActionResult Create()
        {
            return View();
        }

        // POST: Pusheen/Create
        // To protect from overposting attacks, enable the specific properties you want to bind to, for 
        // more details, see http://go.microsoft.com/fwlink/?LinkId=317598.
        [HttpPost]
        [ValidateAntiForgeryToken]
        public async Task<IActionResult> Create([Bind("Id,Name,FavouriteFood,SuperPower")] Pusheen pusheen)
        {
            if (ModelState.IsValid)
            {
                await _pusheenService.Create(pusheen);
                return RedirectToAction(nameof(Index));
            }
            return View(pusheen);
        }
//Code omitted for brevity :)

Final Thoughts

I hope you find this post useful and being part of my coding journey! Thank you for reading my blog! :)

Link to Github Repo

You can find the link to my Github repo with the simple web app example here.