Last active
May 16, 2022 22:06
-
-
Save chrisfcarroll/56875d0d5812b3f9c8b3baf7230457d4 to your computer and use it in GitHub Desktop.
.Net DbContext for Postgres that lowercases identifiers so that quotes are not needed everywhere
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
public class AppDbOnPostgres : DbContext | |
{ | |
public AppDbOnPostgres(NpgsqlConnection dbConnection):base(dbConnection){} | |
protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder) | |
=> optionsBuilder | |
.UseNpgsql(dbConnection) | |
.ReplaceService<ISqlGenerationHelper,NpgsqlSqlGenerationLowercasingHelper>(); | |
} |
This file contains bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
using System.Linq; | |
using System.Text; | |
using System.Text.RegularExpressions; | |
using Microsoft.EntityFrameworkCore.Storage; | |
using Npgsql.EntityFrameworkCore.PostgreSQL.Storage.Internal; | |
#pragma warning disable EF1001 | |
namespace YourNamespaceOfChoice | |
{ | |
/// <summary>A replacement for <see cref="NpgsqlSqlGenerationHelper"/> | |
/// that converts PascalCase_Csharpy_Identifiers to alllowercase. | |
/// So table and column names with no embedded punctuation get | |
/// generated with no quotes or delimiters, making life that | |
/// much sweeter for all.</summary> | |
/// <a href="https://stackoverflow.com/questions/35914530/case-insensitive-name-of-tables-and-properties-in-entity-framework-7">https://stackoverflow.com/questions/35914530/case-insensitive-name-of-tables-and-properties-in-entity-framework-7</a> | |
public class NpgsqlSqlGenerationLowercasingHelper : NpgsqlSqlGenerationHelper | |
{ | |
//Don't lowercase ef's migration table, locale descriptions, … | |
static readonly Regex[] DontAlter= new[] | |
{ | |
new Regex("__efmigrationshistory",RegexOptions.Compiled|RegexOptions.IgnoreCase), | |
new Regex(@"\w{1,3}_\w{1,3}\.UTF-\d{1,2}",RegexOptions.Compiled|RegexOptions.IgnoreCase) | |
}; | |
static string Customize(string input) => DontAlter.Any(r=>r.IsMatch(input)) ? 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)); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment
From https://stackoverflow.com/questions/35914530/case-insensitive-name-of-tables-and-properties-in-entity-framework-7