ASP.NET MVC5 - Keeping Users in Oracle Database

asp.net-identity-2 asp.net-mvc-5 database entity-framework-6 oracle

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.

1
10
3/5/2015 1:15:11 PM

Accepted Answer

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!

7
5/9/2016 6:09:13 AM

Popular Answer

Andres Bejarano worked great for me but I want to add something that would have savde me a lot of time. First make sure you can connect to your database using Oracle.ManagedDataAccess.Client (I had problems with my TNS names file see OracleConnection.Open is throwing ORA-12541 TNS no listener).

Second, I was getting the following error when I tried to register a user:

No Entity Framework provider found for the ADO.NET provider with invariant name 'Oracle.ManagedDataAccess.Client'. Make sure the provider is registered in the 'entityFramework' section of the application config file. See http://go.microsoft.com/fwlink/?LinkId=260882 for more information.

This was because I didn't have all the correct Nuget packages installed and/or I had messed up the web.config.

Make sure you have ODAC installed http://www.oracle.com/technetwork/topics/dotnet/downloads/index.html

If you uninstall/reinstall the following Nuget packages in order. You should get around this error:

  1. UnInstall-Package Oracle.ManagedDataAccess.EntityFramework
  2. UnInstall-Package Oracle.ManagedDataAccess
  3. UnInstall-Package EntityFramework -Version 6.1.1
  4. Install-Package EntityFramework -Version 6.1.1
  5. Install-Package Oracle.ManagedDataAccess
  6. Install-Package Oracle.ManagedDataAccess.EntityFramework


Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow