This article offers an approach to designing, implementing, and unit-testing CRUD APIs (Create, Read, Update, Delete). We will explore best practices for designing web APIs, discuss how to implement pagination, searching, and outline methods for organizing models effectively.

Introduction

In a previous article, we reviewed the general structure of the template project DevCoreApp, which is available on GitHub. In this article, we demonstrate how to add a new entity type as well as CRUD functionality using rules and features available in this project.

Everyone who has recently created an ASP.NET project from a Visual Studio template has likely encountered the Weather Forecast page example. The purpose of this example is to demonstrate how to retrieve data from the server. We will extend this use case by adding a weather forecast table to the database, creating a model, shared model, and implementing the necessary data retrieval logic, service, and controller. Additionally, we will incorporate pagination and searching capabilities.

We are going to work backward from the API itself. We will define the requirements and implement them, starting from the top (API/Controller), then moving to the Service, and finally, to the data provider.

As a starting point, we will use the DevCoreApp project, available on GitHub.

Crafting Web API Requirements

Let's specify the requirements:

• As a client application, I need an API to retrieve a list of weather forecasts. This API should accept parameters for the maximum number of forecasts to return, the page number, and a search parameter.

• As a client application, I need an API to fetch a forecast record by its id, and to insert, update, and delete the forecast record.

Sculpting the API and Shared Model

For the model object shared between the server and client, we'll take the existing WeatherForecast as a base. There are several modifications we have to make to it though:

1. Relocate it to the Shared.Model assembly.

2. In accordance with the shared model naming convention, append the “Item” suffix to the name.

3. To include the id field, inherit it from the EntityItem class.

Here's the refined structure of the shared model item:

public class WeatherForecastItem : EntityItem
{
    public DateTime Date { get; set; }
    public int TemperatureC { get; set; }
    public string Summary { get; set; }
    public int TemperatureF => 32 + (int)(TemperatureC / 0.5556);
}

public class EntityItem
{
    public string Id { get; set; }
}

Now, let's define the CRUD APIs. We are going to modify the WeatherForecastController with the route "api/forecast". In this controller, we will add the following APIs:

1. GetById: Retrieves a WeatherForecastItem by public id or returns HTTP 404 (Not Found) if there is no record with the specified id.

2. Add: Accepts a WeatherForecastItem object and returns it with the public id and other automatic attributes. This API requires authorization and returns HTTP 401 (Unauthorized) if the criteria have not been met.

3. Update: Accepts an id and a WeatherForecastItem object. Returns the updated version of the object. This API also requires authorization and returns HTTP 401 (Unauthorized) or HTTP 404 (Not Found) if the object with the specified id doesn't exist in the database.

4. Remove: Accepts an id and returns the deleted WeatherForecastItem object (allowing the client to reinsert it later, if needed). Follows the same rules as the Update API (HTTP 401 & 404).

5. GetItems: Returns a list of items wrapped in a ModelList object. Accepts parameters for paging, filters, fields, and search. This API is more complex and requires further explanation:

   • ModelList: A wrapper structure for the returned list, including additional attributes such as total item count, current page index, count per page, total number of pages, and the list of items. Refer to ModelList<T> for more information.

   • Current page index and items per page: Determines the pagination (page & top).

   • Search: A text search parameter that filters the results to only include items containing the specified string.

Here is what the controller is going to look like:

[ApiController]
[Route("api/forecast")]
public class WeatherForecastController : BaseController
{
    [HttpGet]
    [ProducesResponseType(StatusCodes.Status200OK)]
    public ActionResult<ModelList<WeatherForecastItem>> GetItems(int? top, int? page, string search = null)
    {
        return HandleWebRequest<ModelList<WeatherForecastItem>>(() =>
        {
            throw new NotImplementedException();
        });
    }

