Entity Framework Core automatically inserts double quotes in Oracle database-CodePudding

I'm developing an ASP.NET Core 6 MVC web app, and I want to use the integrated Identity platform to handle user roles and authentication.

Since this web app must be cross-database, I need to make this work on a Oracle autonomous database. So I've installed the Oracle.EntityFrameworkCore package from NuGet, and switched to the UseOracle method in the ConfigureServices method of my Startup.cs file.

services.AddDbContext<ApplicationDBContext>(options =>
        {
            // options.UseSqlServer(Configuration.GetConnectionString("DefaultConnection"));
            options.UseOracle(Configuration.GetConnectionString("OracleConnection"))
        });

The connection can be established, but here's the issue: when prepping up the UserManager and creating User Roles in Startup.cs, any standard method that EF Core invokes actually executes a query with double quotes around object names, e.g. doing this

private async Task CreateRoles(IServiceProvider serviceProvider)
{
   //Custom roles 
   var RoleManager = serviceProvider.GetRequiredService<RoleManager<IdentityRole>>();

   string[] roleNames = { "Admin" };

   IdentityResult roleResult;

   foreach (var roleName in roleNames)
   {
       var roleExist = await RoleManager.RoleExistsAsync(roleName);

       if (!roleExist)
       {
           // create the roles and seed them to the database: Question 1
           roleResult = await RoleManager.CreateAsync(new IdentityRole(roleName));
       }
   }

   await CreateUser(serviceProvider, "Admin");
}

This code executes this query:

SELECT "a"."Id", "a"."ConcurrencyStamp", "a"."Name", "a"."NormalizedName"
FROM "AspNetRoles" "a"
WHERE "a"."NormalizedName" = :normalizedName_0
FETCH FIRST 1 ROWS ONLY

which fails.

It searches for an "AspNetRoles" table, double quotes means it searches EXACTLY that, and thus it doesn't exist because tables are all uppercase in Oracle autonomous database, and cannot be CamelCase. I get an error ORA-00942.

I can't figure out how to make EF Core NOT use double quotes without reverting to the DevArt Oracle package.

How can I solve this?

CodePudding user response：

In the end I understood the problem wasn't only for Identity Provider tables.

So I've implemented linq2db and linqToDb.Identity and made it work by putting

OracleTools.DontEscapeLowercaseIdentifiers = false;

in the ConfigureServices method of Startup.cs.

Quick note: LinqToDb.Identity NuGet package is stuck on a May 2021 release which is incompatible with recent Linq2DB packages due to various missing references to this (e.g. this.GetTable) in the IdentityDataConnection class, so you might as well download the source code and include it in your project (or make a class library out of it) and fix the class.

Repo link: https://github.com/linq2db/LinqToDB.Identity