One Point to Rule Them All

Every application, or most of them,  in the real world depends on some kind of data source. In most cases, these sources are relational or some kind of NoSQL databases and it is not uncommon that one application relies on multiple data sources at the same time. It is also important to mention that these sources can use various database engines, data access, and object-relational mapper technology. If not implemented properly, support for this scenarios could give us a maintenance headache and simple case of switching from one object-relational mapper to another or introducing another data source could become the worst nightmare.  Lucky for us there is a pattern that solves our issues and creates a single point for all our data sources.

Every day we Developers (I will refer to Microsoft stack in the rest of the content) are dealing with various data access layer implementations. Sometimes we are dealing with ADO.NET or .NET Framework Data Provider for Oracle as a bridge between an application and a data source and sometimes we use object-relational mapper technology as Entity Framework or Dapper. In the most modern applications, there is also some kind of NoSQL data sources like MongoDB or Azure CosmosDB which introduce a separate mechanism for interaction. And if you are lucky and have to deal with CQRS architectural pattern, the queries will be performed by simple SQL statements (for example by using ADO.NET) on one database and the commands (modification of the data) will be done by using EF Core on a separate database. When we think about every data access layer separately they all have more things in common. The most obvious thing is that they share the good old CRUD (create, read, update and delete) and if we look deeper we will find that most databases share common data access functionality.

The Repository pattern

This pattern is the key to encapsulate the logic required to access data sources. The idea behind the pattern is to have a  point where common data access functionality is centralized, which contributes to better maintainability and decoupling of the infrastructure or technology used to access databases from the domain model layer. In the book Patterns of Enterprise Application Architecture, Martin Fowler describes a repository as follows:

“A repository performs the tasks of an intermediary between the domain model layers and data mapping, acting in a similar way to a set of domain objects in memory. Client objects declaratively build queries and send them to the repositories for answers. Conceptually, a repository encapsulates a set of objects stored in the database and operations that can be performed on them, providing a way that is closer to the persistence layer. Repositories, also, support the purpose of separating, clearly and in one direction, the dependency between the work domain and the data allocation or mapping.”

Great, now we know that we need to create classes and components that will implement the logic based on the domain model as repositories but this doesn’t solve the requirement for a single point for all our data sources.

The Unit of Work Pattern

Unit of Work in combination with the repository pattern provides “One Point to Rule Them All” when it comes to working with data sources. The responsibility of the pattern is quite simple, it’s maintaining the state of the repository objects (entities) and track if they are modified, inserted or deleted and completing the transaction (send the changes to the data sources) once the work is done.


So now that we have the necessary tools lets roll up our sleeves. We will create a simple API project in ASP.NET Core with Azure SQL database as a data source. Since I am working on macOS I will be using Visual Studio for Mac for coding and Valentina Studio 8 for accessing and running SQL scripts on Azure SQL database. Also, you can use interactive command-line tool for querying SQL Server if you prefer working with the command line. The entire implementation is available on GitHub.

Creating and preparing Azure SQL Database

Open your Azure subscription and go to SQL databases and create a database. You can use existing Server or create a new one (write down admin login because you will need it) and you can use basic tier for this purpose.

Once the database is created go to Overview screen and set server firewall because you will not be able to connect to the database from your local machine unless you add the rule to allow your IP address to connect to the database. Just click on Add client IP and rule will be added.

Now let’s take from the Overview screen connection string. We will use this in our application and in Valentina Studio,1433;Initial Catalog=conferences;Persist Security Info=False;User ID={your_username};Password={your_password};MultipleActiveResultSets=False;Encrypt=True;TrustServerCertificate=False;Connection Timeout=30;

Open Valentina Studio and click on Connect to.. button. Use the value behind Server=tcp: as server host and 1433 as a port number. Also, you will use your server username and password and connect to the master database.

Now that we are connected to the database you will need to create an SQL login user that will be used in the app. You do not want to use the admin user that you created in the Azure portal in the application. Run the following scripts on the master database.

WITH PASSWORD = 'password'

Now use the database that you have created and run the following scripts:

EXEC sp_addrolemember 'appuserrole', 'appuser';

And now you’re ready to go. You can now connect to the database with your appuser and password credentials and add this credentials into {your_username} and {your_password} sections of the connection string.

For the testing purpose let’s create a simple table and add some records in it.


CREATE TABLE [dbo].[Products] ( 
[ProductID] INT IDENTITY ( 1, 1 )  NOT NULL, 
[ProductName] VARCHAR( 25 ) NOT NULL, 
[Price] MONEY NULL, 
[ProductDescription] TEXT NULL )


INSERT dbo.Products (ProductName, Price, ProductDescription)VALUES ('Screwdriver', 3.17, 'Flat head'),('Tire Bar', NULL, 'Tool for changing tires.')   

Implementing the Repository and Unit of Work Pattern

Now let’s switch to our API. I will start with the “black” solution named Conferences and add several projects to it:

Conferences.API: This is the ASP.NET Core project (I use the 2.2 version)

Conferences.DAL: This is the .net standard project that will contain Unit Of Work implementation

Conferences.DataAccess.Common: This is the .net standard project that contains the interfaces for the Unit of Work, general repository and repository for the Products domain ([dbo].[Products] table)

Conferences.DataAccess.Entities: This is the .net standard project that contains the entities

Conferences.DataAccess.SqlServer: This is the .net standard project that contains EF Core implementation

Let’s start by implementing interfaces in Conferences.DataAccess.Common

As you see we are implementing common CRUD operations in generic repository interface IRepository which is inherited in the repository from Products domain IProductsRepository. In this way Products repository will have default CRUD operations and all other additional operations can be added in the interface definition. In this way, we have separated data access logic and functionality from the data access technology. Next, let’s create our EF Core implementation. It is pretty straightforward implementation so it’s self-explanatory. EF Core’s DbContext already implements Unit Of Work and Repository pattern in its implementation so if you don’t plan to use additional sources the custom implementation of the pattern as we are doing here is unnecessary.

Now let’s implement a generic repository based on the IRepository interface in Conferences.DataAccess.SqlServer project. This will include common CRUD operations on DbContext that will be reusable for all entities and repositories around EF core implementation.

There is one more repository implementation that needs to be done and that is an implementation of ProductsRepository which is simple since we already have a generic repository for all CRUD operations.

We are done with the implementation of the Repository pattern. As you see it’s pretty simple and straightforward. You can reuse the steps of implementation for any other repository around EF and Azure SQL Server database. Also based on this example you can implement additional database providers for data sources. Just create a new project and implement a repository based on the IRepository interface for the provider.

The next step is to implement the Unit Of Work pattern in Conferences.DAL project.

We are done and we can finally implement data access in our API. We only need to create a dependency injection for IUnitOfWork interface and we can start working on our API operations. Here is the implementation of the Startup class of our API:

As you can see from the controller implementation, API isn’t aware of the data access layer implementation and doesn’t have any clue about EF implementation.  We could easily introduce additional data source or change existing one without the fear of breaking the existing API. Also with this implementation unit testing is easy because we could mock the repository, and also the unit of work implementation.