    [HttpGet]
    [Route("{id}")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public ActionResult<WeatherForecastItem> GetById(string id)
    {
        return HandleWebRequest((WebHandler<WeatherForecastItem>)(() =>
        {
            throw new NotImplementedException();
        }));
    }

    [Authorize]
    [HttpPost]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status401Unauthorized)]
    public ActionResult<WeatherForecastItem> Add(WeatherForecastItem item)
    {
        return HandleWebRequest<WeatherForecastItem>(() =>
        {
            throw new NotImplementedException();
        });
    }

    [Authorize]
    [HttpPut]
    [Route("{id}")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status401Unauthorized)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public ActionResult<WeatherForecastItem> Update(string id, [FromBody] WeatherForecastItem item)
    {
        return HandleWebRequest<WeatherForecastItem>(() =>
        {
            throw new NotImplementedException();
        });
    }

    [Authorize]
    [HttpDelete]
    [Route("{id}")]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status401Unauthorized)]
    [ProducesResponseType(StatusCodes.Status404NotFound)]
    public ActionResult<WeatherForecastItem> Remove(string id)
    {                   
        return HandleWebRequest<WeatherForecastItem>(() =>
        {
            throw new NotImplementedException();
        });
    }
}

This controller is derived from the BaseController, which implements the HandleWebRequest method. This method encapsulates exception-handling logic and returns an ActionResult based on the outcome of the call. The text is well-written and presents a straightforward description of the controller.

The Weather Forecast Service

As you can see, the controller above defines APIs but returns a 500 error for every API because the implementation is missing. As explained in the previous article, the controller should remain "slim" and call the service to perform the required tasks. Therefore, we need to define a WeatherForecastService that presents similar methods. There are several points to consider:

• Service class should have the [AppService] attribute.

• Service cannot reference controllers in any case.

• Service cannot expose any internal types besides types declared as part of the shared model or simple types.

Here is what the service looks like:

[AppService]
public class WeatherForecastService : BaseService
{
    public WeatherForecastItem Add(WeatherForecastItem item)
    {
        throw new NotImplementedException();
    }
    // ...
}

In turn, we can add a reference to the controller and implement its methods:

public class WeatherForecastController : BaseController
{
    public WeatherForecastService Service { get; }

    public WeatherForecastController(WeatherForecastService service)
    {
        Service = service;
    }
    // ...
    [Authorize]
    [HttpPost]
    [ProducesResponseType(StatusCodes.Status200OK)]
    [ProducesResponseType(StatusCodes.Status401Unauthorized)]
    public ActionResult<WeatherForecastItem> Add(WeatherForecastItem item)
    {
        return HandleWebRequest<WeatherForecastItem>(() =>
        {
            return Ok(Service.Add(item));
        });
    }
    // ...
}

Now let's implement the service. The first step is to "inject" the data provider. We will use the Repository pattern for this purpose. The following concept of using a repository is prevalent. However, it would be confusing to define all the interfaces working with data at this point. So, we are going to examine the existing implementation of the "Add" method line by line, assuming all data provider interfaces are defined already. Later, we will dive deep into the data provider itself and its interfaces.

I'm going to present the next snippet with the implementation of the method "Add" and explain every line:

public WeatherForecastItem Add(WeatherForecastItem item)
{
    Validate(item);
    var q = Repository.GetWeatherForecastQuery();

    var record = q.CreateNew().ToRecord(item);
    q.Add(record);

    return GetById(record.PublicId);
}

1. We call Validate() to validate the incoming object. This method defines validation rules, and if this model object is not valid for some reason, it will throw a BadRequestException, which will be caught by the controller and converted into HTTP 400 (Bad Request).

2. Every service has a reference to the IQueryRepository interface. This is an abstraction that hides the actual database access logic from the service. This interface will be mocked in unit tests of the service.

3. We get a specific query interface, IWeatherForecastQuery.

4. Once we get a reference to the query interface, we call IModelQuery.CreateNew() which creates a database model object for the forecast record. This object will have all the pre-generated attributes ready, such as Id, PublicID (which will be shared with the client), etc.

5. The extension method ToRecord() merges all the data from the shared model into this newly created record object.

6. The Add() method inserts a record into the database.

7. GetById() returns a shared model object from the database.

As you probably guessed, the Repository and all interfaces it provides are defined in the Data Provider. We will talk about it more later in this article.

Why do we need so much "glue code" and service? There are a couple of reasons to support this approach:

1. Separation of concerns: the controller only cares about handling HTTP requests, which makes it simple. The service implements "business logic" but doesn't care about the actual DB. You can start with an in-memory DB, a flat file, mock it, and delay decisions about which DBMS to use or how the schema should look like. You have to define model and query interfaces, though.

