How to create a Web API with .NET5 and how to refactor the code to use the Repository Pattern to make it more scalable and testable.

9 min readApr 5, 2021

I have been searching for an article on how to create a Web API in .NET5 using the industry standards and the Web API design best practices. I have read about Clean Architecture recently and in this article I’m going to create a Web API in .NET5 using API design best practices by implementing the Repository pattern and by adopting the Clean Architecture.

Prerequisites

SQL Server Management Studio
Visual Studio 2019 or earlier.
.NET 5 or earlier

Create the database

Let’s create a new database from SQL Server Management Console. You can choose to skip this step and add the tables to an existing database.

CREATE DATABASE BookStore;
GO

Create the tables

First, let’s create the Authors table

USE BookStore
GOCREATE TABLE [dbo].[Authors](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [nvarchar](max) NOT NULL,
CONSTRAINT [PK_dbo.Authors] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOALTER TABLE [dbo].[Books]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Books_dbo.Authors_AuthorId] FOREIGN KEY([AuthorId])
REFERENCES [dbo].[Authors] ([Id])
ON DELETE CASCADE
GOALTER TABLE [dbo].[Books] CHECK CONSTRAINT [FK_dbo.Books_dbo.Authors_AuthorId]
GO

Now let’s create the Books table

USE BookStore
GOCREATE TABLE [dbo].[Books](
 [Id] [int] IDENTITY(1,1) NOT NULL,
 [Title] [nvarchar](max) NOT NULL,
 [Year] [int] NOT NULL,
 [Price] [decimal](18, 2) NOT NULL,
 [Genre] [nvarchar](max) NULL,
 [AuthorId] [int] NOT NULL,
 CONSTRAINT [PK_dbo.Books] PRIMARY KEY CLUSTERED 
(
 [Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GOALTER TABLE [dbo].[Books]  WITH CHECK ADD  CONSTRAINT [FK_dbo.Books_dbo.Authors_AuthorId] FOREIGN KEY([AuthorId])
REFERENCES [dbo].[Authors] ([Id])
ON DELETE CASCADE
GOALTER TABLE [dbo].[Books] CHECK CONSTRAINT [FK_dbo.Books_dbo.Authors_AuthorId]
GO

Populating the Authors table

USE BookStore
GO
SET IDENTITY_INSERT [dbo].[Authors] ON 
GO
INSERT [dbo].[Authors] ([Id], [Name]) VALUES (1, N'Jane Austen')
GO
INSERT [dbo].[Authors] ([Id], [Name]) VALUES (2, N'Charles Dickens')
GO
INSERT [dbo].[Authors] ([Id], [Name]) VALUES (3, N'Miguel de Cervantes')
GO
INSERT [dbo].[Authors] ([Id], [Name]) VALUES (4, N'Lisa Halliday')
GO
SET IDENTITY_INSERT [dbo].[Authors] OFF
GO

Populating the Books table

USE BookStore
GO
SET IDENTITY_INSERT [dbo].[Books] ON 
GO
INSERT [dbo].[Books] ([Id], [Title], [Year], [Price], [Genre], [AuthorId]) VALUES (1, N'Pride and Prejudice', 1813, CAST(9.99 AS Decimal(18, 2)), N'Comedy of manners', 1)
GO
INSERT [dbo].[Books] ([Id], [Title], [Year], [Price], [Genre], [AuthorId]) VALUES (2, N'Northanger Abbey', 1817, CAST(12.95 AS Decimal(18, 2)), N'Gothic parody', 1)
GO
INSERT [dbo].[Books] ([Id], [Title], [Year], [Price], [Genre], [AuthorId]) VALUES (3, N'David Copperfield', 1850, CAST(15.00 AS Decimal(18, 2)), N'Bildungsroman', 2)
GO
INSERT [dbo].[Books] ([Id], [Title], [Year], [Price], [Genre], [AuthorId]) VALUES (4, N'Don Quixote', 1617, CAST(8.95 AS Decimal(18, 2)), N'Picaresque', 3)
GO
SET IDENTITY_INSERT [dbo].[Books] OFF
GO

Let’s create a new ASP.NET Core Web API project.

Start Visual Studio. Create a new project. Select ASP.NET Core Web API template and click Next. Enter BooksWebAPI for project name. For location, choose the proper folder. Click Next. Select .NET 5.0 for the Target Framework. Use the default values for Authentication type and Enable Open API support. Click on Create.

Adding the Model to the application

I am going to use EF Core Database First approach to create our models. I like this method especially if I already have a database with all the tables. Navigate to Tools >> NuGet Package Manager >> Package Manager Console. We have to install the package for the database provider that we are targeting which is SQL Server in this case. So run the following command:

Install-Package Microsoft.EntityFrameworkCore.SqlServer

Since we are using Entity Framework Tools to create a model from the existing database, we will install the tools package as well. So run the following command:

Install-Package Microsoft.EntityFrameworkCore.Tools

After you have installed both packages, we will scaffold our model from the database tables using the following command:

Scaffold-DbContext "Data Source=(localdb)\MSSQLLocalDb;Initial Catalog=BookStore;Integrated Security=True" Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models -Tables Authors, Books

Your connection string may use a different server name and database name. Please change them accordingly.

The command will take about a minute to execute. Later, you would see this warning: To protect potentially sensitive information in your connection string, you should move it out of source code. Let’s ignore it for now as the command is still executing.

After this command gets executed successfully, you will find the Models folder has been created and it contains three class files BookStoreContext.cs, Author.cs and Book.cs. We have successfully created our Models using EF core database first approach.

Add the connection string into appsettings.json, before the word “Logging”

"ConnectionStrings": {
    "BookStoreConnection": "Data Source=(localdb)\\MSSQLLocalDb;Initial Catalog=BookStore;Integrated Security=True"
  },

Configure the context to connect to a SQL Server database

Add the following lines in ConfigureServices method of Startup class, before services.AddControllers();

services.AddDbContext<BookStoreContext>(options =>
    options.UseSqlServer(
        Configuration.GetConnectionString("BookStoreConnection")
));

You should see some red squiggly lines under BookStoreContext and UseSqlServer. Move the cursor over BookStoreContext and click on Show Potential fixes. Click on using BooksWebAPI.Models. Move the cursor over UseSqlServer and click on Show Potential fixes. Click on using Microsoft.EntityFrameworkCore.

Add the controllers

In Solution Explorer, Under Controllers, right-click >> Add >> New Scaffolded item… Select API Controller with actions, using Entity Framework and click Add

The following dialog will appear:

After you click Add, the AuthorsController will be created and placed in Controllers folder.

Let’s follow the same steps for creating the BooksController.

Visual Studio will build the project and add the BooksStoreController into the Controllers folder. At this point, you can start the application and you should see this Swagger UI screen:

Note: You can customize the entity type classes and DbContext class to fit your needs. You may want to add additional constructors, methods or properties using a partial class in a separate file so that it does not get overriden if you decide to reverse engineer the model from the database again.

Refactor the code in order to use the Repository Pattern to make the Web API scalable and testable.

Add a project (Class library) and name it ApplicationCore. Add a new folder named Entities and another one named Interfaces.

In Entities folder, create a class named BaseEntity.cs

public class BaseEntity
    {
        public int Id { get; set; }
    }

In Interfaces folder, create an Interface named IRepository.cs

public interface IRepository<T> where T : BaseEntity
    {
        T GetById(int id);
        T GetSingleBySpec(ISpecification<T> spec);
        IEnumerable<T> ListAll();
        IEnumerable<T> List(ISpecification<T> spec);
        T Add(T entity);
        void Update(T entity);
        void Delete(T entity);
    }

Add another class library project named Infrastructure to the solution. Add a Data folder. Delete Class1.cs. Add EntityFrameworkCore package to the project. Add BookStoreContext class.

public class BookStoreContext : DbContext
    {
        public BookStoreContext(DbContextOptions<BookStoreContext> options) : base(options)
        {
        }
        public virtual DbSet<Author> Authors { get; set; }
        public virtual DbSet<Book> Books { get; set; }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            modelBuilder.Entity<Author>(entity =>
            {
                entity.Property(e => e.Name).IsRequired();
            });

            modelBuilder.Entity<Book>(entity =>
            {
                entity.Property(e => e.Price);

                entity.Property(e => e.Title).IsRequired();

                entity.HasOne(d => d.Author)
                    .WithMany(p => p.Books)
                    .HasForeignKey(d => d.AuthorId);

            });

        }
    }

Add a class named AuthorRepository in Infrastructure project under Data folder

using ApplicationCore.Entities;
using ApplicationCore.Interfaces;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Infrastructure.Data
{
    public class AuthorRepository : EfRepository<Author>, IAuthorRepository
    {
        public AuthorRepository(BookStoreContext dbContext) : base(dbContext)
        {
        }

        public async Task<Author> GetAuthor(int id)
        {
            var author = await _dbContext.Authors.FindAsync(id);
            return author;
        }

        public async Task<IEnumerable<Author>> GetAuthors()
        {
            var authors =  await _dbContext.Authors.ToListAsync();
            return authors;
        }

        public async Task SaveAuthor(Author author)
        {
            _dbContext.Entry(author).State = EntityState.Modified;
            await _dbContext.SaveChangesAsync();
        }

        public async Task AddAuthor(Author author)
        {
            _dbContext.Authors.Add(author);
            await _dbContext.SaveChangesAsync();
        }

        public async Task DeleteAuthor(Author author)
        {
            _dbContext.Authors.Remove(author);
            await _dbContext.SaveChangesAsync();
        }
        public bool AuthorExists(int id)
        {
            return _dbContext.Authors.Any(e => e.Id == id);
        }
    }
}

Refactor the AuthorsController by injecting the authorRepository instead of the bookStoreContext as shown below:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Threading.Tasks;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;
using ApplicationCore.Entities;
using ApplicationCore.Interfaces;

namespace BooksWebAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class AuthorsController : ControllerBase
    {
        private readonly IAuthorRepository _authorRepository;

        public AuthorsController(IAuthorRepository authorRepository)
        {
            _authorRepository = authorRepository;
        }

        // GET: api/Authors
        [HttpGet]
        public async Task<ActionResult<IEnumerable<Author>>> GetAuthors()
        {
            var authors = await _authorRepository.GetAuthors();
            return Ok(authors); 
        }

        // GET: api/Authors/5
        [HttpGet("{id}")]
        public async Task<ActionResult<Author>> GetAuthor(int id)
        {
            var author = await _authorRepository.GetAuthor(id);

            if (author == null)
            {
                return NotFound();
            }

            return author;
        }

        // PUT: api/Authors/5
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPut("{id}")]
        public async Task<IActionResult> PutAuthor(int id, Author author)
        {
            if (id != author.Id)
            {
                return BadRequest();
            }

            try
            {
                await _authorRepository.SaveAuthor(author);
            }
            catch (DbUpdateConcurrencyException)
            {
                if (!AuthorExists(id))
                {
                    return NotFound();
                }
                else
                {
                    throw;
                }
            }

            return NoContent();
        }

        // POST: api/Authors
        // To protect from overposting attacks, see https://go.microsoft.com/fwlink/?linkid=2123754
        [HttpPost]
        public async Task<ActionResult<Author>> PostAuthor(Author author)
        {
            await _authorRepository.SaveAuthor(author);

            return CreatedAtAction("GetAuthor", new { id = author.Id }, author);
        }

        // DELETE: api/Authors/5
        [HttpDelete("{id}")]
        public async Task<IActionResult> DeleteAuthor(int id)
        {
            var author = await _authorRepository.GetAuthor(id);
            if (author == null)
            {
                return NotFound();
            }

            await _authorRepository.DeleteAuthor(author);

            return NoContent();
        }

        private bool AuthorExists(int id)
        {
            return _authorRepository.AuthorExists(id);
        }
    }
}

