Flexibler Datenbankzugriff mit OData – Integration in deine ASP.NET Core WebApi

Jan-Hendrik Precht

Jan-Hendrik Precht begeistert sich für die Themen "lebenslanges Lernen" und "systemische Unterstützung in Personalentwicklung und Vertrieb". Zum Einen, weil Ihn sowohl die Vielfalt an möglichen Themen als auch innovative Steuerungsmöglichkeiten faszinieren. Zum Anderen, weil letztendlich alle Beteiligten etwas davon haben.

September 16, 2022
10:27
ASP.NET Core, c#, EF Core, OData

Nahezu jede WebApi ist auf die Persistenz ihrer Daten in einer Datenbank angewiesen. Für den Datenaustausch zwischen Client und WebApi (Server) gelten dann zumeist hohe Anforderungen. Einheitliche Semantik und Plattformunabhängigkeit haben im Anforderungskatalog immer eine hohe Priorität. Das Open Data Protocol (OData) erfüllt mit seinem HTTP-basierten Protokoll beide Anforderungen gleichermaßen. Das OData Protokoll wurde ursprünglich von Microsoft veröffentlicht. Softwarekonzerne wie SAP nutzen OData als Verbindungsglied zu ihren Anwendungen.

Dieser Blogbeitrag zeigt dir, wie du OData in deine eigene ASP.NET Core WebApi integrieren kannst. Hauptaugenmerk liegt auf der Abfrage von Daten aus dem relationalen Datenbanksystem MySQL, sowie ODatas Zusammenspiel mit Microsofts Objekt-Datenbank-Mapper Entity Framework Core, kurz EF Core.

Vorteile

Durch die Verwendung von OData bieten sich dir die folgenden Vorteile:

Ein flexibler Zugriff auf Datenbanksysteme, sowie Daten allgemein.
Die Möglichkeit Anwendungen zu entwickeln, die plattform- und technologieunabhängig integriert werden können.
Gut strukturierte und einfach lesbare Semantik der Requests.
Das Protokoll erfüllt internationale Standards nach OASIS und ISO.

Relationale Referenzdatenbank

Das folgende Entity Relationship Diagramm zeigt die Struktur der im Beitrag verwendeten Referenzdatenbank.

Die gezeigte Datenbank könnte einer Kalenderanwendung zugrundeliegen, in der ein Nutzer Termine in seinem Kalender verwalten und diese dann mit Erinnerungen versehen kann. Die Tabellen verbindet jeweils eine 1:N Relation.

Einstieg

Sofern du Docker auf deinem Rechner installiert hast, kannst du die Referenzdatenbank auf einem MySQL Server innerhalb eines Docker Containers ausführen. Starte dafür einfach den Docker Engine und führe anschließend das Shell-Skript run_mysql_server.sh aus.

Über den folgenden Connection String kannst du deine Anwendungen dann mit der Datenbank verbinden:

Server=localhost; Port=4200; Username=root; Password=pasSworD; Database=db_odata_aspnet;

Wenn du auf deinem Rechner einen MySQL Datenbankserver installiert hast, dann kannst du auch diesen verwenden. Stelle in diesem Fall aber eine entsprechende Konfiguration sicher.

Projekte erstellen und einrichten

Zu Beginn musst du EF Core einmalig auf deinem Rechner installieren. Optional führst du dazu das Shell-Skript ef_install.sh aus.

Erstelle dann eine passende Projektstruktur für Library und WebApi Komponenten. In der Datei Directory.Build.targets fügst du anschließend die notwendigen Pakete hinzu.

Du kannst jetzt OData WebApi-Endpoints mit ASP.NET Core erstellen. Die Pakete geben dir Zugriff auf die EF Core Basis- und Design-Time Komponenten. Weiterhin werden Komponenten für die Einbindung relationaler Datenbanken, sowie Pomelo’s MySQL Datenbank-Provider für EF Core verfügbar.

Implementierung der Model-Klassen

Die OData Model-Klassen sind bereits durch die EF Core Entity-Klassen Calendar, Meeting und Reminder implementiert.

Implementierung der Controller-Klassen

Die OData Controller-Klassen implementierst du in der folgenden Grundstruktur. Hier ist beispielhaft der CalendarController gezeigt.

// File: CalendarController.cs

using Microsoft.AspNetCore.Mvc;
using Microsoft.AspNetCore.OData.Query;
using Microsoft.AspNetCore.OData.Routing.Controllers;
using ODataSample.Library.Repositories;

