Repository Pattern (C#)

There are some really good articles out there about the repository pattern but I haven’t seen one that strongly demonstrates different data providers, sample code, or some of the tricks I’ve learned. I’ve used this pattern at almost every .NET job I’ve ever had. It’s very common and I’ve learned a few tricks over the years. I’ve set up a sample Repository Pattern Project on GitHub that will allow you to clone it. This project is an MVC application built on Domain-Driven Design (DDD) principles. It’s built with Docker and creates an MSSQL Server, MongoDB, and a Postgres server. There are 4 repositories that interface each of those databases and a fake repository for unit testing. I even threw in some Dapper.

NOTE: my wordpress is broke for previewing.. so I’m editing and working on this RIGHT now.. check back 7/13/2020

What you will learn:

  • Repository Pattern
  • Dapper with MSSQL Stored Procedures
  • MongoDB
  • Unit Testing with Mock
  • Dependency Injection
  • How to change data providers with ease

Getting Started

You will need to clone this repository from GitHub and run Docker Compose. This will set up Docker containers for MSSQL, MongoDB, and PostgreSQL. The MSSQL container auto seeds the database with stored procedures and everything to start with.

GitHub: Repository Pattern

git clone https://github.com/mrjamiebowman-blog/C-Sharp-Repository-Pattern.git

Then you will need to cd into that directory to run Docker Compose.

docker-compose up

Repository Pattern Anatomy

The repository pattern is fairly simple. It’s very easy to decouple code and replace a data provider in the future by encapsulating that logic within this pattern. It’s also very easy to work with unit testing with this pattern because a fake repository can be created and used.

Repository Interface

The repository interface defines what methods are expected from any repository that derives from this interface.

The name of the interface should always be generic.

ICustomersRepository is more generic than ICustomersSqlRepository.

Tips: use dynamic/object instead of integers for an anonymous parameter value. Certain data sources may use different types of identifiers. While MSSQL commonly uses integers, it could use a composite key or MongoDB for example uses alphanumeric keys while Dynamics CRM/D365 uses Guids to identify records. This follows the open/closed principle where this is open for extension but closed for modification.

int id = 4; // mssql
string id = “5f0a888694e331568c891831”; // mongodb
string id = “D1822037-16D0-46B3-89D1-9B666A796059”; // dynamics crm

public interface ICustomersRepository
{
    Task<Customer> GetByIdAsync(dynamic id);
    Task<List<Customer>> GetCustomersAsync();
    Task DeleteByIdAsync(dynamic id);
    Task<Customer> CreateAsync(Customer model);
    Task<Customer> SaveAsync(Customer model, bool upsert = true);
    Task InitDb();
}

Dependency Injection

During the Startup.cs process the ConfigureServices method is called. This is where dependency injection must be configured.

Manual Configuration

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();

    services.AddTransient<IDataService, DataService>();
    services.AddTransient<ICustomersRepository, SqlCustomersRepository>();
}

One way to do this is to manually configure this in the Startup.cs like above. Each entity will have a repository so this could become cumbersome.

Alternative Configuration

An alternative would be to create a static method on the DataService class that handles the mapping. This would leave all of the dependency injection logic in the RepositoryPattern.Data library which could be beneficial, especially if this were a NuGet library or a Git submodule that was being reused across multiple projects.

public void ConfigureServices(IServiceCollection services)
{
    services.AddControllersWithViews();
    DataService.ConfigureServices(services);
}

DataService.cs

public static void ConfigureServices(IServiceCollection services)
{
    services.AddTransient<IDataService, DataService>();
    services.AddTransient<ICustomersRepository, SqlCustomersRepository>();
}

Facade Service (DataService.cs)

Repository code should do one thing, interface the data provider. If there is explicit logic happening in the repository code then you’re doing it wrong. I typically wrap all of my logic in a facade service. This allows for easy unit testing, dependency injection, and overall it’s much more reusable.

In the example below we will have a method for GetCustomerAgeAsync() which will return an integer. This is the exact kind of logic that should not happen in the repository.

public class DataService : IDataService
{
    private readonly ICustomersRepository _customersRepository;

    public DataService()
    {

    }

    public static void ConfigureServices(IServiceCollection services)
    {
        services.AddTransient<IDataService, DataService>();
        services.AddTransient<ICustomersRepository, SqlCustomersRepository>();
        //services.AddTransient<ICustomersRepository, MongoCustomersRepository>();
    }

    public DataService(ICustomersRepository customersRepository) : this()
    {
        _customersRepository = customersRepository;
    }

    #region Customers

    public async Task<Customer> CreateCustomerAsync(Customer model)
    {
        return await _customersRepository.CreateAsync(model);
    }

    public async Task DeleteCustomerByIdAsync(dynamic id)
    {
        await _customersRepository.DeleteByIdAsync(id);
    }

