Summary

The website content provides a comprehensive guide on setting up a REST API project using Oracle Entity Framework Core 5 with a focus on clean architecture, CQRS/Repository patterns, and boilerplate code for CRUD operations, sorting, filtering, and paging, with the source code available on GitHub.

Abstract

The article outlines a step-by-step tutorial on creating a .NET Core 5 REST API project with Oracle Entity Framework Core 5, detailing the process of scaffolding a clean architecture project using the OnionAPI Visual Studio template. It covers configuring the solution to connect to an Oracle database, setting up the ApplicationDbContext, and adding the necessary Oracle Entity Framework Core NuGet package. The tutorial also includes instructions on database migration, emphasizing the need for manual migration when using Oracle Entity Framework Core due to the lack of support for automatic database creation. The author provides insights into the benefits of using such templates for rapid prototyping and maintaining consistency in development projects, while also offering the source code for the tutorial on GitHub.

Opinions

The author believes that the OnionAPI template can significantly enhance development efficiency by providing a structured approach to building REST API projects.
The use of Oracle Entity Framework Core 5 is presented as a modern and viable alternative to other data access technologies, with the tutorial demonstrating its integration into a .NET Core 5 project.
The author suggests that the ability to swap backend databases without rewriting code is a key advantage of using clean architecture design and Entity Framework Core technology.
The article implies that the OnionAPI template, which the author has contributed to, is a valuable tool for organizations looking to improve governance and delivery times in their development processes.
The author expresses openness to community feedback, inviting readers to request a version of the OnionAPI template prewired with Oracle Entity Framework Core if there is interest.
The author endorses an AI service as a cost-effective alternative to ChatGPT Plus (GPT-4), indicating a preference for this service based on performance and cost.

REST API with Oracle EntityFrameworkCore 5

On February 11, 2021, the senior principal product manager at Oracle Alex Keh announced the release of Oracle Entity Framework Core 5. After seeing the news, I was wondering what would it take to wire up a C# REST API project to work with the new version 5.

In this story, I will demonstrate how to scaffold a clean architecture NetCore 5 REST API project — prewired with CQRS/Repository patterns and boilerplate code for CRUD, sort, filter, and paging. Then configure the solution to use Oracle Entity Framework Core for data access. Source code is available in Github.

Tutorial Content

The tutorial has three parts:

Part 1: Scaffold a REST API project Using Visual Studio template OnionAPI, create a Clean Architecture solution running on NetCore 5.

Part 2: Configure the solution to work with the Oracle database Update connection string and ApplicationDbContext to point to Oracle DB and add Oracle Entity Framework Core Nuget package.

Part 3: Database Migration Run add-migration and update-database

Opensource Project Highlights

VSIXTemplateOnionAPI is a Visual Studio template for scaffolding a clean architecture ASP.NET REST API solution consisting of five projects

Domain — Entities and the common models
Application — Interfaces, CQRS Features, Exceptions, Behaviors
Infrastructure.Persistence — Application-specific database access (default MSSQL)
Infrastructure.Shared — Common services such as Mail Service, Date Time Service, Mock, and so on
WebApi — Controllers for REST API resources and endpoints

The underline tech stack provides loosely-coupled and inverted-dependency architecture with good design patterns and practices. At the time of this writing, the OnionAPI template is at version 1.3 which can generate the REST API projects running on NetCore 5.

You can download the OnionAPI template from the Visual Studio Marketplace. After download, click on the VSIXTemplateOnionAPI.vsix to install the extension. If you are new to Visual Studio Extension, visit Manage extensions for Visual Studio for installation instructions.

Prerequisites

The following tools/skills are recommended

Visual Studio 2019 Community — free editor for ASP.NET Core WebAPI C#.
Familiarity with C# model classes and CQRS pattern.
OnionAPI template installed
Access to Oracle DB version 11 or higher

Let’s get started.

Part 1: Scaffold REST API project

The easiest way to create a new project is to start from a project template for a particular type of application or website. A project template consists of a basic set of pre-generated code files, config files, assets, and settings.

When you first open Visual Studio, the start window appears, and from there, you can choose to create a new project. See Figure 1 for more information.

Figure 1— Menu option to create a new project in Visual Studio

Follow the steps below to scaffold a new project

Step 1: On the Create a new project screen, search for and select the OnionAPI template and click on the Next button. See Figure 2 for visual aids.

