Case insensitive name of tables and properties in Entity Framework 7

Question

I use Entity Framework 7 with Npgsql adapter. Sql generated by EF seems like

SELECT "r"."Id", "r"."Name" FROM "public"."Role" AS "r"

and it doesn't work in Postgres, because case-sensitive policy. To make it work i need to write create table script

CREATE TABLE "Role" (
    "Id" int,
    "Name" varchar(200)
);

But it's ugly. Is there the way to make EF generate scripts without quotes or with lowercase naming style?

score 7 · Answer 1 · edited May 16 '22 at 22:08

Here's a compact solution for .NET Core 3.X - Net5 (might well work in Net6). This will assume all tables and columns are in lowercase and quoted. You'll find that unconditional quoting helpful if someone names a table/column conflicting with a reserved keyword (e.g.: "user", "role", "default", "comment" etc).

    /// <summary>A replacement for <see cref="NpgsqlSqlGenerationHelper"/>
    /// to convert PascalCaseCsharpyIdentifiers to alllowercasenames.
    /// So table and column names with no embedded punctuation
    /// get generated with no quotes or delimiters.</summary>
    public class NpgsqlSqlGenerationLowercasingHelper : NpgsqlSqlGenerationHelper
    {
        //Don't lowercase ef's migration table
        const string dontAlter="__EFMigrationsHistory";
        static string Customize(string input) => input==dontAlter? input : input.ToLower();
        public NpgsqlSqlGenerationLowercasingHelper(RelationalSqlGenerationHelperDependencies dependencies) 
            : base(dependencies) { }
        public override string DelimitIdentifier(string identifier)
            => base.DelimitIdentifier(Customize(identifier));
        public override void DelimitIdentifier(StringBuilder builder, string identifier)
            => base.DelimitIdentifier(builder, Customize(identifier));
    }

Plugging it in is straightforward enough:

optionsBuilder.UseNpgsql(...)
  .ReplaceService<ISqlGenerationHelper, NpgsqlSqlGenerationLowercasingHelper >();

I would give double-upvote if I could :-) nb for Postgres I inherited from the NpgsqlGenerationHelper because that helper knows the postgres reserved words and special characters — Chris F Carroll, Oct 02 '21 at 18:05

score 5 · Answer 2 · answered Nov 12 '17 at 14:37

I really don't really like having PascalCase identifiers in my PostgreSql database since I do a lot of manual querying directly against the database, so for my new .NET Core solution I kinda went to an extreme to change it.

First, I defined my standard ApplicationDbContext using my PascalCase entity classes and marked it as abstract, then I created a PgDbContext specifically for my Postgres implementation.

Next, I created a helper method like so:

    public static string FromPascalCaseToSnakeCase(this string str)
    {
        return string.IsNullOrWhiteSpace(str) ? str : string.Concat(str.Select((x, i) => i > 0 && char.IsUpper(x) ? "_" + x.ToString() : x.ToString())).ToLower();
    }

I then overrode some key methods by implementing some Npgsql related classes:

public class LowercaseSqlGenerationHelper : RelationalSqlGenerationHelper
{
    public LowercaseSqlGenerationHelper(RelationalSqlGenerationHelperDependencies dependencies) : base(dependencies)
    {
    }

    public override void DelimitIdentifier(StringBuilder builder, string identifier)
    {
        base.DelimitIdentifier(builder, identifier.FromPascalCaseToSnakeCase());
    }

    public override void DelimitIdentifier(StringBuilder builder, string name, string schema)
    {
        base.DelimitIdentifier(builder, name.FromPascalCaseToSnakeCase(), schema.FromPascalCaseToSnakeCase());
    }

    public override string DelimitIdentifier(string identifier)
    {
        return base.DelimitIdentifier(identifier.FromPascalCaseToSnakeCase());
    }