namespace ODataSample.WebApi.Controllers;

[ApiController]
[Route("calendars")]
public class CalendarController : ODataController
{
    private readonly ICalendarRepository _calendarRepository;

    public CalendarController(ICalendarRepository calendarRepository)
    {
        _calendarRepository = calendarRepository;
    }

    [EnableQuery]
    [HttpGet("query")]
    public IActionResult GetQuery()
    {
        return Ok(_calendarRepository.Query);
    }

    [EnableQuery]
    [HttpGet("list")]
    public IActionResult GetList()
    {
        return Ok(_calendarRepository.List);
    }
}

Deine Controller-Klasse leitet von der Basisklasse ODataController ab. Die HttpGet Methoden erhalten das Attribut EnableQuery als Dekorator. Dadurch werden die OData Query-Optionen für die entsprechenden WebApi-Endpoints aktiviert.

Die Ausgabe von Daten der Typen IQueryable oder ActionResult<IQueryable> ermöglicht es OData-Abfragen unmittelbar in SQL-Abfragen zu übersetzen und auf dem Datenbankserver auszuführen. Dabei kombiniert OData die Fähigkeiten von LINQ und EF Core.

Bei Ausgabe von Daten anderer Typen wie z.B. IReadOnlyList oder IEnumerable werden die OData-Abfragen im Arbeitsspeicher der Anwendung ausgeführt. Die Daten müssen also vorab von der Datenbank in den Arbeitsspeicher (RAM) geladen werden. Anschließend können die OData-Abfragen dann im Arbeitsspeicher erfolgen.

Konfiguration des OData Service

Du konfigurierst den OData Service vor dem Start deiner Anwendung in der Datei Program.cs. Der folgende Codeblock zeigt dir eine mögliche Konfiguration.

// File: Program.cs (Auszug)

var builder = WebApplication.CreateBuilder(args);

builder.Services.AddControllers()
    .AddOData(options =>
    {
        options.Select();
        options.OrderBy();
        options.Filter();
        options.Expand();
        options.SetMaxTop(100);
    });

Die Methoden aktivieren hier die OData Query-Optionen $select, $orderby, $filter, $expand und $top. Die letzte Methode SetMaxTop() setzt den maximalen Wert der Query-Option $top den der Client anfordern kann. Alle Optionen sind global für sämtliche Controller-Klassen gültig.

Möchtest du z.B. die Controller-Klasse CalendarController individuell konfigurieren, dann kannst du Query-Optionen gezielt per Attribut aktivieren. Der folgende Codeblock zeigt dir ein Beispiel.

// File: CalendarController.cs (Auszug)

[Page(MaxTop = 50, PageSize = 10)]
public class CalendarController : ODataController
{
}

So kannst du die globale Konfiguration von $top mit MaxTop überschreiben und zusätzlich die Ausgabe der OData-Abfrage mit PageSize begrenzen.

Abfrage von Daten

Wenn du auf deinem Rechner Docker installiert hast, dann starte einfach den Docker Engine und führe die Shell-Skripte run_mysql_server.sh und run_webapi.sh nacheinander aus. Achte darauf, dass der Docker Container mit dem MySQL Server vollständig gestartet ist, bevor du anschließend die WebApi startest.

Beim ersten Start der Anwendung fügt die Klasse DatabaseUpdater Beispiel-Daten in die Datenbank ein. Zusätzlich kannst du in deinem Browser über die URL http://localhost:4300/ den Datenbank Adminer öffnen.

Das besprochene Anwendungsbeispiel kannst du jetzt über Postman oder cURL testen. Im GitHub Repository findest du dafür eine Postman Collection und einige Shell-Skripte.

Über den folgenden HTTP GET Request kannst du sämtliche Daten der Tabelle Calendar abfragen.

https://localhost:5001/calendars/query

Der gezeigte Request liefert das folgende JSON im Response-Body.

[
  {
    "id": "2000ff3d-03f8-4c50-8c03-d08465ada110",
    "owner": "Marvin",
    "type": "personal",
    "isActive": false,
    "meetings": []
  },
  {
    "id": "536dc919-445f-4feb-ae8b-7aac2f97ee3a",
    "owner": "Ford Perfect",
    "type": "work",
    "isActive": true,
    "meetings": []
  },
  {
    "id": "f10e426b-068f-41b3-83bd-66ab6f3c1378",
    "owner": "Arthur Dent",
    "type": "personal",
    "isActive": true,
    "meetings": []
  }
]

