ASP.NET Core 6: Using Entity Framework Core with Oracle Database with Code-First Approach

-

In this article, we will see how to use Oracle Database with Entity Framework Core with Code-First Approach. The reason behind writing a post on this is, recently while conducting training, one of my students raised the query regarding the EF Core with Oracle. Since Oracle is one of the most powerful databases, various enterprise applications are using it for the past several years and since the .NET Core is the most popular technology for building modern apps, the scenarios for accessing the Oracle database in .NET Core apps the use Entity Framework Core is common.

Oracle Data Provider for .NET Entity Framework Core

The Oracle Data Provider for .NET i.e. ODP.NET is used to access data from the Oracle database in the .NET Core apps using Entity Framework Core. This uses the Oracle.EntityFrameworkCore.dll, a managed library. This data provider allows using Code-First and Database-First approaches to perform Read/Write Operations with Oracle database.   

Implementation

To implement code for the application, I have an Oracle 19c Docker image. This image can be downloaded from this link.  To interact with the database from the image, I have used Oracle SQL Developer.  This can be downloaded from this link. 

To run the Docker Image run the command as shown in Listing 1 from the Command Prompt


docker run --name oracontainer -p 1521:1521 -p 5500:5500 -e ORACLE_SID=ORASID -e ORACLE_PDB=ORCLPDB1 -e ORACLE_PWD=P@ssw0rd_ -e ORACLE_CHARACTERSET=AL32UTF8

Listing 1: The Command to Run the image

We are running an image on oracontainer and the database is accessible on port 1521. The default username is 'sys'. To uniquely identify the database instance the SID is set as ORASID. The ORACLE_PDB is the Oracle database service name. The command shown in Listing 1 will take 2-3 minutes to start the database. Once the database is started we can access it from Oracle SQL Developer as shown in figure 1.



Figure 1: Oracle SQL Developer

We can create Tables, Stored Procs, etc. using this tool. Let's create .NET Core 6 API application.

Step 1: Open Visual Studio 2022 and create a new ASP.NET Core API application targeted to .NET 6. Name this application as Core_APIOracle. In this project add the following NuGet packages

Microsoft.EntityFrameworkCore

Microsoft.EntityFrameworkCore.Design

Oracle.EntityFrameworkCore

Step 2: In the project, add a new folder and name it Models. In this folder add a new class file and name it as ProductsInfo.cs. In this class file, we will add an entity class as shown in Listing 2

using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Core_APIOracle.Models
{
    [Table("Products")]
    public class ProductsInfo
    {
        [Key]
        public int ProductRecordId { get; set; }
        [Required]
        [StringLength(100)]
        public string ProductId { get; set; } = String.Empty;
        [StringLength(400)]
        public string ProductName { get; set; } = String.Empty;
        [StringLength(200)]
        public string Manufacturer { get; set; } = String.Empty;
        [StringLength(1000)]
        public string Description { get; set; } = String.Empty;
        public int Price { get; set; }
    }
}


Listing 2: The ProductsInfo class     

We need to create DbContext class for the Code-First approach. In the Models folder, add a new class file and name it as OraDbContext.cs. In this class, file add code as shown in Listing 3


using Microsoft.EntityFrameworkCore;
using Oracle.EntityFrameworkCore;
namespace Core_APIOracle.Models
{
    public class OraDbContext : DbContext
    {
        public DbSet<ProductsInfo> Products { get; set; }
        public OraDbContext(DbContextOptions<OraDbContext> options):base(options)
        {
        }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            base.OnConfiguring(optionsBuilder);
        }
    }
}

Listing 3: The DbContext class

Using this class, we will be able to connect to the Oracle database and perform Read/Write Operations. We need to configure the Connection String for the Oracle database using appsettings.json file. Modify the appsettings.json file by adding Connection String as shown in listing 4


 "ConnectionStrings": {
    "OraDbConnection": "User Id=sys; Password=P@ssw0rd_;DBA Privilege=SYSDBA; 
        Data Source=localhost:1521/ORASID;"
  }

Listing 4: Oracle Connection String              

 In the connection string, we have set the Data Source as the Oracle instance running on the port 1521 on the local machine inside the Docker Container. In the connection string I have used Privilege=SYSDBA so that the required access on database for Read/Write operations are granted for the sys user. (NoteL: Read the Oracle Database Users and Roles for more information.)

