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?

Answer 1

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

Answer 2

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