    public async Task<Customer> GetCustomerByIdAsync(dynamic id)
    {
        return await _customersRepository.GetByIdAsync(id);
    }

    public async Task<List<Customer>> GetCustomersAsync()
    {
         return await _customersRepository.GetCustomersAsync();
    }

    public async Task<Customer> SaveCustomerAsync(Customer model, bool upsert = true)
    {
        return await _customersRepository.SaveAsync(model, upsert);
    }

    public Task<int> GetCustomerAgeAsync(Customer model)
    {
        int age = DateTime.Now.Year - model.Birthdate.Year;
        if (DateTime.Now.DayOfYear < model.Birthdate.DayOfYear)
            age = age - 1;
        return Task.FromResult(age);
    }

    #endregion
}

SQL Customer Repository (Dapper)

Dapper is the absolute most powerful Object Relational Mapper (ORM) I’ve come across. The reason I feel so strongly about this statement is that Dapper can do things that most of the other ORMs can’t. Dapper is completely capable of splitting and managing multiple datasets being returned from a stored procedure. It’s easy and effective to write custom mappers for Dapper.

public class SqlCustomersRepository : BaseSqlRepository, ICustomersRepository
{
    public async Task<Customer> CreateAsync(Customer model)
    {
        try
        {
            using (IDbConnection conn = GetConnection())
            {
                conn.Open();

                var parameters = new DynamicParameters();
                parameters.Add("@FirstName", model.FirstName);
                parameters.Add("@LastName", model.LastName);
                parameters.Add("@Email", model.Email);
                if (model.BillingAddress.AddressId.HasValue)
                    parameters.Add("@BillingAddressId", model.BillingAddress.AddressId);
                if (model.ShippingAddress.AddressId.HasValue)
                    parameters.Add("@ShippingAddressId", model.ShippingAddress.AddressId);

                var data = (await conn.QueryAsync<Customer>(StoredProcedures.CreateCustomer, parameters, commandType: CommandType.StoredProcedure)).SingleOrDefault();
                return data;
            }
        }
        catch (Exception ex)
        {
            throw ex;
        }
    }

Base Repository

Creating an abstract base repository is a good choice when derived repositories need to share common logic like SQL connections, stored procedure names, or any other common logic.

static class StoredProcedures

I also like to create a sub-class with static properties to reference stored procedures. This is very useful because you can see how many references there are to those stored procedures. This helps identify duplicate code and makes refactoring easier because it will be easier to identify which functions are using these stored procedures.

public abstract class BaseSqlRepository : BaseRepository
{
    private IConfigurationSection _databaseSettings;
    private IConfigurationSection _settings;

    protected bool? _debug { get; set; }
    private static string dbSchema = "dbo";

    public BaseSqlRepository()
    {
        _databaseSettings = GetConfigurationSection("SqlDatabase");
    }

    public IDbConnection GetConnection()
    {
        var conn = new SqlConnection(_databaseSettings["ConnectionString"]);
        return conn;
    }

    public static class StoredProcedures
    {
        public static string CreateCustomer = $"{dbSchema}.uspCustomerCreate";
        public static string GetCustomer = $"{dbSchema}.uspCustomerGet";
        public static string DeleteCustomer = $"{dbSchema}.uspCustomerDelete";
        public static string SaveCustomer = $"{dbSchema}.uspCustomerSave";
        public static string GetCustomers = $"{dbSchema}.uspCustomersGet";
    }
}

Switching Data Providers with Ease

Switching data providers is easier because the repository pattern encapsulates the data logic. If we were to decide to switch to another data provider like MongoDB I could easily create a MongoCustomersRepository that interfaces ICustomerRepository and set up my logic there. Switching data providers at this point are as easy as configuring the dependency resolver to map ICustomersRepository to MongoCustomersRepository. This is a great design pattern if there are plans to change to another data source in the future. A popular open-source alternative to MSSQL is Postgres. This may reduce costs since licensing isn’t required.

public static void ConfigureServices(IServiceCollection services)
{
    services.AddTransient<IDataService, DataService>();
    //services.AddTransient<ICustomersRepository, SqlCustomersRepository>();
    services.AddTransient<ICustomersRepository, MongoCustomersRepository>();
}

Unit Testing

Generic Repository Pattern

I highly recommend reading this article. It covers more about the repository pattern and how to write a generic repository.

https://codewithshadman.com/repository-pattern-csharp/

Links

https://codewithshadman.com/repository-pattern-csharp/
I really liked this article. He covers a lot of other points like Entity Framework, and UnitOfWork.

4 Common Mistakes with the Repository Pattern
This was also mentioned on Shadman’s blog. I highly recommend looking over this as well.

LEAVE A REPLY

Please enter your comment!
Please enter your name here