Quickly Connect an External Database to an ASP.NET Core API using OData

Robert Devlin — Fri, 03 Mar 2023 00:50:35 +0000

Overview

In this article, I'd like to introduce a way we can use well-documented building blocks in a powerful configuration to quickly start developing APIs using ASP.NET. Many times, thin APIs are needed to fetch and update data, and using OData and Entity Framework, then we can easily add new tables and data models to our external databases and utilize them in ASP.NET Core.

Pre-Requisites

We will need to make sure that some packages are installed in Visual Studio before we get started.

Why?

A lightweight way to get started building simple backend APIs. Using OData and its templated routing allows convenient dependency injection and service logic for barebones APIs. Once setup, accessing data and adding endpoints can be done by updating the API with the desired data models, creating a new controller, and defining bindings to allow for automatic routing / data access. In doing so, this reduces the amount of code that has to be maintained while allowing development to move quickly. Testing on top of this allows for quick, maintainable code, allowing me to focus on domain modeling and service logic when necessary.

First API Using WeatherForecast

To leverage all the default set-up of the ASP.NET Core API samples, we can make a new sample API. If using the ASP.NET Core CLI tool, then it is as simple as dotnet new webapi --name <YOUR_WEB_API_PROJECT_NAME> which will generate the dotnet webapi template. Similarly, from Visual Studio, if the ASP.NET Core package is installed, then we can just create the template by choosing the "ASP.NET Core Web API" template. More instructions can be found in the official documentation.

Filtering by Language:C# and Project Type: WebAPI when creating a new project in Visual Studio

This template generates a WebAPI with a sample controller built for an example weatherforecast. To check that everything is working, call this endpoint by sending a request using cUrl, Postman, or by going to the following address in your favorite browser: https://localhost:<port>/weatherforecast. The port number for both HTTP and HTTPS is randomly generated between 5000-5300 and 7000-7300 respectively, so please check and/or configure the port number for your web API. This can be found in the Properties/launchSettings.json file.

The default web API uses Swagger, so it should be easy to check which endpoints are exposed.

More details on the WeatherForcecast API can be found in this tutorial.

Once we can see that this endpoint works, we're ready to start using the other building blocks of this approach. Next let's talk about Entity Framework.

Entity Framework

Entity Framework (EF) acts as an object-relational mapper (O/RM) that lets us define data models purely in C# and then maps that to database tables automatically. We could generate models from tables and vice versa, but for the scope of this article, we will handcode the tables and data models to understand how they interact. More information can be found in the official documentation.

External Database

In my experience, database tables are usually created first, and then that data is exposed to some external source via an API to be accessed. Let's build our API on that assumption. We can create some tables to hold our example data. Let's build it in the style of the web api tutorial listed above, creating a Todo API.

We will build two tables with the following schema. In our case I am using SQL Server, so the DDL will look like this:

-- Todo.sql
CREATE TABLE [dbo].[Todo]
(
    [Id] INT NOT NULL PRIMARY KEY,
    [Name] NTEXT NULL,
    [isComplete] BIT NOT NULL,
    [CategoryId] INT NULL,
    [DeadlineDate] DATETIME NULL, 
    CONSTRAINT FK_Todo_TodoCategory FOREIGN KEY ([CategoryId]) 
    REFERENCES [TodoCategory](Id)
);

-- TodoCategory.sql
CREATE TABLE [dbo].[TodoCategory]
(
    [Id] INT NOT NULL PRIMARY KEY, 
    [Name] NCHAR(20) NOT NULL
)

We will also add the following sample data to our external database.

BEGIN TRANSACTION;

-- create some deadlines for our todo items
DECLARE @thisMorning AS datetime = '2023-02-15 08:00:00.000';
DECLARE @tonight AS datetime = DATEADD(hour, 1, @thisMorning);
DECLARE @tomorrowMorning AS datetime = DATEADD(day, 1, @thisMorning);
DECLARE @tomorrowLunch AS datetime = DATEADD(hour, 4, @tomorrowMorning);
DECLARE @tomorrowNight AS datetime = DATEADD(day, 1, @tonight);