Similarly, add a class named BookRepository in Infrastructure project under Data folder

using ApplicationCore.Entities;
using ApplicationCore.Interfaces;
using Microsoft.EntityFrameworkCore;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace Infrastructure.Data
{
    public class BookRepository : EfRepository<Book>, IBookRepository
    {
        public BookRepository(BookStoreContext dbContext) : base(dbContext)
        {
        }

        public async Task<IEnumerable<Book>> GetBooks()
        {
            return await _dbContext.Books.ToListAsync();
        }

        public async Task<Book> GetBook(int id)
        {
            var book = await _dbContext.Books.FindAsync(id);
            return book;
        }

        public async Task SaveBook(Book book)
        {
            _dbContext.Entry(book).State = EntityState.Modified;
            await _dbContext.SaveChangesAsync();
        }

        public async Task AddBook(Book book)
        {
            _dbContext.Books.Add(book);
            await _dbContext.SaveChangesAsync();
        }

        public async Task DeleteBook(Book book)
        {
            _dbContext.Books.Remove(book);
            await _dbContext.SaveChangesAsync();
        }

        private bool BookExists(int id)
        {
            return _dbContext.Books.Any(e => e.Id == id);
        }

    }
}

After BookRepository class is created, we can create the IBookRepository interface with the help of Visual Studio. Place the cursor in the class name BookRepository and with the mouse, select Edit > Refactor > Extract Interface. Actually there are different methods to bring up the Extract Interface dialog box. See Microsoft documentation

