Structuring ASP.NET Core project: Designing and implementing providers for SQLServer and PostgreSQL

In this article we going to look into designing the data providers, the third layer of the tri-layer architecture.

Providers are responsible for "proving" tools to a service. Things like handling data operations (SQL or LINQ queries), working with files, network requests or sending emails are the responsibilities of the providers. Every provider should have an abstraction (a set of interfaces) that is called by service and should never be accessed directly.

Email or Identity providers are self-descriptive. Their structure is straight forward: interfaces and their implementations. Please see README for more information.

Data providers are more complicated and required further explanation. The main idea is to isolate high level business logic from a specific database implementation or even a data query implementation. It provides a flexibility in decision making process about choosing the database engine, replacing it if needed or maybe not using a database at all.

The data providers located in "Database" folder have been divided into two categories: "Core" and "Provider". "Core" contains all the interfaces, some common logic and is independent of specific database engine. "Provider" should implement the interfaces and add the database specific code including migrations.

Use Case

Let's review the simple use case before diving into the database project's structure. Imagine a web API application that has method to return list of employees as pages with 25 items per page. Here is how the query will be constructed by Service using data provider interfaces:

[AppService]
public class EmplyeeService : BaseService
{
    public EmplyeeService (IQueryRepository query) 
        : base(query)
    {

    }

    public async List<EmployeeItem> List(int pageIndex)
    {
		var pageSize = 25;
        var result = await Repository.GetEmployeeQuery().Skip(pageIndex * pageSize).Take(pageSize).SelectAsync();
        return result.ToView();
    }
}

Let's review what is happening in these two lines in method List():

1. Repository follows facade pattern and contains number of methods to obtain references for the specific queries. In this case: IEmployeeQuery

2. Query provides methods needed to construct a query of the specific object type. In this case we use IEmployeeQuery which declares method SelectAsync and inherits IQPageable interface. In this case: Skip->Take->SelectAsync should return IQueryable<Employee> where Employee is a database model type.

3. Decorators transform database model types and queries into Shared Model types. In this case extension method ToView() executes the query returned from SelectAsync and returns List. Decorator's code looks like this:

public static List<EmployeeItem> ToView(this IQueryable<Employee> query)
{
	var finalRes = (from result in query
					select new EmployeeItem
					{
						Id = result.PublicId,
						Title = result.Name,
						Address = result.Address,
						...
					});

	return finalRes.ToList();
}

Decorators don't know anything about a specific database. They are optional and may not be a solution in some complex scenarios.

Why cannot something like EF or LINQ be used directly in the service? - it would dramatically "relax" boundaries between service and provider layers. Remember: a service should not know anything about database. It may be a flat file solution instead of relational database and service doesn't care. In addition, it would make unit testing of services a nightmare.

Project structure

Core

The Core is a library that includes provider interfaces and some common code that can be reused by a specific implementation. The source code is structured in the following way:

• Data: contains logic and interface needed to realize queries. IQueryRepository is a facade interface for all the queries available by provider. Every query interface provides actions that can be performed against a specific object type. Following the example above, "Employee" table can be associated with IEmployeeQuery interface that declares Select(), ByName(), etc. Additionally, it can inherit IModelQuery interface for providing CRUD methods and IQPageable for implementing paging. Data folder also contains "Decorators" folder. Decorators are extension methods to help transforming data from database model into shared model. See use case above for more information about decorators.

• Models: is the namespace for database model objects

• ApplicationDbContext.cs: database context. It is an abstract class and should be implemented by a specific context in the provider rather than used directly.

• ConfigurationExtensions.cs: generic methods that encapsulates configuring code (usually called in Startup.cs)

Provider

Provider implements a database engine specific logic. There are two providers in this project as of right now: Postgres and SqlServer. The projects' structure is the similar to the core. Every provider should:

1. Override or implement database context.

2. Implement interfaces in the Queries namespace.

3. Provide migrations (see Migrations section below);

4. Provide configuration extensions.

Configuration

Depending on the project itself, developer may choose to support single or multiple providers. The sample app projects offer two providers: SqlServer and Postgres. However, majority of real projects will go with the single one. Here is a common steps that must be done in WebService project:

1. Add a reference to the provider to WebService project;

2. Add connection string to appsettings.json;

3. In Startup.cs add (in case of Postgres):

//Configuring Postgres
private void Configure (IServiceCollection services)
{
	services.ConfigurePostgresDatabase(Configuration);
	services.AddDatabaseDeveloperPageExceptionFilter();
	services.ConfigurePostgresIdentityContext();
}

Migrations

Migrations is own by the provider project. It make a creating migration litile bit tricky. Command should be run for WebService referring to Provider project. See more details in Readme file.