INSERT INTO TodoCategory(TodoCategoryId, CategoryName) VALUES (1, 'Home');
INSERT INTO TodoCategory(TodoCategoryId, CategoryName) VALUES (2, 'Shopping');
INSERT INTO TodoCategory(TodoCategoryId, CategoryName) VALUES (3, 'Learning');
INSERT INTO TodoCategory(TodoCategoryId, CategoryName) VALUES (4, 'Work');

INSERT INTO Todo(TodoId, Name, isComplete, TodoCategoryId, DeadlineDate) VALUES (1, 'Pick up groceries', 'True', 2, NULL);
INSERT INTO Todo(TodoId, Name, isComplete, TodoCategoryId, DeadlineDate) VALUES (2, 'Take out trash', 'True', 1, NULL);
INSERT INTO Todo(TodoId, Name, isComplete, TodoCategoryId, DeadlineDate) VALUES (3, 'Study Spanish for 30 minutes', 'True', 3, NULL);
INSERT INTO Todo(TodoId, Name, isComplete, TodoCategoryId, DeadlineDate) VALUES (4, 'Make bed', 'True', 1, @thisMorning);
INSERT INTO Todo(TodoId, Name, isComplete, TodoCategoryId, DeadlineDate) VALUES (5, 'Prepare lunch for tomorrow', 'False', 1, @tonight);
INSERT INTO Todo(TodoId, Name, isComplete, TodoCategoryId, DeadlineDate) VALUES (6, 'Make bed', 'False', 1, @tomorrowMorning);
INSERT INTO Todo(TodoId, Name, isComplete, TodoCategoryId, DeadlineDate) VALUES (7, 'Meet Jake and Miranda for Lunch', 'False', 4, @tomorrowMorning);
INSERT INTO Todo(TodoId, Name, isComplete, TodoCategoryId, DeadlineDate) VALUES (8, 'Go into office', 'False', 4, @tomorrowMorning);
INSERT INTO Todo(TodoId, Name, isComplete, TodoCategoryId, DeadlineDate) VALUES (9, 'Brush up on C#', 'False', 3, @tomorrowNight);

COMMIT TRANSACTION;

Create Data Models

We will create our own Todo data models to work with. I made a new folder called "Models" and submitted the code beneath.

namespace TodoProject.Models
{
    public class Todo
    {
        public int TodoId { get; set; }
        public string? Name { get; set; }
        public bool IsComplete { get; set; }

        public TodoCategory? TodoCategory { get; set; }

        public DateTime? DeadlineDate { get; set; }
    }
}

Similarly, we will also add some additional information to categorize our Todo objects.

namespace TodoProject.Models
{
    public class TodoCategory
    {
        public int TodoCategoryId { get; set; }
        public string CategoryName { get; set; } = string.Empty;
    }
}

Link Database to Data Models

To link our database to our data models, we need to bind them using a DbContext. The DbContext is an interface between the database and EF Core, so by declaring the data bindings, we have finished the O/RM mapping.

using Microsoft.EntityFrameworkCore;

namespace ODataWithSqlDbApi.Models;

public class TodoContext : DbContext
{
    /// <summary>
    /// Default Constructor
    /// </summary>
    /// <param name="options"></param>
    public TodoContext(DbContextOptions<TodoContext> options) : base(options) { }

    public DbSet<Todo> Todos { get; set; }
    public DbSet<TodoCategory> TodoCategories { get; set; }

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Todo>().ToTable("Todo");
        modelBuilder.Entity<TodoCategory>().ToTable("TodoCategory");
    }
}

Connect External Database

To let our API know that it needs to talk to an external databse, then we must specify how to connect to that external database. For this, we can use a connection string to specify this behavior. For our case, we will use a simple public database that is locally run, but this can be easily extended to a server in the cloud as well with access credentials. In my appsettings (or appsettings development file), I add the following connection string, where DB_NAME is the name of my database:

"ConnectionStrings": {
    "SqlServer": "Data Source=(localdb)\\ProjectModels; Initial Catalog=DB_NAME; Integrated Security=true"
  },

In Program.cs, we bind the database to the API using our custom DbContext:

builder.Services.AddDbContext<TodoContext>(options =>
    options.UseSqlServer(new SqlConnection(builder.Configuration.GetConnectionString("SqlServer")))
    );

OData

OData is a standard for consuming data from RESTful APIs. It standardizes routing and can automatically create routes based on linked data. It also enables data querying and filtering in the URL which frees up service logic that would otherwise need to be implemented as API logic. As a result, it allows us to pull data easily without having to write even more boilerplate code to fetch basic data types. More can be learned here.

OData Queries

OData's primary strength comes from its queries. By standardizing data querying, it allows for us to write URLs with query parameters that can serve as business logic, filtering and sorting data for our purposes. This is best described by OData themselves, found here. ASP.NET Core can leverage these queries, but they must be enabled per endpoint.

⚠️ Because OData queries are written at the URL level, it is easy for a less-nice person to write queries that can cause a lot of load on a system. For example, if a table has millions of rows and that table is linked to a data model connected via OData, then it is possible to make a request that returns all rows, causing incredible load on the server. OData has powerful out-of-the-box behavior, but additional logic and data checking should be required for performance and data security.

EDM Binding

To access the routing, we need to bind our objects to a data model to traverse. This is our EDM. More information on EDMs can be found here.

static IEdmModel GetTodoEdmModel()
{
    ODataConventionModelBuilder builder = new ODataConventionModelBuilder();
    builder.EntitySet<Todo>("Todos");
    builder.EntitySet<TodoCategory>("TodoCategories");
    builder.EnableLowerCamelCase();
    return builder.GetEdmModel();
}

We can connect our EDM to our routing, and enable queries all at the same time. This lets us be specific with which queries we allow on which API endpoints. In this case, we will not be specific and just enable several different types of queries for demonstration purposes.

builder.Services.AddControllers().AddOData(opt =>
{
    opt.AddRouteComponents("", GetTodoEdmModel()) // bind Todos and TodoCategories to the root path
    .Select().OrderBy().Filter().Expand(); // enabled OData queries
});

As a note, we define a path to both Todos and TodoCategories, but this implementation only covers the Todos endpoint.

An OData Controller

In our case, we need to do some dependency-injection to make the most of OData's capabilities, as well as update our controller's interface to take advantage of the OData libraries.

We will use the ODataController interface and implement our controllers accordingly. If we give access to the data layer directly in an MVC-style approach, then we can write a controller that retrieves a list of todo objects simply like this:

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.OData.Query;
using Microsoft.AspNetCore.OData.Routing.Controllers;
using Microsoft.EntityFrameworkCore;
using TodoProject.Models;


namespace TodoProject.Controllers;
public class TodosController : ODataController
{
    private readonly TodoContext context;

    public TodosController(TodoContext todoContext) => context = todoContext;

    [EnableQuery()]
    [HttpGet]
    public async Task<IActionResult> Get()
    {

      // business logic, if any

      return Ok(await context.Todos.Include(todo => todo.TodoCategory).ToListAsync());
    }
}

One highlight in the above code is the .Include(todo => todo.TodoCategory). EF Core allows us to "eagerly load" our data in one call. This means that every time we ask for our Todo list we will receive the TodoCategory associated with it as well.

Demonstration

Given the following sample data, we can query it in many different ways without needing to implement any service logic.

GET /todos with no additional filtering to see all todo data looks like this.