Let's modify the Program.cs to register the OraDbContext class in the dependency container so that the connection with Oracle database will be established and using Code-First migration commands tables will be created in the Oracle database. Add the code as shown in Listing 5 to register the OraDbContext class in dependency container

builder.Services.AddDbContext<OraDbContext>(options => 
{
    options.UseOracle(builder.Configuration.
    	GetConnectionString("OraDbConnection"));
});


Listing 5: Register the OraDbContext class in the Depednency Container

Step 2: Open the Command Prompt and navigate to the Core_APIOracle project folder. Run following commands to generate Migrations and Update the database to generate table as shown in Listing 6

dotnet ef migrations add firstMigration -c Core_APIOracle.Models.OraDbContext

 dotnet ef database update -c Core_APIOracle.Models.OraDbContext

Listring 6: Migration and Database Update Commands

Once the database update command is executed successfully, the Products table will be generated int the Oracle database as shown in Figure 2



Figure 2: Product Table generated       

We can add sample data in the table by opening the table as shown in Figure 3



Figure 3: Adding records in the table 

Step 3: In the Controllers add a new Empty API controller. Name this controller as ProductInfoController and add the code in this controller as shown in Listing 6


    [Route("api/[controller]")]
    [ApiController]
    public class ProductInfoController : ControllerBase
    {
        private readonly OraDbContext _context;

        public ProductInfoController(OraDbContext context)
        {
            _context = context;
        }

        [HttpGet]
        public async Task<IActionResult> Get()
        {
            var result = await _context.Products.ToListAsync();
            return Ok(result);
        }

        [HttpPost]
        public async Task<IActionResult> Post(ProductsInfo productsInfo)
        {
            var result = await _context.Products.AddAsync(productsInfo);
            await _context.SaveChangesAsync();
            return Ok(result.Entity);
        }
    }

Listing 6: The ProductInfoController to perform Read/Write operations

The controller class shown in listing 6 is injected with OraDbContext class. The controller perform Read/Write operations using Get() and Post() methods. We can test these methods by runnng the API project and using the Swagger UI in the browser as shown in Figure 4



Figfure 4:  The Swagger UI for the Get call

Similarly, we can slo test the Post call to add new record. The code for this article can be downloaded from this link.

Conclusion: Using the Oracle Data Provider for .NET with Entoty Framework Core, we can easily connect to Oracle database and perform Read/Write operations. You have to make sure that the correct configuration of Oracle database and the User grants are provided to the .NET 6 application. 

Popular posts from this blog

Uploading Excel File to ASP.NET Core 6 application to save data from Excel to SQL Server Database

-
Image
Recently, while conducting a session on ASP.NET Core 6, my client raised a query regarding uploading excel files to the ASP.NET Core MVC application and then further saving data from excel to the SQL Server Database table. I gave him the solution and then thought to write a post on it. As we all know, the Excel Workbook is one of the most heavily used files to save data. But to persist data it is recommended that it should be stored in an SQL Server database.  If the data is stored in an SQL Server database it can be queried easily.  Figure 1, explains an implementation of the application which we are going to discuss in the article. Figure 1: The ASP.NET Core That accepts Excel files and then further store data from Excel Files in SQL Server Database The   ExcelDataReader.DataSet Library To read excel files in ASP.NET Core applications, we need to use the ExcelDataReader.DataSet package. This is a lightweight and fast library for reading Excel files in .NET applications....
Read more

ASP.NET Core 6: Downloading Files from the Server

-
Image
In this article, we will implement the file downloading feature using ASP.NET Core 6 application. In real-world applications, we can have various files like reports, spreadsheets, images, PDF documents stored on the server. (Note that all cloud-hosted applications prefer to store these files on the storage services, e.g. Blob, S3, etc.). But what if the client prefers to store files on the separate file server mapped to the web application host server? Then, in that case, we may need to download these files from the file server. In this article, we will implement exactly the same thing.   Step 1: Open Visual Studio 2022 and create a new ASP.NET Core MVC Application targetted to .NET 6. Name this application as Core6_FileDownload. In this project add a new folder and name it as ServerFiles. In this file add some images. (You can add Excel, PDF, Word, Files). Step 2: Since we need to read the ServerFiles folder using the ASP.NET Core application, we need to add the IFi...
Read more