.NET 5.0, ORACLE, ASP.NET Identity with N-tier architecture-Part 01

In this tutorial series we will be creating ASP.NET Core MVC Web application using ORACLE Database. Before we dive into anything, whole code with database scripts will be available on Github.This tutorial series will be split into 3 parts.

First part is setting up our development environment, setting up multiple projects with structure that will use couple of design patterns like N-tier architecture, SOC, Unit of Work and scaffolding existing ORACLE 12.2 database and creating our first migration.

Second part will be explaining our architecture of application, implementing Repository pattern for our Data layer, keeping SoC, implementing base interfaces for CRUD operations on existing database. Then we will create our service or Business logic layer by implementing interfaces that will represent Unit of Work. And last, in our web project we will use model to view model mapping and create our mapping profile.

Third part will be explaining how to use ASP.NET Identity with ORACLE database and with Windows authentication. So lets start shall we?
First things first lets explain what type of project are we going to use and how are we going to connect to database using code first approach of entity framework. Open your Visual Studio (Mine is 2019), then File -> New -> Project.

Next we are going to choose out project type. We will be using ASP.NET Core Web App(Model-View-Controller) type of project.

Then click next and name your project and solution and choose folder. For out N-Tier artchitecture we will be creating three projects. First will be our Web project, Second will be our Business logic layer and last will be Data logic layer. So when creating we will name our Web project with .Web extension. Next two project will have .Bll and .Dll extensions as well.

Next we will use .NET 5.0 as our target framework and will not choose any type of authentication for now, as we will be adding Windows authentication with ASP.NET Identity later. Click create and we are done.

So now we have to add two more projects for our business and data layers. Right click on solution and choose New project.

Choose Class library(not .NET Framework one), and click next. Choose same name of web project but with .Dll and .Bll extensions.

Click Next and choose .NET 5.0 as target framework and last create your project.

We will have structure like on next image.

Next thing that we will do is to setup our Dll project. This will include installing additional packages for connecting to Oracle database, organizing our project with SoC pattern (separation of concerns) and setting up our first migration.

So go to NuGet package manager, either by right clicking on Dll project and choosing “Manage NuGet packages” or opening console. Then browse for next packages and install them:

• Microsoft.EntityFrameworkCore.Design
• Oracle.EntityFrameworkCore

Next we will add a few folders that will structure our application in order to implement our design patterns and organize it properly.
First add next 4 folders:

• Context
• Entities
• Migrations
• Repositories

And in Repositories add Abstract and Implementation folders. These folders will hold our abstract repositories for accessing our data. Context folder will hold our generated Context class, Migrations folder will hold our migrations and Entities will hold our generated classes/models for data access. Project structure should look like this.

Next we will generate our Context class and Entities. Just to mention, we are generating context and entities from existing database, which means that we are scaffolding from existing database. Initially if we did not have already existing database, we would just create those classes and execute our first migration that will create new tables in our database.

First we need to install dotnet cli. For more reference check this official link:

Go to package manager console, choose .Dll project and execute this command:

So, now we will generate our context and entity classes from our existing database schema on Oracle 12c release 2 (12.2). To do that we need to use dotnet ef command with specifying of couple parameters:
-o tells us what output directory of our Dll project will entities be placed in. In our case it is Entities

• -context-dir tells us what output directory of our Dll project will context be placet. In our case it is Context folder
  -c tells what will be name of our
  -p tells us on what project command will execute. In our case it id Dll project
  -f tells us that we do not need to confirm our scaffolding

Whole command should look like this:

dotnet ef dbcontext scaffold "User Id=EXAMPLE_SCHEMA;Password=my_password;Data Source=server:1521/orcl;Connection Timeout=600;min pool size=0;connection lifetime=18000;PERSIST SECURITY INFO=True;" Oracle.EntityFrameworkCore -o Entities --context-dir Context -c EXAMPLE_SCHEMA_Context -f -p NTierOracleIdentityExmple.Dll

After we execute command we will generate context class, entities and migration in our folders that we have created.

Also there is a shortcut to dotnet ef and that is EF Core Power Tools that can be found on next link.