{
    "@odata.context": "https://localhost:7162/$metadata#Todos",
    "value": [
        {
            "id": 1,
            "name": "Pick up groceries",
            "isComplete": true,
            "categoryId": 2,
            "deadlineDate": null
        },
        {
            "id": 2,
            "name": "Take out trash",
            "isComplete": true,
            "categoryId": 1,
            "deadlineDate": null
        },
        {
            "id": 3,
            "name": "Study Spanish for 30 minutes",
            "isComplete": true,
            "categoryId": 3,
            "deadlineDate": null
        },
        {
            "id": 4,
            "name": "Make bed",
            "isComplete": true,
            "categoryId": 1,
            "deadlineDate": "2023-02-15T08:00:00+09:00"
        },
        {
            "id": 5,
            "name": "Prepare lunch for tomorrow",
            "isComplete": false,
            "categoryId": 1,
            "deadlineDate": "2023-02-15T09:00:00+09:00"
        },
        {
            "id": 6,
            "name": "Make bed",
            "isComplete": false,
            "categoryId": 1,
            "deadlineDate": "2023-02-16T08:00:00+09:00"
        },
        {
            "id": 7,
            "name": "Meet Jake and Miranda for Lunch",
            "isComplete": false,
            "categoryId": 4,
            "deadlineDate": "2023-02-16T08:00:00+09:00"
        },
        {
            "id": 8,
            "name": "Go into office",
            "isComplete": false,
            "categoryId": 4,
            "deadlineDate": "2023-02-16T08:00:00+09:00"
        },
        {
            "id": 9,
            "name": "Brush up on C#",
            "isComplete": false,
            "categoryId": 3,
            "deadlineDate": "2023-02-16T09:00:00+09:00"
        }
    ]
}

Demo of OData Queries

Select / Filter

If we want to see the unfinished todos and when they need to be done by, then we can write a request like this to see that data: /todos?select=name,isComplete,deadlinedate&filter=isComplete eq false

{
    "@odata.context": "https://localhost:7162/$metadata#Todos(name,isComplete,deadlineDate)",
    "value": [
        {
            "name": "Prepare lunch for tomorrow",
            "isComplete": false,
            "deadlineDate": "2023-02-15T09:00:00+09:00"
        },
        {
            "name": "Make bed", 
            "isComplete": false,
            "deadlineDate": "2023-02-16T08:00:00+09:00"
        },
        {
            "name": "Meet Jake and Miranda for Lunch",
            "isComplete": false,
            "deadlineDate": "2023-02-16T08:00:00+09:00"
        },
        {
            "name": "Go into office",
            "isComplete": false,
            "deadlineDate": "2023-02-16T08:00:00+09:00"
        },
        {
            "name": "Brush up on C#",
            "isComplete": false,
            "deadlineDate": "2023-02-16T09:00:00+09:00"
        }
    ]
}

Access Nested Data

If we want to see a certain type of Todo, we can query by Category to see the results. The nested relationship is also considered by OData because we are eagerly loading our nested entities.

/todos?$filter=TodoCategory/CategoryName eq 'Learning'

{
    "@odata.context": "https://localhost:7162/$metadata#Todos",
    "value": [
        {
            "todoId": 3,
            "name": "Study Spanish for 30 minutes",
            "isComplete": true,
            "deadlineDate": null
        },
        {
            "todoId": 9,
            "name": "Brush up on C#",
            "isComplete": false,
            "deadlineDate": "2023-02-16T09:00:00+09:00"
        }
    ]
}

We can also sort by deadline. This returns all the Todos with deadline dates in descending order.

/todos?orderBy=DeadlineDate desc&filter=deadlineDate ne null