2. It is easy to unit test. As mentioned earlier, mocking the data provider interface will help in unit testing the service. Additionally, you don't have to refer to any data types used in the controller (such as ActionResult). The following example shows how to set up a unit test for the "Add" method:

[TestMethod()]
public void AddTest()
{
    var id = Guid.NewGuid();
    var publicId = IdGenerator.New();
    var date = DateTime.Now;
    var mockSelect = new Mock<IWeatherForecastQuery>();
    var service =
    SetupServiceMock((mockRepository) =>
    {    
        WeatherForecast[] data = { 
            new WeatherForecast { Id = id, PublicId = publicId, CreateDate = date, UpdateDate = date }, };
        mockSelect.Setup(x => x.CreateNew()).Returns(data[0]);
        mockSelect.Setup(x => x.Select()).Returns(data.AsQueryable());
        mockSelect.Setup(x => x.ByPublicId(It.IsAny<string>   ())).Returns(mockSelect.Object);
        mockSelect.Setup(x => x.Clone()).Returns(mockSelect.Object);
        mockRepository.Setup(x =>     x.GetWeatherForecastQuery(It.IsAny<UserProfile>())).Returns(mockSelect.Object);
    });

    var item = new WeatherForecastItem
    {
        Date = date,
        Summary = "Test",
        TemperatureC = 32,
    };

    var result = service.Add(item);

    Assert.IsNotNull(result);
    Assert.AreEqual("Test", result.Summary);
    Assert.AreEqual(date, result.Date);
    Assert.AreEqual(32, result.TemperatureC);

    mockSelect.Verify(x => x.Add(It.IsAny<WeatherForecast>()), Times.Once);
}

Please refer to the test class WeatherForecastServiceTests for more details.

Data Provider

Data providers play a crucial role in supplying the necessary tools and resources to a service. Their primary objective is to separate high-level business logic from the specifics of database implementations or data query methods. This separation grants flexibility when it comes to selecting a database engine, replacing it when necessary, or even opting not to use a database at all.

The "Database" folder contains data providers divided into two main sections: "Database.Core" and "Database.<Provider>(e.g. Database.SqlServer or Database.Postgres)". The "Database.Core" section encompasses all the interfaces, shared logic, and remains agnostic to any particular database engine. On the other hand, the "Database.<Provider>" section is responsible for implementing the interfaces, incorporating database-specific code, and, if using Entity Framework (EF), handling migrations. See more about providers and migrations in Database ReadMe.

Database Model

Database model classes define the structure and schema of your database. They typically share a majority of fields with their corresponding shared models. However, there are some key differences:

1. Database models include primary and, in some cases, secondary keys. Shared models should never contain internal keys from the database. Instead, they should use a PublicID, which can be changed if necessary without affecting database integrity.

2. Database model types must not be shared with controllers; they should be used exclusively by services. Services are responsible for converting data between shared models and database models. To facilitate this, the Core library provides extension methods known as "decorators." You can refer to an example of decorators here.

Here's an example of a model class definition for weather forecast records:

public class WeatherForecast
{
    [Key]
    public Guid Id { get; set; }

    [Required]
    public string PublicId { get; set; }

    public DateTime Date { get; set; }

    public int Temperature { get; set; }

    public string Summary { get; set; }
}

This model class illustrates the structure of weather forecast records in the database, including primary keys, required fields, and other relevant attributes.

Implementing the Repository Pattern

The Repository Pattern is a design pattern that establishes a clear separation between data access and business logic in an application. By centralizing data access and decoupling it from the underlying data storage, this pattern allows for more flexible and maintainable code. In our example, the pattern is represented by the IQueryRepository interface.

IQueryRepository

IQueryRepository is part of the Database.Core library and its purpose is to define the methods required to interact with different data sources:

public interface IQueryRepository
{
    ...
    IWeatherForecastQuery GetWeatherForecastQuery();
}

Provider Implementation

The actual implementation of the IQueryRepository interface is located in the Provider library. In this example, we use the SQLServer provider:

public class SqlServerQueryRepository : IQueryRepository
{
    public SqlServerQueryRepository(ApplicationDbContext dB)
    {
        return new SqlServerForecastQuery(DB);
    }
}

By adhering to the Repository Pattern, you create a clean separation between data access and business logic, making it easier to switch the underlying data storage technology, implement unit testing, and maintain the codebase. The IQueryRepository interface serves as a contract that can be implemented by different database providers, offering flexibility and adaptability to your application.

Implementing the Query Interface

The query interface is a part of the Core project as well. For each specific table or set of tables, every query interface implements operations that can be performed on them. The repository will return a reference to a specific query implementation, in our case, IWeatherForecastQuery:

public interface IWeatherForecastQuery : IModelQuery<WeatherForecast, IWeatherForecastQuery>, 
                                        IQSearchable<IWeatherForecastQuery>,
                                        IQPageable<IWeatherForecastQuery>
{
    IQueryable<WeatherForecast> Select();
}

As you can see, IWeatherForecastQuery inherits from several other interfaces. These interfaces are common building blocks for queries:

public interface IModelQuery<T, D>
{
    T CreateNew();
    void Add(T record);
    void Update(T record);
    void Remove(T record);
    D Clone();
}

public interface IQPageable<T>
{
    T Skip(int value);
    T Take(int value);
}

public interface IQSearchable<T>
{
    T ByPublicId(string id);
    T Search(string search);
}

The IModelQuery<T, D> interface provides methods for creating, adding, updating, and removing records of type T, as well as cloning the implementing instance. The CreateNew() method creates a new instance of the model type T, while the Add(), Update(), and Remove() methods are used to add, update, and remove records in the data store, respectively. The Clone() method creates a deep copy of the implementing instance and returns a new instance of type D.

The IQPageable<T> interface provides two methods, Skip() and Take(), which allows pagination for a given sequence. The Skip() method is used to skip a specified number of items before starting to take elements, while the Take() method specifies the maximum number of items to include in the resulting sequence. Both methods return an updated instance of the implementing class. The IQSearchable interface is self-explanatory. Most of the queries working with entities associated with public ids should implement this interface.

Finally, the query implementation will look like this:

public class SqlServerWeatherForecastQuery : IWeatherForecastQuery
{
    private IQueryable<WeatherForecast> currentQuery;
    protected ApplicationDbContext DB { get; }

    private CoreWeatherForecastQuery(IQueryable<WeatherForecast> q,                          ApplicationDbContext dB)
    {
        currentQuery = q;
        DB = dB;
    }

    public CoreWeatherForecastQuery(ApplicationDbContext dB) 
        : this(from ts in dB.WeatherForecasts
               orderby ts.Date descending
               select ts, dB)
    {

    }

    public void Add(WeatherForecast record)
    {
        DB.WeatherForecasts.Add(record);
        DB.SaveChanges();
    }

    public IWeatherForecastQuery ByPublicId(string id)
    {
        currentQuery = from pr in currentQuery
                       where pr.PublicId == id
                       select pr;

        return this;
    }

    public IWeatherForecastQuery Clone()
    {
        return new CoreWeatherForecastQuery(currentQuery, LogManager, TimeProvider, DB, CurrentProfile);
    }

    public WeatherForecast CreateNew()
    {
        return new WeatherForecast
        {
            Id = Guid.NewGuid(),
            PublicId = IdGenerator.New(),
        };
    }

    public void Remove(WeatherForecast record)
    {
        DB.WeatherForecasts.Remove(record);
        DB.SaveChanges();
    }

    public IQueryable<WeatherForecast> Select()
    {
        return (from pr in currentQuery select pr);
    }

    public void Update(WeatherForecast record)
    {
        DB.WeatherForecasts.Update(record);
        DB.SaveChanges();
    }

    public IWeatherForecastQuery Search(string search)
    {
        throw new NotImplementedException();
    }

    public IWeatherForecastQuery Skip(int value)
    {
        currentQuery = currentQuery.Skip(value);
        return this;
    }

    public IWeatherForecastQuery Take(int value)
    {
        currentQuery = currentQuery.Take(value);
        return this;
    }
}

By implementing these interfaces, you can create a clean and modular structure for your queries, making it easier to maintain and extend your data access layer.

The Final Solution

Here is a final solution for the service:

[AppService]
public class WeatherForecastService : BaseService
{
    public WeatherForecastService(IQueryRepository rep) : base(rep)
    {
    }

    public ModelList<WeatherForecastItem> GetItems(int? top, int? page, string search)
    {
        using (log.TraceScope())
        {
            var coreQuery = Repository.GetWeatherForecastQuery(AuthorizationContext.CurrentProfile);

            coreQuery = ApplyFilters(coreQuery, search);

            var pagedQuery = coreQuery.Clone();
            pagedQuery = ApplyPages(pagedQuery, top, page);

            var list = pagedQuery.Select().ToView();

            return CreateListPage(coreQuery.Select().Count(), list.ToArray(), top, page);
        }
    }

    private static void Validate(WeatherForecastItem item)
    {
        if (item.TemperatureC < -273 || String.IsNullOrWhiteSpace(item.Summary))
        {
            throw new BadRequestException();
        }
    }

    public WeatherForecastItem Add(WeatherForecastItem item)
    {
        Validate(item);

        var q = Repository.GetWeatherForecastQuery(AuthorizationContext.CurrentProfile);

        var record = q.CreateNew().ToRecord(item);

        q.Add(record);

        return GetById(record.PublicId);
    }


    public WeatherForecastItem GetById(string id)
    {
        var record = GetRecordByPublicId(id);
        return record.ToView();
    }

    private WeatherForecast GetRecordByPublicId(string id)
    {
        var q = Repository.GetWeatherForecastQuery(AuthorizationContext.CurrentProfile).ByPublicId(id);
        var record = q.Select().FirstOrDefault();
        if (record == null)
        {
            throw new RecordNotFoundException();
        }

        return record;
    }

    public WeatherForecastItem Remove(string id)
    {
        var q = Repository.GetWeatherForecastQuery(AuthorizationContext.CurrentProfile);
        var record = GetRecordByPublicId(id);

        q.Remove(record);

        return record.ToView();
    }

    public WeatherForecastItem Update(string id, WeatherForecastItem item)
    {
        Validate(item);

        var q = Repository.GetWeatherForecastQuery(AuthorizationContext.CurrentProfile);
        var record = GetRecordByPublicId(id);

        q.Update(record.ToRecord(item));

        return GetById(record.PublicId);
    }
}

and the BaseService for reference:

public abstract class BaseService
{
    public IQueryRepository Repository { get; }

    public BaseService(IQueryRepository query)
    {
        Repository = query;
    }

    protected static ModelList<T> CreateListPage<T>(int totalItemsCount, T[] items, int? top, int? page)
    {
        var pageIndex = 0;
        var totalPageCount = 1;

        if (top.HasValue && top.Value > 0)
        {
            totalPageCount = (int)Math.Ceiling((double)totalItemsCount / (double)top.Value);
        }

        if (page.HasValue && page.Value >= 0)
        {
            pageIndex = page.Value;
            if (pageIndex >= totalPageCount)
            {
                pageIndex = totalPageCount - 1;
            }
        }

        return new ModelList<T>()
        {
            TotalCount = totalItemsCount,
            Count = items.Length,
            PagesCount = totalPageCount,
            Page = pageIndex,
            Items = items
        };
    }

    protected static T ApplyPages<T>(T q, int? top, int? page) where T : IQPageable<T>
    {
        if (top.HasValue && top.Value > 0)
        {
            if (page.HasValue && page.Value > 0)
            {
                q = q.Skip(page.Value * top.Value);
            }
            q = q.Take(top.Value);
        }

        return q;
    }

    protected static T ApplyFilters<T>(T coreQuery, string search) where T : IQSearchable<T>
    {
        if (!String.IsNullOrEmpty(search))
        {
            coreQuery = coreQuery.Search(search);
        }

        return coreQuery;
    }

}

Additional Thoughts

This separation of concerns ensures a clean, maintainable codebase that makes it easier to manage and test your application.

The solution is a simplified version of the one implemented in DevCoreApp. But I hope it demonstrates how to use and extend this app. Also, I intentionally skipped database migration and changes in the database context to keep this article concise. Please see Database Readme for more details.

However, it's worth noting that this solution is just one approach among many possible solutions, and different situations may require different design patterns or architectures. It's important to choose the appropriate solution for your specific use case and requirements.