Figure 2— Select OnionAPI template to scaffold a new project

Step 2: Enter the project name OracleEFCore5 and location C:\apps\devkit\ApiResources then click on the Create button. See Figure 3 for visual aids.

Figure 3 — Entering parameters to create a new project

Part 2: Configure the solution to use Oracle DB

By default, the OnionAPI template has a DefaultConnection string points to MSSQL DB. We will perform the following tasks

Add a new connection string to point to Oracle DB
Set the ApplicationDbContext to use the Oracle connection string
Add Oracle Entity Framework Core Nuget package

Task 1: Add a new Oracle connection string

Follow the steps below to add Oracle connection string

Open the appsettings.json in the WebAPI project and locate the ConnectionStrings section
Comment out DefaultConnection string
Add below connection string and make the necessary change to match your environment

OracleConnection”: “DATA SOURCE=(DESCRIPTION=(ADDRESS=(PROTOCOL=TCP)(HOST=localhost)(PORT=1521))(CONNECT_DATA=(SERVICE_NAME=ORCLCDB.localdomain)));USER ID=dummy; Password=dummy;”

See Figure 4 for a screenshot of ConnectionStrings section after adding a new Oracle connection string.

Figure 4— Example of Oracle connection string

Task 2: Set the ApplicationDbContext to use the Oracle connection

Follow the steps below

Open the file ServiceRegistration.cs in the Infrastructure.Peristence project
Locate the UserSqlServer section and comment it out
Add below code

services.AddDbContext(options =>

options.UseOracle(

configuration.GetConnectionString(“OracleConnection”),

b => b.MigrationsAssembly(typeof(ApplicationDbContext)

.Assembly.FullName)

.UseOracleSQLCompatibility(“12”)));

See Figure 5 for a screenshot of ServiceRegistration.cs after adding a new UseOracle code. Notice that the setting UseOracleSQLCompatibility(“12”) is the version I have on my desktop. If you are on 19c, you can comment it out. I was able to test both 12 and 19c on my laptop.

Figure 5— Screenshot of the ServiceRegistration.cs in the Infrastructure.Peristence project

Task 3: Add Oracle Entity Framework Core Nuget package

Follow the steps below

Right mouse click on the OracleEFCore5.Infrastructure.Persistence and select the option Manage Nuget Packages…
Browse for Oracle
Select Oracle.EntityFrameworkCore v5.21.1
Click on Install

See Figure 6 for visual aids.

Figure 6— Install Oracle Entity Framework Core via Nuget Package Manager UI

Part 3: Database Migration

The OnionAPI template includes code in the Startup.cs (dbContext.Database.EnsureCreated) to migrate the database when running the project for the first time. However, according to StackOverflow, that feature is not supported in Oracle Entity Framework Core.

No problem. Follow the steps below to migrate manually

In Visual Studio, go to Tools > NuGet Package Manager > Package Manager Console
In the Default project, select OracleEFCore5.Infrastructure.Persistence
Type in command add-migration and hit Enter
Type in update-database and hit Enter

See Figure 7 for visual aid.

Figure 7— Package Manager Console to run the database migration

The above data migration created the Positions table and seeded it with 1,000 rows using Bogus library. See Figure 8 for a screenshot of the table in SQL Developer.

Figure 8— Positions table seeded with 1,000 records

Hit F5 to run the solution. You should see the Swagger as shown in Figure 9. Try out the page, sort, filter on the Positions resource.

References

Filter, Sort, Page, and Shape Data in Asp.Net Core REST API with OnionAPI Template
Bogus Library for Fake Data In ASP.NET Core WebAPI
NetCore 5 Pagination 100,000+ Rows
DevOps Series: Running Oracle Database 19C in a Docker Container (I followed this story to set up Oracle 19c)
Install Oracle database on Docker and connect with SQL Developer (I followed this youtube to set up Oracle 12)

Summary

With clean architecture design and Entity Framework Core technology, developers can swap the backend database without code rewrite.

When managing the development, many organizations are struggling with governance and look for value-added process improvement tools. Templates such as OnionAPI (which I am the author of) can help to increase consistency while decrease delivery times. If you like to see a template like OnionAPI prewired with Oracle Entity Framework Core, leave a comment in the Responses section and I will consider creating one.

The source code for this story is available on Github.