{
    "@odata.context": "https://localhost:7162/$metadata#Todos",
    "value": [
        {
            "todoId": 9,
            "name": "Brush up on C#",
            "isComplete": false,
            "deadlineDate": "2023-02-16T09:00:00+09:00"
        },
        {
            "todoId": 6,
            "name": "Make bed",
            "isComplete": false,
            "deadlineDate": "2023-02-16T08:00:00+09:00"
        },
        {
            "todoId": 7,
            "name": "Meet Jake and Miranda for Lunch",
            "isComplete": false,
            "deadlineDate": "2023-02-16T08:00:00+09:00"
        },
        {
            "todoId": 8,
            "name": "Go into office",
            "isComplete": false,
            "deadlineDate": "2023-02-16T08:00:00+09:00"
        },
        {
            "todoId": 5,
            "name": "Prepare lunch for tomorrow",
            "isComplete": false,
            "deadlineDate": "2023-02-15T09:00:00+09:00"
        },
        {
            "todoId": 4,
            "name": "Make bed",
            "isComplete": true,
            "deadlineDate": "2023-02-15T08:00:00+09:00"
        }
    ]
}

Testing

When using frameworks and libraries that "do the work for you," it is even more important to write tests to check that code.

In our case because we are relying on OData completely for our query logic, we can write integration tests to check that the controller is behaving as expected. We can use an in-memory database with sample data to isolate the data for these tests, and in a real-world scenario, we can use these tests to monitor regression in the future. The following code uses MSTest in a test project setup within my solution. Help for doing that can be found here.

Building our Testing Database

Because our API is connected to an external database, we should use a database for testing that is similar to it. Our test database will be in-memory for performance and size, and the recommendation by EF Core is to generate a SQLite database in-memory for testing. This article and its attached sample code was extremely helpful for this section. Essentially, we need to start and maintain our SQLite database during testing. We can use the using keyword to manage this resource.

First we must make an in-memory context that is similar to our custom TodoContext. This code is directly based off of the sample code found in the EF documentation found here.

using Microsoft.Data.Sqlite;
using Microsoft.EntityFrameworkCore;
using System.Data.Common;
using TodoProject.Models;

namespace TodoProjectTest
{
    public class InMemoryTestContext : IDisposable
    {
        private readonly DbConnection _connection;
        private readonly DbContextOptions<TodoContext> _contextOptions;

        public InMemoryTestContext()
        {
            // Generate a connection to a SQLite In-Memory database for testing
            _connection = new SqliteConnection("Filename=:memory:");
            _connection.Open();

            // Include the open connection as part of our TodoContext
            _contextOptions = new DbContextOptionsBuilder<TodoContext>()
                .UseSqlite(_connection)
                .Options;

            // by calling 'using', we invoke the Dispose method which will close our connection when done 'using'
            using var context = new TodoContext(_contextOptions);

            // Ensure the database is created
            context.Database.EnsureCreated();
        }
       public TodoContext CreateContext() => new TodoContext(_contextOptions);

       public void Dispose() => _connection.Dispose();
    }

}

Testing the Controller

That means that we can then write code as follows to test our endpoint:

using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using TodoProject.Controllers;
using TodoProject.Models;

namespace TodoProjectTest.Controllers
{
    [TestClass]
    public class TodoControllerTest
    {
        private readonly TestSampleData tsd = new();

        [TestMethod]
        public async Task TodoController_Get_NoTodos()
        {
            // build empty test database
            using var context = new InMemoryTestContext().CreateContext();
            TodosController controller = new(context);

            IActionResult res = await controller.Get();
            Assert.IsInstanceOfType(res, typeof(OkObjectResult));

            var resultValue = ((OkObjectResult)res).Value;
            Assert.IsNotNull(resultValue);
            Assert.IsInstanceOfType(resultValue, typeof(List<Todo>));

            List<Todo> resultTodoList = (List<Todo>)resultValue;
            Assert.IsTrue(resultTodoList.Count == 0);
        }

