Best Design Pattern for Data Access in C#

There are a lot of useful, great, and time-tested design patterns that you can use in your projects. For data access, there is a well-known pattern called the Repository pattern. I have done it for several projects, but to be honest, I’m not a huge fan of the Repository pattern, so for my next project, I chose not to use it.

[wpsm_toplist]

In this post, I will share with you what approach I have taken, and so far I like this approach. In my opinion, the design pattern I’ve chosen is very intuitive, clean, easy to understand, makes the queries to be easily testable.

What I like the most about this approach, is that there’s a clear separation between the queries and the rest of your project. One class, one query.

For this post, I’ve created a new solution and replicated the data access related bits. This is how the solution structure looks like:

DataAccess project

The DataAccess project is a class library project containing 3 folders:

Contexts. This folder contains DBContexts. In this case there’s only one DbContext – ApplicationDBContext.

Entities. This folder contains entity classes. In this case, there’s only one entity – Post.

Operations. This folder contains interfaces that define how all the queries will look like. In reality, we perform these types of queries (or the Operations as I called it):

IAction – use this interface when you need to perform a data modification query in the database, e.g. Delete or Update. This interface gives you two flavors – execute a parameterized query and get the resulting output (IEnumerable), or just perform some action and get the result (IEnumerable).

using System.Collections.Generic;

namespace DataAccess.Operations
{
    public interface IAction<in TActionData, out TActionResult>
        where TActionData : ActionData
    {
        IEnumerable<TActionResult> Execute(TActionData actionData);
    }

    public interface IAction<out TActionResult>
    {
        IEnumerable<TActionResult> Execute();
    }
}

ISingleAction – use this interface when you need to perform a data modification query in the database, e.g. Delete or Update. This interface gives you two flavors – execute a parameterized query and get the resulting output (single item), or just perform some action and get the result (single item).

namespace DataAccess.Operations
{
    public interface ISingleAction<in TActionData, out TActionResult>
        where TActionData : ActionData
    {
        TActionResult Execute(TActionData actionData);
    }

    public interface ISingleAction<out TActionResult>
    {
        TActionResult Execute();
    }
}

INoResultAction – use this interface when you need to perform a data modification query in the database, e.g. Delete or Update. This interface gives you two flavors – execute a parameterized action query and get the resulting output, or just perform some action. This interface will not give you any resulting output.

namespace DataAccess.Operations
{
    public interface INoResultAction<in TActionData>
        where TActionData : ActionData
    {
        void Execute(TActionData actionData);
    }

    public interface INoResultAction
    {
        void Execute();
    }
}

IQuery – use this interface when you need to fetch the data (SELECT). This interface gives you two flavors – execute a parameterized query and get the resulting output (IEnumerable), or just fetch all data without any parameters.

using System.Collections.Generic;

namespace DataAccess.Operations
{
    public interface IQuery<in TQueryData, out TQueryResult>
        where TQueryData : QueryData
    {
        IEnumerable<TQueryResult> Execute(TQueryData queryData);
    }

    public interface IQuery<out TQueryResult>
    {
        IEnumerable<TQueryResult> Execute();
    }
}

IPagedQuery – use this interface when you need to fetch the paged data (SELECT). This interface gives you a single flavor – execute a parameterized query and get the resulting paged output (IEnumerable).

namespace DataAccess.Operations
{
    public interface IPagedQuery<TQueryData, TQueryResult>
        where TQueryData : PagedQueryData
    {
        PagedQueryResult<TQueryResult> Execute(TQueryData queryData);
    }
}

ISingleQuery – use this interface when you need to fetch the data (SELECT). This interface gives you two flavors – execute a parameterized query and get the resulting output (single item), or just fetch a single data item without passing any parameters.

namespace DataAccess.Operations
{
    public interface ISingleQuery<in TQueryData, out TQueryResult>
        where TQueryData : QueryData
    {
        TQueryResult Execute(TQueryData queryData);
    }

