Database Set up for Asp.NET API with SQL Server and Entity Framework

8 min readJun 25, 2021

Intro

In this post we will go through a complete database set up for Asp.Net 5.0 Web APIs with Entity Framework Code-First and Sql Server. We will also cover how to tackle performance issues you might face when fetching data using Entity Framework.

Context

There are many ways to use a SQL Server database in Asp.Net 5.0 using ORMs (Object Relational Mapping) libraries, or writing plain queries using ADO.NET. If you are after the maximum performance when dealing with a database, you should go for writing direct query against your database, as every library you add into the mix to facilitate this process will add some overhead to your query performance. However if you are after a balance between query efficiency and convenience/time/cost of development, ORMs are for you.

By the way, this topic belongs to the series to set up an Asp.NET API for production use.

Choices

There are many ORMs out there. Over the years I have used Entity Framework, Dapper, Linq to SQL, NHibernate, and LLBLGen Pro. In this post I will cover Entity Framework, and in future I will also write about Dapper, which is a fast micro-ORM.

Approach

The outcomes I’d like to achieve here, is to introduce a pattern for dealing with database, in a way that is:

Replaceable: you’d be able to replace your ORM at anytime in future with minimal/no change to the rest of your application
Scalable: to allow your code-base scale/grow in size as you add more and more tables/entities while maintaining a predictable code organisation
Performant: to have patterns in place to create an acceptable level of query performance.

By the end of this post, you will have an Asp.Net 5.0 Web API with Entity Framework using Unit of Work repository pattern.

End Game

Like always, lets begin with the end in mind. We say, anywhere you like to access you database, typically in your services, you will access to all repositories (entities/tables), you will be able to run queries on them, perform CRUD (create/read/update/delete), and run custom queries or call stored procedures. All accessed through one object, which we call it Unit of Work (UoW). Here is how our UoW looks like in code — I have called it SampleApiDb:

Looking at the above snippet, you have all the CRUD operations, as well as running queries, calling stored procedures, and all the benefits I mentioned; There is no mention of EntityFramework, so you could easily replace it with the ORM of your choice, you can add as many repositories (tables, like Customer) as you like, and you can inject this Unit of Work as an interface to your database layer anywhere so it is a scalable pattern as well. I will cover the performance side later as we dive into the implementation. At the end, when you are done with all the DB operations, you can call SaveAsync, which will save all the Insert, Update, and Delete commands across all the repositories.

Repository

Let’s say you have 5 tables (such as Customer) in your database, for each table you can have one Repository, which will be part of your Unit of Work. By accessing CustomerRepository in your Unit of Work, you are then able to perform all the operations we talked about. So now lets have a look at what’s inside a repository:

This means you have a database layer around Customer table with a primary key of GUID type, which does all the CRUD operations as well as running queries and calling stored procedures, which is pretty much what you need when talking to your database. Please also note that we can support all the below query modes accessible through thr RepositoryBase.

NonQuery
Procedure
Query
QueryScalar
TableValuedFunction

Now here is your generic RepositoryBase, with the implementation of the above methods, which enable all your individual repositories to perform those operations:

And last but not least, the generic IRepository, which enables you to change your primary key type if you wished:

Unit of Work

Going back to SampleApiDb, which plays the role of Unit of Work for us, originally we only had one repository, called CustomerRepository. However you can look at it as a container of all repositories, which match all the tables in your database. Eventually it will be something like below, with all the repositories injected as members:

So now, you can inject SampleApiDb, use all the repositories and manipulate your data, and once you are done, call the SaveAsync function on your SampleApiDb, and you are all good.

Configuration

1. Start Up

Now that we have the pattern to connect to and use our database, we need to configure it using Code-First. Assuming that you have a similar project structure as below, you will have your EntityFramework DbContext defined in DataAccess layer, and used in your start up project, which in this case is, SubscriptionApi.

Figure 1, Asp.NET 5.0 Project Structure

Looking inside your startup file, you can define and inject an instance of Entity Framework like below:

This snippet will do a few things,

