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?

Solution 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.

Solution 2

1. Override DelimitIdentifier in NpgsqlSqlGenerationHelper like this:

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

2. 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) 
       { }
   }
   

Solution 3

Here's a compact solution for .NET Core 3.X (may work in 5.X not sure). 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 >();

Solution 4

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.

Solution 5

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.

View more solutions

Comments

[csharpfolk]

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)

[Jeremy Holovacs]

.NET Core 2.1 did change a few things here, though... the above is only a solution for 2.0.

[Auspex]

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.

[TBR]

> 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

[Shay Rojansky]

Note also github.com/efcore/EFCore.NamingConventions, which is a plugin that automatically applies naming conventions such as snake_case

[Ramazan Sağır]

It was the best solution for me. Thanks

[Luiz Palte]

Man! You realy sabe my life.. Thanks!

[Shay Rojansky]

For anyone wanting lower-case identifiers, check out 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.

[Chris F Carroll]

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