    public interface ISingleQuery<out TQueryResult>
    {
        TQueryResult Execute();
    }
}

Queries project

This project is a class library project and store all interfaces that define queries that inherit from one of the Operations interfaces. So, if you want to get a single post from the Blog database, you would have a query interface called something like IGetPostQuery. Similarly, if you need to get multiple posts, e.g. a page of posts, you’d have a query interface called IGetPagedPostsQuery.

This is how these queries would look like.

IGetPostQuery

using System;
using DataAccess.Operations;

namespace Queries.Posts
{
    public interface IGetPostQuery : ISingleQuery<GetPostQueryData, GetPostQueryResult>
    { }

    public class GetPostQueryData : QueryData
    {
        public Guid PostId { get; set; }
    }

    public class GetPostQueryResult
    {
        public Guid Id { get; set; }

        public string Title { get; set; }

        public string Content { get; set; }

        public DateTime CreatedAt { get; set; }
    }
}

IGetPagedPostsQuery

using System;
using DataAccess.Operations;

namespace Queries.Posts
{
    public interface IGetPagedPostsQuery : IPagedQuery<GetPagedPostsQueryData, GetPagedPostsQueryResult>
    { }

    public class GetPagedPostsQueryData : PagedQueryData
    {
        public string Title { get; set; }
    }

    public class GetPagedPostsQueryResult
    {
        public Guid Id { get; set; }

        public string Title { get; set; }

        public string Content { get; set; }

        public DateTime CreatedAt { get; set; }
    }
}

As you can see in the queries code, queries have their own models, one as a parameter, and one as a result output. What I like about this query design is that you don’t need to think of how you will name your models; they are always after the name of the query itself. This way you have consistency around how you write queries and their models.

Let’s have a look at how to implement those queries.

Queries.Implementation

This is a class library project and as the project name suggests, this project store query implementations. It’s not necessary to have a separate project created for that, could have the same within the Queries project, but for some reason, I’ve decided to have the implementations in a separate project.

GetPostQuery

using Queries.Posts;
using System.Linq;
using DataAccess.Contexts;

namespace Queries.Implementation.Posts
{
    public class GetPostQuery : IGetPostQuery
    {
        private readonly ApplicationDbContext _context;

        public GetPostQuery(ApplicationDbContext context)
        {
            _context = context;
        }

        public GetPostQueryResult Execute(GetPostQueryData queryData)
        {
            var post = _context.Posts
                .Where(x => x.Id == queryData.PostId)
                .Select(x => new GetPostQueryResult
                {
                    Id = x.Id,
                    Title = x.Title,
                    Content = x.Content,
                    CreatedAt = x.CreatedAt
                })
                .FirstOrDefault();

            return post;
        }
    }
}

GetPagedPostsQuery

using Queries.Posts;
using System.Linq;
using DataAccess.Contexts;
using DataAccess.Operations;

namespace Queries.Implementation.Posts
{
    public class GetPagedPostsQuery : IGetPagedPostsQuery
    {
        private readonly ApplicationDbContext _context;

        public GetPagedPostsQuery(ApplicationDbContext context)
        {
            _context = context;
        }

        public PagedQueryResult<GetPagedPostsQueryResult> Execute(GetPagedPostsQueryData queryData)
        {
            // Build the query but not execute it
            var posts = _context.Posts
                .Where(x => x.Title.Contains(queryData.Title))
                .Select(x => new GetPagedPostsQueryResult
                {
                    Id = x.Id,
                    Title = x.Title,
                    Content = x.Content,
                    CreatedAt = x.CreatedAt
                });

            // Build paged result
            var pagedResult = new PagedQueryResult<GetPagedPostsQueryResult>(posts, queryData.PageSize, queryData.PageNumber);

            // Return
            return pagedResult;
        }
    }
}

WebApp project

Now that we have queries and their implementations, we can have a look at how to execute them from within the application. For that, I’ve created a simple ASP.NET Core MVC project.

First, we need to bootstrap those queries by adding some lines in the Startup class (almost all code from the class was removed for brevity):

using Microsoft.AspNetCore.Builder;
using Microsoft.AspNetCore.Hosting;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Configuration;
using Microsoft.Extensions.DependencyInjection;
using Microsoft.Extensions.Hosting;
using Queries.Implementation.Posts;
using Queries.Posts;
using DataAccess.Contexts;

namespace WebApp
{
    public class Startup
    {
        public void ConfigureServices(IServiceCollection services)
        {
            BoostrapQueries(services);
        }

        private void BoostrapQueries(IServiceCollection services)
        {
            services.AddScoped<IGetPostQuery, GetPostQuery>();
            services.AddScoped<IGetPagedPostsQuery, GetPagedPostsQuery>();
            services.AddScoped<ICreatePostActionQuery, CreatePostActionQuery>();
            services.AddScoped<IDeletePostActionQuery, DeletePostActionQuery>();
        }
    }
}

You may think, with this design pattern for data access, you will end up with lots of interfaces, and you will always have to manually register those queries with the IoC container, etc. I have to admit that’s the downside of this design, however, if you don’t want to add each query interface manually, you can always use a third-party IoC container and register queries based on the naming convention. It means you can bind interfaces with the concrete classes that contain word “Query” in their names.

Here’s how I’ve done it using Autofac. This is taken from my other project, but you will get the idea.

using Autofac;

namespace Hsn.Queries.Bootstrap
{
    public class QueriesModule : Module
    {
        protected override void Load(ContainerBuilder builder)
        {
            builder.RegisterAssemblyTypes(ThisAssembly)
                .Where(t => t.Name.Contains("Query"))
                .AsImplementedInterfaces();
        }
    }
}

And lastly, here’s how to use queries from within the web application controllers (or from anywhere else you need queries).

using System;
using System.Diagnostics;
using Microsoft.AspNetCore.Mvc;
using Microsoft.Extensions.Logging;
using Queries.Posts;
using WebApp.Models;

namespace WebApp.Controllers
{
    public class HomeController : Controller
    {
        private readonly IGetPostQuery _getPostQuery;
        private readonly IGetPagedPostsQuery _getPagedPostsQuery;
        private readonly ICreatePostActionQuery _createPostActionQuery;
        private readonly IDeletePostActionQuery _deletePostActionQuery;

        public HomeController(
            IGetPostQuery getPostQuery,
            IGetPagedPostsQuery getPagedPostsQuery,
            ICreatePostActionQuery createPostActionQuery,
            IDeletePostActionQuery deletePostActionQuery)
        {
            _getPostQuery = getPostQuery;
            _getPagedPostsQuery = getPagedPostsQuery;
            _createPostActionQuery = createPostActionQuery;
            _deletePostActionQuery = deletePostActionQuery;
        }

        public IActionResult Index()
        {
            // Get single post
            var singlePost = _getPostQuery.Execute(new GetPostQueryData
            {
                PostId = Guid.Parse("920241CA-8DFB-4A8F-A370-3C0B6AFC195B")
            });

            // Get paged posts
            var pagedResult = _getPagedPostsQuery.Execute(new GetPagedPostsQueryData
            {
                Title = "Lorem",
                PageNumber = 2,
                PageSize = 4
            });

            var pagedPosts = pagedResult.Data;

            // Create post and get its Id
            var newPostId = _createPostActionQuery.Execute(new CreatePostActionQueryData
            {
                Title = "New Post Title",
                Content = "Lots of content"
            });

            // Delete post
            _deletePostActionQuery.Execute(new DeletePostsActionQueryData { PostId = Guid.NewGuid() });

            return View();
        }
    }
}

Conclusion

The reasons why I think this is a great design pattern for data access are these:

[wpsm_list type=”arrow”]

• Highly readable
• Highly testable
• Clear separation between the queries
• Easy to implement
• Can use with any data sources

[/wpsm_list]

I hope all makes sense, complete code of the solution is available in my Github profile.I hope all makes sense,