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

Question

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?

kbd · Accepted Answer · 2024-05-22 08:54:50Z

3

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);
    });

edited May 22, 2024 at 8:54

kbd

4,5397 gold badges40 silver badges82 bronze badges

answered May 22, 2024 at 8:26

MT0

173k12 gold badges70 silver badges136 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

kbd Over a year ago

That was easy ... I would prefer a migration setting over having to manually annotate everything. I will hold off accepting as an answer for now in the hopes someone (or you) can provide that info. (If it takes too long I will just accept)

MT0 Over a year ago

@kbd I don't know the answer for the migration settings - however if you look at the Using a Migration Operation documentation it gives an example of how to override the SQL that is generated and the example includes the line .Append(sqlHelper.DelimitIdentifier(operation.Name)) if it was changed to .Append(operation.Name) then the generated query would be using unquoted identifiers (but may also be vulnerable to SQL injection). ...

MT0 Over a year ago

You need to investigate whether something similar is possible for tables (and understand the implications).

kbd Over a year ago

I found it. I have edited your answer to have a single solution.

MT0 Over a year ago

@kbd I was just adding something similar - however your edit was slightly more comprehensive.

Collectives™ on Stack Overflow

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

1 Answer 1

5 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

5 Comments

Your Answer

Sign up or log in

Post as a guest

Related