It can be installed as and VS2019 extension that can generate needed stuff either by scaffolding existing DB using code first or database first approach(create edmx file) and can generate migrations for existing code.
So lets assume that we have our schema already created in our database with some tables in it. So it might look like this.

After execution of previous command, our project should include next generated classes for our context and existing entities.

Next thing is to modify our Context. First install next package from NuGet:

• Microsoft.EntityFrameworkCore.Proxies
  https://stackoverflow.com/questions/55234943/what-is-the-equivalent-of-configuration-proxycreationenabled-in-ef-core
  Next modify OnConfiguring method in context and add next line that contain our connection string:

if (!optionsBuilder.IsConfigured)
{
//Proxy for navigation properties //Microsoft.EntityFrameworkCore.Proxies
optionsBuilder.UseLazyLoadingProxies().UseOracle(“User Id=SCHEMA_NAME;Password=xxxxx;Data Source=server:1521/SID;Connection Timeout=600;min pool size=0;connection lifetime=18000;PERSIST SECURITY INFO=True;”);
}

First our edit contains connection string that will be used from our context to connect and create/apply new migrations. Second change is change that is using UseLazyLoadingProxies that includes navigational properties from our context entities that we select. This is explained in next Stackoverflow question.

Our last change will include changing the OnModelCreating method. Add next line:

base.OnModelCreating(modelBuilder);

Now we are ready to create our first migration. Our first migration will include only one table that will represent our log table for logging data from our application. Later in next part we will create repository, service and controller actions for implementing CRUD functionalities over that table. Also we will create our CRUD functionalities over Identity table in part 3.

So first things first, lets define our table. Create next class in Entities folder of our DLL project.

public class Log
{
  public int pk { get; set; }
  public string CreatedBy { get; set; }
  public DateTime CreationDate { get; set; }
  public string ModifiedBy { get; set; }
  public DateTime ModifiedDate { get; set; }
  public DateTime? Date { get; set; }
  public string Value { get; set; }
}

Next thing is to create how our table is going to look when we create it in Oracle database schema. Open Context class from Context folder of our DLL project.
First add our table as DbSet.

public virtual DbSet<Log> Log{ get; set; }

Next is to define how our table is going to look when our context class build our entity. Add next code to OnModelCreating method.

modelBuilder.Entity<Log>(entity =>
{
  entity.HasKey(e => e.pk).HasName("LOG_PK");
  entity.ToTable("LOG");
  entity.Property(e => e.pk)
  .HasColumnType("NUMBER(10)")
  .HasColumnName("PK");
  entity.Property(e => e.CreatedBy)
  .HasMaxLength(256)
  .IsUnicode(false)
  .HasColumnName("CREATED_BY");
  entity.Property(e => e.CreationDate)
  .HasColumnType("DATE")
  .HasColumnName("CREATION_DATE");
  entity.Property(e => e.ModifiedBy)
  .HasMaxLength(256)
  .IsUnicode(false)
  .HasColumnName("MODIFIED_BY");
  entity.Property(e => e.ModifiedDate)
  .HasColumnType("DATE")
  .HasColumnName("MODIFIED_DATE");
  entity.Property(e => e.Value)
  .IsUnicode(false)
  .HasColumnName("LOG_VALUE");
});

Only thing remaining is to create our first migration and update database. Lets create our DATA_LOG migration.

dotnet ef migrations add EXAMPLE_SCHEMA_LOG -p NTierOracleIdentityExmple.Dll -c NTierOracleIdentityExample.Dll.Context.EXAMPLE_SCHEMA_Context -o Migrations

After executing this command, our migration class will be created in Migrations folder of our DLL project.

Now lets update our database

dotnet ef database update EXAMPLE_SCHEMA_LOG -p NTierOracleIdentityExmple.Dll -c NTierOracleIdentityExample.Dll.Context.EXAMPLE_SCHEMA_Context

Now if you open the Oracle database from any client tool like SQLDeveloper or Toad you will see our table created, with migration history table.

That is it for now. Next part will be explaining how to create base repository, services for our CRUD operations with couple of design patterns.iting here...