    public override string DelimitIdentifier(string name, string schema)
    {
        return base.DelimitIdentifier(name.FromPascalCaseToSnakeCase(), schema.FromPascalCaseToSnakeCase());
    }
}

public class LowercaseQuerySqlGenerator : NpgsqlQuerySqlGenerator
{
    public LowercaseQuerySqlGenerator(QuerySqlGeneratorDependencies dependencies, RelationalSqlGenerationHelperDependencies rSGenDep, SelectExpression selectExpression) : 
        base(
            new QuerySqlGeneratorDependencies(dependencies.CommandBuilderFactory, 
                new LowercaseSqlGenerationHelper(rSGenDep), 
                dependencies.ParameterNameGeneratorFactory, 
                dependencies.RelationalTypeMapper)
            , selectExpression)
    {
    }
}

public class LowercaseHistoryRepository:NpgsqlHistoryRepository
{
    public LowercaseHistoryRepository(HistoryRepositoryDependencies dependencies) : base(dependencies)
    {
    }

    protected override string ExistsSql
    {
        get
        {
            var builder = new StringBuilder();

            builder.Append("SELECT EXISTS (SELECT 1 FROM pg_catalog.pg_class c JOIN pg_catalog.pg_namespace n ON n.oid=c.relnamespace WHERE ");

            if (TableSchema != null)
            {
                builder
                    .Append("n.nspname='")
                    .Append(SqlGenerationHelper.EscapeLiteral(TableSchema.FromPascalCaseToSnakeCase()))
                    .Append("' AND ");
            }

            builder
                .Append("c.relname='")
                .Append(SqlGenerationHelper.EscapeLiteral(TableName.FromPascalCaseToSnakeCase()))
                .Append("');");

            return builder.ToString();
        }
    }
}

Finally, wired up the IServiceCollection configuration like so:

        services.AddDbContext<PgDbContext>(
            options =>
            {
                options.UseNpgsql(config.GetSection("ConnectionStrings:ApplicationContext").Value)
                    .ReplaceService<ISqlGenerationHelper, LowercaseSqlGenerationHelper>()
                    .ReplaceService<IQuerySqlGenerator, LowercaseQuerySqlGenerator>()
                    .ReplaceService<IHistoryRepository, LowercaseHistoryRepository>();
            },
            ServiceLifetime.Scoped);
        services.AddScoped<ApplicationDbContext>(di => di.GetService<PgDbContext>());

With this, all my table names, columns, and constraints were named in snake_case instead of PascalCase, which meant I did not have to worry about using quoted identifiers in my manual queries. My entity classes are cased the way I like them, and my database names are also the way I like them.

YMMV, but it works pretty swimmingly for me. It's important to note that while this does not actually remove the quotes from EF queries, it makes needing the quotes for manual queries disappear.

.NET Core 2.1 did change a few things here, though... the above is only a solution for 2.0. — Jeremy Holovacs, Jul 15 '18 at 13:59
For EF7 there might be a bug. Migrations doesn't work properly and I had to exclude identifier `__EFMigrationsHistory` from the customizations (the fields therein could be customized). — Peter L, Jun 13 '23 at 01:10

Maksim Fedorov · Answer 3 · 2016-12-23T12:01:51.600

Override DelimitIdentifier in NpgsqlSqlGenerationHelper like this:

public class SqlGenerationHelper : NpgsqlSqlGenerationHelper
{
    public override string DelimitIdentifier(string identifier) => identifier.Contains(".") ? base.DelimitIdentifier(identifier) : identifier;
}

Replace ISqlGenerationHelper with your class using ReplaceService method:

public class MyContext : DbContext
{
    public virtual DbSet<MyTable> MyTable { get; set; }

    public MyContext(DbConnection connection) :
           base(new DbContextOptionsBuilder().UseNpgsql(connection)
                                             .ReplaceService<ISqlGenerationHelper, SqlGenerationHelper>()
                                             .Options) 
    { }
}

