Case insensitive name of tables and properties in Entity Framework 7

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

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

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.