Now, we need to register the IAuthorRepository and IBookRepository services with the corresponding concrete types in the ConfigureServices method of Startup class.

services.AddScoped<IAuthorRepository, AuthorRepository>();
services.AddScoped<IBookRepository, BookRepository>();

Notice that when we execute GetAuthors from Swagger UI, it shows the authors without the book information as shown below:

Let’s update the GetAuthors method in AuthorRepository so it shows the author and the book information.

Before modifying that method, let’s create a folder named DTO and a data transfer object named AuthorDTO.

using ApplicationCore.Entities;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ApplicationCore.DTO
{
    public class AuthorDTO
    {
        public int Id { get; set; }
        public string Name { get; set; }
        public ICollection<BookDTO> Books { get; set; } = new List<BookDTO>();
    }
}

Let’s add another DTO named BookDTO

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace ApplicationCore.DTO
{
    public class BookDTO
    {
        public int Id { get; set; }
        public string Title { get; set; }
        public int Year { get; set; }
        public decimal Price { get; set; }
        public string Genre { get; set; }
        public int AuthorId { get; set; }
    }
}

Here is the new GetAuthors method

public async Task<IEnumerable<AuthorDTO>> GetAuthors()
        {
             var authors = await _dbContext.Authors.Include(a => a.Books)
                .Select(x => new AuthorDTO
                {
                    Id = x.Id,
                    Name = x.Name,
                    Books = x.Books
                        .Select(b =>
                        new BookDTO
                        {
                            Id = b.Id,
                            Title = b.Title,
                            Genre = b.Genre,
                            Price = b.Price,
                            Year = b.Year,
                            AuthorId = b.AuthorId
                        })
                        .ToList()
                }).ToListAsync();
            return authors;
        }

Now, let’s execute the GetAuthors method and see if we get the list of authors with the list of books.

Conclusion

In this article, I showed you how to create a Web API using .NET5 and I showed you how to refactor the code to use the Repository Pattern and I updated the GetAuthors method to make it return the proper results. By using the Repository pattern, I will be able to unit test the Web API easily. That’s the topic for my next article.

The code for this article can be found on my Github

Thanks for reading this article.