ASP.NET MVC5 - Keeping Users in Oracle Database

Question

Once creating a ASP.NET MVC5 project (with target framework is .NET 4.5.1 and the authentication type is Individual User Account), so what is the most elegant way to configure the project so that it keeps the user, claims, roles etc. in an Oracle 12c database? I mean, how can I keep the authorization/authentication data in Oracle without deforming the automatically-generated MVC5 project structure.

I guess changing the <defaultConnection> tag is not enough and there should be another Oracle implementation to replace Microsoft.AspNet.Identity.EntityFramework. It would be very helpful to gather an answer telling what to do step by step right from the start, where the template project is generated by VisualStudio (i.e. which references should be added to the project; in which way the Web.config file should be arranged? Should the Oracle tables be named strictly as AspNetUsers, AspNetClaims etc. or can I inject the already existing table names into the code side?)

Note: Devart's dotConnect for Oracle is out of my scope as it is not a free product. I use Oracle ManagedDataAccess for database access and to make use of Entity Framework, but using the ASP.NET Identity 2 + EntityFramework6 logic with Oracle database (without deeply changing the structure of the classical MVC5 project) has been frustrating to me.

Answer 1

This may arrive a bit late for you but I'll leave it in case anyone else runs into the same problem. So I finally managed to make Identity 2.0 and Oracle work together. The following steps work if you don't want to make any changes to the default IdentityUser (ex. if you're ok with having a char ID instead of int or long) and just want the tables on your existing Oracle schema.

1. Create Identity tables on Oracle. You can change the table names if you want to, just make sure to include the necessary columns for Identity to work with it. You can also add any extra columns you may need on your application (script originally found on Devart, I copied it to a gist in case URL breaks):

   Gist here

2. If you're using an EDMX file, you need to add a new connection string cause the one that gets generated automatically won't work, you need a standard connection string. Try following this template:

   <add name="IdentityContext" connectionString="Data Source=localhost:1521/xe;PASSWORD=password;USER ID=username;" providerName="Oracle.ManagedDataAccess.Client" />

3. Tell your ApplicationDbContext to use your new connectionString

   public ApplicationDbContext()
       : base("IdentityContext", throwIfV1Schema: false)
   {
   }
   

4. Tell Identity to use your existing schema and tables. Add this method inside the ApplicationDbContext definition found in IdentityModels.cs:

   protected override void OnModelCreating(DbModelBuilder modelBuilder)
   {
       base.OnModelCreating(modelBuilder); // MUST go first.
   
       modelBuilder.HasDefaultSchema("YOUR_SCHEMA"); // Use uppercase!
   
       modelBuilder.Entity<ApplicationUser>().ToTable("AspNetUsers");
       modelBuilder.Entity<IdentityRole>().ToTable("AspNetRoles");
       modelBuilder.Entity<IdentityUserRole>().ToTable("AspNetUserRoles");
       modelBuilder.Entity<IdentityUserClaim>().ToTable("AspNetUserClaims");
       modelBuilder.Entity<IdentityUserLogin>().ToTable("AspNetUserLogins");
   }
   

5. Rebuild and thats it!

Let me know if it works for you!