Entity Framework and PostgreSQL: quotation marks issue

Question

The problem I am facing is around SQL queries in pgAdmin 4.

Entity Framework (including its Core edition) capitalizes the names of tables and columns.

Which means your SQL will then look something like

select e."Id", e."Text" from "Entries" e
where e."Text" like '%implicated%'

I was googling the way to prevent Entity Framework from capitalizing names but didn't found out much.

Is there a workaround to avoid wrapping table and column names in quotes?

Thanks in advance!

Answer 1

For EF Core 3 and above

By default postgres saves all table names, column names as lowercase.

Some databases seem to not require any configuration with EF Core in order to map Camel cased names in the application to case insensitive names in the database. For instance with MS SQL Server, you can have the application entities with CamelCased, but then in the database you don't case them and everything works fine.

With Postgres this seems not to be the case and therefore some configuration of is required.

Npgsql recommend this nuget package EFCore.NamingConventions which provides similar functionality to @Alex Herman's answer. Although you don't have to invoke the method on every property, you just use it once.

There are two places you can use it

1. In your DbContext.cs

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseNpgsql(...)
        .UseLowerCaseNamingConvention();

2. Or in your Startup.cs

public void ConfigureServices(IServiceCollection services)
{
   services.AddEntityFrameworkNpgsql().AddDbContext<DilaDbContext>(opt => 
       opt.UseNpgsql(
           Configuration.GetConnectionString("DefaultConnection"), 
           b => b.MigrationsAssembly("NameOfAssembly"))
       .UseLowerCaseNamingConvention() // <======== HERE
   ...

Npgsql.org says that

Starting with 3.0.0, you can use the EFCore.NamingConventions plugin to automatically set all your table and column names to snake_case instead:

There are also some other naming conventions included, I used the lowercase naming convention, because that's what I've used most

They offer these conventions

• UseSnakeCaseNamingConvention: FullName becomes full_name
• UseLowerCaseNamingConvention: FullName becomes fullname
• UseUpperCaseNamingConvention: FullName becomes FULLNAME

Probably don't use the uppercase one, as its the uppercase letters in the database objects name that causes the EF config to require the quotes