Da der WebApi-Endpoint in CalendarController die Daten über den Typ IQueryable ausgibt, können die OData-Abfragen unmittelbar in SQL-Abfragen übersetzt werden. Hier wird die folgende SQL-Abfrage generiert.

SELECT `c`.`Id`, `c`.`IsActive`, `c`.`Owner`, `c`.`Type`
FROM `Calendars` AS `c`;

Den oben gezeigten HTTP GET Request kannst du jetzt mit den aktivierten OData Query-Optionen erweitern. Die folgenden Abschnitte zeigen, welche Möglichkeiten sich dir bieten.

Query-Option `$select`

Die Query-Option $select ermöglicht das gezielte Selektieren und Laden von Daten aus einer Tabelle. Über den folgenden Request kannst du z.B. die Daten Owner und Type aus der Tabelle Calendar abfragen.

HTTP GET Request:

https://localhost:5001/calendars/query?$select=Owner,Type

JSON Response-Body:

[
  {
    "Owner": "Marvin",
    "Type": "personal"
  },
  {
    "Owner": "Ford Perfect",
    "Type": "work"
  },
  {
    "Owner": "Arthur Dent",
    "Type": "personal"
  }
]

SQL-Query:

SELECT `c`.`Owner`, `c`.`Type`, `c`.`Id`
FROM `Calendars` AS `c`;

Das generierte SQL-Query lädt die gewünschten Daten Owner und Type aus der Tabelle Calendars. Zusätzlich werden stets die Daten Id geladen. Sie werden hier allerdings nicht ausgegeben, siehe JSON Response-Body.

Query-Option `$orderby`

Die Query-Option $orderby ermöglicht das Sortieren von Daten in auf- und absteigender Reihenfolge. Über den folgenden Request kannst du z.B. die Daten Owner und Type aus der Tabelle Calendar abfragen und in aufsteigender Reihenfolge nach Owner sortieren.

HTTP GET Request:

https://localhost:5001/calendars/query?$select=Owner,Type&$orderby=Owner

JSON Response-Body:

[
  {
    "Owner": "Arthur Dent",
    "Type": "personal"
  },
  {
    "Owner": "Ford Perfect",
    "Type": "work"
  },
  {
    "Owner": "Marvin",
    "Type": "personal"
  }
]

SQL-Query:

SELECT `c`.`Owner`, `c`.`Type`, `c`.`Id`
FROM `Calendars` AS `c`
ORDER BY `c`.`Owner`;

Das generierte SQL-Query sortiert die Daten Owner aufsteigend. Du kannst die Daten auch in absteigender Sortierung ausgeben lassen, siehe dazu das Shell-Skript calendars_query_orderby_desc.sh.

Query-Option `$filter`

Die Query-Option $filter ermöglicht das Filtern von Daten. Du kannst $filter verwenden, wenn du ausschließlich mit Daten arbeiten möchtest, die eine bestimmte Bedingung erfüllen. Über den folgenden Request kannst du z.B. ausschließlich die Datensätze aus der Tabelle Calendars abfragen, für die die Bedingung Type gleich work erfüllt ist.

HTTP GET Request:

https://localhost:5001/calendars/query?$select=Owner,Type&$filter=Type eq 'work'

JSON Response-Body:

[
  {
    "Owner": "Ford Perfect",
    "Type": "work"
  }
]

SQL-Query:

SELECT `c`.`Owner`, `c`.`Type`, `c`.`Id`
FROM `Calendars` AS `c`
WHERE `c`.`Type` = 'work';

Das generierte SQL-Query filtert die Daten mit der WHERE Klausel und dem = Operator auf Gleichheit.

Query-Option `$expand`

Die Query-Option $expand ermöglicht dir die Abfrage von Daten aus mehreren verknüpften Tabellen. Über den folgenden Request kannst du z.B. Datensätze aus der Tabelle Calendars und der Tabelle Meetings (1:N Beziehung) abfragen. Zusätzlich muss hier in Tabelle Calendars die Bedingung Type gleich work erfüllt sein.

HTTP GET Request:

https://localhost:5001/calendars/query?$filter=Type eq 'work'&$expand=Meetings

JSON Response-Body:

[
  {
    "Meetings": [
    {
      "Id": "272858cb-895b-4a34-9eb0-2d5e3ea5abdf",
      "Title": "Lorem ipsum dolor sit amet.",
      "Description": null,
      "StartAt": "2022-09-03T17:34:48.228427",
      "Duration": 164,
      "CalendarId": "a11f7882-6060-4169-b190-8fc44b26d7a8"
    },
    {
      "Id": "f1800389-5495-432c-b388-356b32a79770",
      "Title": "Polish my friend Marvin",
      "Description": "A really sad robot",
      "StartAt": "2022-09-01T17:34:48.228422",
      "Duration": 82,
      "CalendarId": "a11f7882-6060-4169-b190-8fc44b26d7a8"
    }
    ],
    "Id": "a11f7882-6060-4169-b190-8fc44b26d7a8",
    "Owner": "Ford Perfect",
    "Type": "work",
    "IsActive": true
  }
]

SQL-Query:

SELECT `c`.`Id`, `c`.`IsActive`, `c`.`Owner`, `c`.`Type`, `m`.`Id`,
       `m`.`CalendarId`, `m`.`Description`, `m`.`Duration`, `m`.`StartAt`, `m`.`Title`
FROM `Calendars` AS `c`
LEFT JOIN `Meetings` AS `m` ON `c`.`Id` = `m`.`CalendarId`
WHERE `c`.`Type` = 'work'
ORDER BY `c`.`Id`;

Das generierte SQL-Query erweiterte die Daten aus Calendars mit der LEFT JOIN Klausel um Daten aus Meetings. Zusätzlich wird hier aufsteigend nach Id aus Calendars sortiert.

Query-Option `$top`

Die Query-Option $top ermöglicht dir die Anzahl der zurückzugebenden Datensätze zu begrenzen. Wenn du die Rückgabe einer zu großen Anzahl von Datensätzen erwartest, dann kann $top sehr nützlich sein. Über den folgenden Request kannst du z.B. die Ausgabe der Datensätze aus der Tabelle Calendars auf maximal zwei Datensätze begrenzen.

HTTP GET Request:

https://localhost:5001/calendars/query?$select=Owner,Type&$top=2

JSON Response-Body:

[
  {
    "Owner": "Ford Perfect",
    "Type": "work"
  },
  {
    "Owner": "Arthur Dent",
    "Type": "personal"
  }
]

SQL-Query:

SELECT `c`.`Owner`, `c`.`Type`, `c`.`Id`
FROM `Calendars` AS `c`
ORDER BY `c`.`Id`
LIMIT 2;

Das generierte SQL-Query begrenzt die Ausgabe mit der LIMIT Klausel auf zwei Datensätze. Zusätzlich wird mit der ORDER BY Klausel aufsteigend nach dem Primärschlüssel Id sortiert. So erhältst du für den Request immer die selbe Ausgabe.

Verwendest du die Query-Option $top zusammen mit $skip, dann ist Pagination großer Datensätze möglich. Für die Query-Option $skip wird die OFFSET Klausel in deine SQL-Query generiert. Ein Beispiel findest du im GitHub Repository.

Fazit

Der Blogbeitrag zeigt dir die ersten Schritte mithilfe von OData strukturiert Daten aus deiner ASP.NET Core WebApi abzufragen. Weiterhin hast du einen ersten Eindruck erhalten, wie OData und EF Core mit dem relationalen Datenbanksystem MySQL interagieren.

Weitere Schritte für einen produktiven Einsatz von OData werden sicherlich nötig sein. Genannt sind hier die Beschreibung des zugrundeliegenden Entity Data Model (EDM) und die Konfiguration von Zugriffsrechten sowie Sicherheitsmerkmalen.

Den Code zum Blog findest du auch auf GitHub.

Happy Coding!

Die Beitragsbilder wurden zur Verfügung gestellt von Vecteezy.com.

Flexibler Datenbankzugriff mit OData – Integration in deine ASP.NET Core WebApi

Jan-Hendrik Precht

Vorteile

Relationale Referenzdatenbank

Einstieg

Projekte erstellen und einrichten

Implementierung der Model-Klassen

Implementierung der Controller-Klassen

Konfiguration des OData Service

Abfrage von Daten

Query-Option $select

Query-Option $orderby

Query-Option $filter

Query-Option $expand

Query-Option $top

Fazit

Query-Option `$select`

Query-Option `$orderby`

Query-Option `$filter`

Query-Option `$expand`

Query-Option `$top`