I'm using EF7 and found I needed to override more methods from. `NpgsqlSqlGenerationHelper` per @jeremy-holovacs answer. — Peter L, Jun 13 '23 at 00:32

score 4 · Answer 4 · answered May 04 '18 at 23:15

4

FYI version 2.1 of the Npgsql EF Core provider will only quote identifiers when that's needed (e.g. when they contain an uppercase letter).

More importantly, everyone wanting snake case columns (or anything else besides the current behavior) can simply use the EF Core fluent API to manually specify whatever table and column name they want. It's also quite easy to write code that goes over all entities and properties, and automatically defines their database names by applying snake-case conversion or something else.

This is better than changing any provider service, and will always work, whereas changing the SQL generation service (or any other) could be brittle.

answered May 04 '18 at 23:15

Shay Rojansky

15,357
2
40
69

1

This is much better than your original answer (yes, we know _why_ the SQL gets quoted, but in fact it's a backwards solution) but not upvoted because you give no examples. – Auspex Jan 29 '20 at 15:34
2

Note also https://github.com/efcore/EFCore.NamingConventions, which is a plugin that automatically applies naming conventions such as snake_case – Shay Rojansky Feb 17 '21 at 08:10

score 3 · Answer 5 · answered Mar 15 '16 at 16:28

To do this, you would need to swap out the SQL generation service with your own, quote-less, lowercase version. To do this, you will need to understand how EF uses DI (try reading Understanding EF Services), and need to replace the service that generates SQL. In EF, this could be ISqlGenerationHelper, IMigrationsSqlGenerator, or IUpdateSqlGenerator depending on the circumstance..

score 3 · Answer 6 · answered May 04 '18 at 18:13

As you can see in NpgsqlSqlGenerationHelper.cs:

static bool RequiresQuoting(string identifier)
{
        var first = identifier[0];
        if (!char.IsLower(first) && first != '_')
            return true;

Npgsql thinks that identifiers that start with upper-case letter needs quoting. After a bit of thinking I implemented a solution described in https://andrewlock.net/customising-asp-net-core-identity-ef-core-naming-conventions-for-postgresql/ (converts all PascalCase identifiers to snake-case). It is a bit simplistic right now but I how EF Core soon will provide a way to define custom naming conventions.

score -1 · Accepted Answer · edited Apr 05 '16 at 22:02

-1

There's a very good reason Npgsql generates quotes everywhere - so you definitely should not remove them (even if it's technically possible as @natemcmaster says). Identifiers without quotes are automatically converted to lowercase by PostgreSQL. Entity Framework needs to be able to map C# properties to database columns, but C# properties are case-sensitive; so if you remove database case sensitivity you're shooting yourself in the foot...

Unless you have a real problem (aside from the perceived ugliness) you should leave things as they are.

edited Apr 05 '16 at 22:02

rmac

812
6
13

answered Mar 31 '16 at 16:11

Shay Rojansky

15,357
2
40
69

8

Please also think about Ops and other people that will want to use you app database. Using `"` everywhere is more than annoying. Almost all current DBs are case insensitive by default. Also it is not a good practice to have two properties on the same C# class that differe only in case (also they will not be usable from e.g. VisaulBasic) – csharpfolk May 04 '18 at 17:47
> but C# properties are case-sensitive Every serializer are able to manage that, this is not a real argument And I can't agree more with the previous comment, have to use quotes is a mess for Ops – TBR Oct 15 '20 at 09:33
2

For anyone wanting lower-case identifiers, check out https://github.com/efcore/EFCore.NamingConventions. This is an EF Core plugin which can make your tables and columns be snake_case, which is the general standard for PostgreSQL. At this point you no longer need to quote them, and Npgsql's EF Core provider won't do that. – Shay Rojansky Jul 29 '21 at 15:48

Case insensitive name of tables and properties in Entity Framework 7

7 Answers7

Linked