Configure ConnectionString: which in this case I am reading it from the secret store. Refer to my post here on how to Manage Secrets in Asp.Net 5.0.
Lazy Loading: Lazy loading is configured so you would fetch what you need from your database in terms of related entities as opposed to fetching unwanted records.
Sensitive Data Logging: Which you would enable it only in Dev environments.
Enable Service Provider Caching: This will enable the caching of internal service providers, and it should only be Disabled in Dev/Test environments for testing purposes, which otherwise will have considerable negative impact on performance.
AppLoggerFactory: can be configured to log warnings coming out of Entity Framework, which will cover shortly.

2. DbContext

Next we configure the actual DbContext located in the DataAccess layer. It simply contains the entities, and custom configuration you might need to apply to those entities such as Indexes or Default Values like below:

Also you can find the AppLoggerFactory implementation at the end of the above class.

3. Entities

Next we go to entities, and define them one by one. As an example let’s look at OrderLine entity, which has relationship with Plan and Price entities, as well as a bunch of other scalar properties.

Please note, that those relationships should be defined as virtual, and if it is a one-to-many relationship, it should be of virtual ICollection type.

public virtual ICollection<Feature> Features { get; set; }

Note
Configuration, plumbing and troubleshooting your software foundation take a considerable amount of time in your product development. Consider using Pellerex which is a complete foundation for your enterprise software products, providing source-included Identity and Payment functions across UI (React), API (.NET), Pipeline (Azure DevOps) and Infrastructure (Kubernetes).

4. Migrations

The way to track changes in EF Code-First is through migrations. Every time you make a change to your entities, you run a migration, and the script to migrate the existing database and bring it up to date with the change you just made, is generated. Subsequently, the next time you update your database (which we will cover in a bit), EF check the current database version, and only run those migrations which haven’t been applied yet.

Below is the command you run to generate the change scripts.

dotnet-ef migrations --startup-project ..\Api\Api.csproj add add-new-migration --verbose

This requires you to have installed the DotNet EF tools beforehand which you can get it from here.

Note When Running the Migration Commands

You can waste a log of time here! Make sure when you run the above command, you are cmd is at the root of DataAccess layer, with DBContext file right beside it.
Also make sure you have the — verbose flag enabled, as it highlights the underlying issues. If you don’t include that flag, you can go round round round with a vague generic error without knowing what the actual problem is.
Make sure you include the — startup-project flag, as it points to the API which is consuming the database context. Without that, it will fail as well.

Once you are done generating all the scripts for the migrations, it is time to update the database, which you can do through the below command:

dotnet-ef database --startup-project ..\Api\Api.csproj update

Note When Running the Update Commands

In case you wanted to roll back to a specific migration, include the name of that specific migration at the end of the command like below:

dotnet-ef database --startup-project ..\Api\Api.csproj update specificMigrationName

Automated Database Update

While the above database update commands are manual, you can always automate these updates as part of your pipeline, or even simpler, run them Whenever your Api runs. All you need to do, is to include and run the below method as part of your API middleware.

All this is doing, is to grab your DbContext, and update it at run time, so you don’t need to worry about DbUpdate in Production or any other environments.

5. Dependency Injection

As you can see in our Unit of Work class, all the repositories are being injected. Hence for each repository in the Unit of Work, you will need to inject the repository, along with the Unit of Work class itself.

services.AddTransient<IRespository<Guid, DataAccess.Models.Customer>, CustomerRepository>();
    services.AddTransient<SampleApiDb>();

Pellerex Foundation: For Your Next Enterprise Software

How are you building your current software today? Build everything from scratch or use a foundation to save on development time, budget and resources? For an enterprise software MVP, which might take 8–12 months with a small team, you might indeed spend 6 months on your foundation. Things like Identity, Payment, Infrastructure, DevOps, etc. they all take time, while contributing not much to your actual product. These features are needed, but they are not your differentiators.

Pellerex does just that. It provides a foundation that save you a lot development time and effort at a fraction of the cost. It gives you source-included Identity, Payment, Infrastructure, and DevOps to build Web, Api and Mobile apps all-integrated and ready-to-go on day 1.

Check out Pellerex and talk to our team today to start building your next enterprise software fast.