Entity Framework and PostgreSQL: quotation marks issue

entity-framework entity-framework-core postgresql

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!

1
1
6/14/2018 5:48:32 PM

Accepted Answer

Easy done!


Go to OnModelCreating method.

You need an extension method (the code is shared below)

modelBuilder.NamesToSnakeCase();

Create ModelBuilderExtensions class and paste the following:

public static void NamesToSnakeCase(this ModelBuilder modelBuilder)
{
    foreach (var entity in modelBuilder.Model.GetEntityTypes())
    {
        // Replace table names
        entity.Relational().TableName = entity.Relational().TableName.ToSnakeCase();

        // Replace column names            
        foreach (var property in entity.GetProperties())
        {
            property.Relational().ColumnName = property.Name.ToSnakeCase();
        }

        foreach (var key in entity.GetKeys())
        {
            key.Relational().Name = key.Relational().Name.ToSnakeCase();
        }

        foreach (var key in entity.GetForeignKeys())
        {
            key.Relational().Name = key.Relational().Name.ToSnakeCase();
        }

        foreach (var index in entity.GetIndexes())
        {
            index.Relational().Name = index.Relational().Name.ToSnakeCase();
        }
    }
}

You can see ToSnakeCase extension method - here it is in StringExtensions class:

public static string ToSnakeCase(this string input)
{
    if (string.IsNullOrEmpty(input)) { return input; }

    var startUnderscores = Regex.Match(input, @"^_+");
    return startUnderscores + Regex.Replace(input, @"([a-z0-9])([A-Z])", "$1_$2").ToLower();
}

Remove the db, recreate migrations, then run dotnet ef database update - bingo!

enter image description here

1
7/1/2018 2:15:31 PM

Popular Answer

Just create your tables and fields using only lowercase simbols.



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