Avoid double quotes when querying Oracle tables created by EF Core 8 Code First

Background

I have a table model like this:

public class Book
{
    [Key]
    public Guid Id { get; set; }
    public string Name { get; set; } = default!;
    public string Author { get; set; } = default!;
    public int Pages { get; set; } = default!;
}

If I run my add migration command

dotnet ef migrations add AddBooks -p .\MyApplication.Infrastructure\ -s .\MyApplication.API\

I get the expected outcome:

migrationBuilder.CreateTable(
    name: "Books",
    columns: table => new
    {
        Id = table.Column<Guid>(type: "RAW(16)", nullable: false),
        Name = table.Column<string>(type: "NVARCHAR2(2000)", nullable: false),
        Author = table.Column<string>(type: "NVARCHAR2(2000)", nullable: false),
        Pages = table.Column<int>(type: "NUMBER(10)", nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_Books", x => x.Id);
    });

If I then update the database:

dotnet ef database update -p .\MyApplication.Infrastructure\ -s .\MyApplication.API\

The table is created as specified:

Where it goes wrong

What I expect to see is that I can query my table like this:

select * from Books b

However it states that Table or view does not exist.

If I generate my query it does that like this:

select * from "Books" b

That works correctly but it is different to how all other tables are set up.

My question

How can I generate my table(s) without needing those extra double quotes?

Oracle is case-sensitive. However, the default behaviour is to convert all unquoted identifiers to upper-case so that it appears to be case-insensitive. Therefore:

select * from Books
select * from books
select * from BoOkS
select * from BOOKS

Would all select from a table called BOOKS (upper-case) and are equivalent.

Quoted identifiers are used to enforce case-sensitivity in an identifier. Therefore:

select * from "Books"
select * from "books"
select * from "BoOkS"
select * from "BOOKS"

Would select from four different tables - Books, books, BoOkS and BOOKS respectively and only the last query is the equivalent of the queries using unquoted identifiers.

---

When you create the migration, you are generating a table named Books (in mixed-case) this means if you want to access it you will need to use a quoted identifier with the exact case:

select * from "Books"

Alternatively, you need to modify your EF code to generate upper-case identifiers in the migrations:

[Table("BOOKS")]
public class Book
{
    [Key]
    [Column("ID")]
    public Guid Id { get; set; }
    [Column("NAME")]
    public string Name { get; set; } = default!;
    [Column("AUTHOR")]
    public string Author { get; set; } = default!;
    [Column("PAGES")]
    public int Pages { get; set; } = default!;
}

Then you will be able to use unquoted identifiers (in any case) in your queries.

Else look to see if there is a migration setting that will change the case of all the identifiers to upper-case so you can use unquoted identifiers.

Addendum

If you add nuget package

EFCore.NamingConventions

to the project that holds your DbContext and override OnConfiguring you can do the following:

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
{
    optionsBuilder.UseUpperSnakeCaseNamingConvention();

    base.OnConfiguring(optionsBuilder);
}

This will cause the Books table to be created like this:

migrationBuilder.CreateTable(
    name: "BOOKS",
    columns: table => new
    {
        ID = table.Column<Guid>(type: "RAW(16)", nullable: false),
        NAME = table.Column<string>(type: "NVARCHAR2(2000)", nullable: false),
        AUTHOR = table.Column<string>(type: "NVARCHAR2(2000)", nullable: false),
        PAGES = table.Column<int>(type: "NUMBER(10)", nullable: false)
    },
    constraints: table =>
    {
        table.PrimaryKey("PK_BOOKS", x => x.ID);
    });