        [TestMethod]
        public async Task TodoController_Get_OneTodo()
        {
            // build empty test database
            using var context = new InMemoryTestContext().CreateContext();

            // add sample data
            context.AddRange(tsd.InitTodoCategory, tsd.InitTodo);
            context.SaveChanges();

            // our expected return value
            Todo expected = tsd.InitTodo;

            TodosController controller = new(context);

            IActionResult res = await controller.Get();
            Assert.IsInstanceOfType(res, typeof(OkObjectResult));

            var resultValue = ((OkObjectResult)res).Value;
            Assert.IsNotNull(resultValue);
            Assert.IsInstanceOfType(resultValue, typeof(List<Todo>));

            List<Todo> resultTodoList = (List<Todo>)resultValue;
            Assert.IsTrue(resultTodoList.Count == 1);

            // use json serialization to compare object instead of overwriting equality method
            Assert.AreEqual(JsonConvert.SerializeObject(resultTodoList.First()), JsonConvert.SerializeObject(expected));
        }
    }
}

As a note, I re-use sample data in a sample data class, but feel free to define your test data in your own way.

using TodoProject.Models;

namespace TodoProjectTest.Controllers
{
    public class TestSampleData
    {
        // Initial Data for Test Database
        public TodoCategory InitTodoCategory;
        public Todo InitTodo;
        public Todo TomorrowTodo;

        public TestSampleData()
        {

            DateTime date = DateTime.UtcNow;
            this.InitTodoCategory = new TodoCategory { TodoCategoryId = 1, CategoryName = "TodoCategory1" };

            this.InitTodo = new Todo
            {
                TodoId = 1,
                Name = "CompletedTodo",
                IsComplete = true,
                DeadlineDate = date,
                TodoCategory = InitTodoCategory
            };
            this.TomorrowTodo = new Todo {
                TodoId = 2,
                Name = "UnfinishedTodo",
                IsComplete = false,
                DeadlineDate = date.AddDays(1),
                TodoCategory = InitTodoCategory
            };
        }
    }
}

Benefits of Reducing Service Complexity

With less code to update, we can add new features faster. With good test-writing habits, then we can efficiently add functionality to our API server with just a handful of lines of code per dataset.

Benefits of Auto-Routing Controllers

Instead of manually declaring routing using attributes or the like, we can focus on data modeling and make sure that the data modeling is intuitive and accurate. The API naming will reflect the data, which also removes room for human error when defining API routes.

Drawbacks of Framework Reliance

A lot of the explicit connections and data routing is a little obfuscated behind libraries. As a result, it can be hard to know what is truly going on. This is particularly challenging if new to API development or if new to ASP.NET Core. This article hopes to demystify some of those connections.

Malicious Query Use

OData has one large weakness which is its security. Using OData blindly exposes the dataset via the API. It means that if a dataset is large enough, then a naive call to an API will return large amounts of data, consuming server resources dramatically. ASP.NET Core documentation has a page on security guidance which I believe is a must-read if the database is expected to scale. It also could expose sensitive information within an object's member variable if it is not explicitly excluded. Read the full article here.

Implicit Routing

If bindings on the EDM or naming of a controller does not match the expected formats, then the endpoint routing will not complete. Using Swagger while developing is a great tool to check if the endpoint is accessible or not. For troubleshooting if this occurs, I recommend understanding Attribute Routing and Entity Routing which is part of the ODataController.

Notes

If there are issues importing libraries or recognizing functions in the project, please check that the following libraries are installed.

Libraries for API

    <ItemGroup>
        <PackageReference Include="Microsoft.AspNetCore.OData" Version="8.0.12" />
        <PackageReference Include="Microsoft.AspNetCore.OpenApi" Version="7.0.2" />
        <PackageReference Include="Microsoft.EntityFrameworkCore" Version="7.0.2" />
        <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="7.0.2" />
        <PackageReference Include="Swashbuckle.AspNetCore" Version="6.4.0" />
    </ItemGroup>

Libraries for Testing

    <PackageReference Include="Microsoft.NET.Test.Sdk" Version="17.3.2" />
    <PackageReference Include="MSTest.TestAdapter" Version="2.2.10" />
    <PackageReference Include="MSTest.TestFramework" Version="2.2.10" />
    <PackageReference Include="coverlet.collector" Version="3.1.2" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Sqlite" Version="7.0.2" />

DEV Community: Robert Devlin