pluggable schemes for mapping c# names

[SepiaGroup]

I am using EF Core with PostgreSQL/Npgsql. Since PostgreSQL is case sensitive for object names and Properties are proper cased in C# this cause you to use double quotes when writing native sql.

could it be possible to create a pluggable scheme to allow the mapping of object names, similar to what is done when you want to camel case objects to json using CamelCasePropertyNamesContractResolver().

i asked this of the PostgresSQL developers and they think this functionality would be best implemented in EF Core and not in npgsql.

npgsql/efcore.pg#21

[roji]

Just to add a bit of detail, this would provide users the choice to map C# names like MyClassName to my_class_name, etc.

[roji]

Another note: the same mapping scheme provider would ideally be used for reverse-engineering.

[rowanmiller]

It's actually super easy to override the default naming scheme with some simple code in your OnModelCreating method.

foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
    foreach (var property in entity.GetProperties())
    {
        property.Relational().ColumnName = property.Name.ToLower();
    }
}

Or I prefer to write it like this...

modelBuilder.Model.GetEntityTypes()
    .SelectMany(e => e.GetProperties())
    .ToList()
    .ForEach(p => p.Relational().ColumnName = p.Name.ToLower());

[roji]

Of course... that makes total sense.

[SepiaGroup]

modelBuilder does not seem to be in OnConfiguring but is in OnModelCreating, which is where i place this code.

to lower table names i added

// Lower Table Names
builder.Model.GetEntityTypes()
.Select(e => e.Relational())
.ToList()
.ForEach(t => t.TableName = t.TableName.ToLower()
);

// Lower column names
builder.Model.GetEntityTypes()
.SelectMany(e => e.GetProperties())
.ToList()
.ForEach(p => p.Relational().ColumnName = p.Name.ToLower()
);

thanks.

[rowanmiller]

modelBuilder does not seem to be in OnConfiguring but is in OnModelCreating

Typo on my behalf, OnModelCreating is the correct place (I updated my original comment).

[geocine]

How about lowercase of table names?

Got it. Just sharing this here:

var dbSetFinder = this.GetService<IDbSetFinder>();
var setProperties = dbSetFinder.FindSets(this);

// Converts column name mapping of C# PascalCase property to snake_case
// Converts tables name mapping of C# Customers to customers
foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
    foreach (var property in entity.GetProperties())
    {
        property.Relational().ColumnName = property.Name.ToSnakeCase();
    }
    entity.Relational().TableName = setProperties.Where(d => d.ClrType == entity.ClrType).Select(d => d.Name).First().ToLower();
}

If there is a better way let me know

[asymetrixs]

@geocine Your solution uses the full namespace to generate the name which can lead to very long (and ugly) names for tables and keys/indices.

Fortunately Npgsql already offers an SnakeCase-Translator (which it uses for enums) so I solved it like this:
`private void _FixSnakeCaseNames(ModelBuilder modelBuilder)
{
var mapper = new Npgsql.NpgsqlSnakeCaseNameTranslator();

foreach (var entity in modelBuilder.Model.GetEntityTypes())
{
    // modify column names
    foreach (var property in entity.GetProperties())
    {
        property.Relational().ColumnName = mapper.TranslateMemberName(property.Relational().ColumnName);
    }

    // modify table name
    entity.Relational().TableName = mapper.TranslateMemberName(entity.Relational().TableName);

    // move asp_net tables into schema 'identity'
    if (entity.Relational().TableName.StartsWith("asp_net_"))
    {
        entity.Relational().TableName = entity.Relational().TableName.Replace("asp_net_", string.Empty);
        entity.Relational().Schema = "identity";
    }
}

}`

called from here

protected override void OnModelCreating(ModelBuilder modelBuilder) { base.OnModelCreating(modelBuilder); _FixSnakeCaseNames(modelBuilder); }
Moreover I do move the identity-tables into another namespace and remove the asp_net prefix

[geocine]

@asymetrixs I did not know there was a built in function for snake case thank you

For the table name, I am actually using the name of the DbSet when you add it to your context.

Example:

public DbSet<Employee> EmployeeDetails { get; set; }

It will give you employee_details

[FikruKebede]

In my case, modelBuilder.Model is not available. in which name space the Model class is available?

[ajcvickers]

@FikruKebede Are you sure you are using EF Core, not EF6?

[FikruKebede]

I was using EF6. Now I am using EF Core and the problem is fixed.

[neumartin]

Hi!!

Not working in my code, all tables and fields still in CamelCase.
Im using the EF Core 2.0.

My context class is this:

public class SevntDbContext : IdentityDbContext<Usuario, IdentityRoleSevnt, int>, ISevntDbContext
    {
        public SevntDbContext() : base()
        {
            Configure();
        }

        public SevntDbContext(DbContextOptions<SevntDbContext> options) : base(options)
        {
            Configure();
        }

        private void Configure()
        {
            // Set up configuration sources.
            var builder = new ConfigurationBuilder()
                .SetBasePath(Directory.GetCurrentDirectory())
                .AddJsonFile("project.json", optional: true)
                .AddJsonFile("appsettings.json", optional: false);

            builder.AddEnvironmentVariables();
            Configuration = builder.Build();
        }

        public IConfigurationRoot Configuration { get; set; }

        public DbSet<Adjunto> Adjuntos { get; set; }
        public DbSet<CategoriaEvento> CategoriasEvento { get; set; }
        public DbSet<Provincia> Provincias { get; set; }
        public DbSet<Pais> Paises { get; set; }
        public DbSet<Empresa> Empresas { get; set; }
        public DbSet<Usuario> Usuarios { get; set; }
        public DbSet<TipoCondicionFiscal> TiposCondicionesFiscales { get; set; }
        public DbSet<TipoDocumento> TiposDocumento { get; set; }
        public DbSet<TipoEntrada> TipoEntrada { get; set; }
        public DbSet<TipoEstadoCivil> TiposEstadoCivil { get; set; }
        public DbSet<Entrada> Entradas { get; set; }
        public DbSet<Evento> Eventos { get; set; }
        public DbSet<Zona> Zonas { get; set; }
        public DbSet<Pedido> Pedidos { get; set; }
        public DbSet<TipoAlerta> TiposAlerta { get; set; }
        public DbSet<Ingreso> Ingresos { get; set; }
        public DbSet<Persona> Personas { get; set; }
        public DbSet<PersonaVinculo> PersonasVinculos { get; set; }
        public DbSet<PersonaAlertaCargada> PersonasAlertasCargadas { get; set; }
        public DbSet<PersonaAlertaEmitida> PersonasAlertasEmitidas { get; set; }
        public DbSet<TipoUsuario> TiposUsuario { get; set; }
        public DbSet<Horario> Horarios { get; set; }
        public DbSet<DiaSemana> DiasSemana { get; set; }
        public DbSet<BoardingPass> BoardingPasses { get; set; }
        public DbSet<Aerolinea> Aerolineas { get; set; }
        public DbSet<IATAAeropuerto> IATAAeropuertos { get; set; }
        public DbSet<IATAPais> IATAPaises { get; set; }

        protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        {
            optionsBuilder.UseNpgsql(Configuration["ConnectionStrings:ConnectionString"]); //.MigrationsAssembly("pBoxe.DataAccess");                                                                             // optionsBuilder.UseSqlServer("Server=localhost;Database=Sevnt;Trusted_Connection=True;MultipleActiveResultSets=true;"); //.MigrationsAssembly("pBoxe.DataAccess");
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);

            modelBuilder.HasPostgresExtension("citext");

            new AdjuntoMap(modelBuilder.Entity<Adjunto>());
            new CategoriaEventoMap(modelBuilder.Entity<CategoriaEvento>());
            new EmpresaMap(modelBuilder.Entity<Empresa>());
            new EntradaMap(modelBuilder.Entity<Entrada>());
            new EventoMap(modelBuilder.Entity<Evento>());
            new ProvinciaMap(modelBuilder.Entity<Provincia>());
            new PaisMap(modelBuilder.Entity<Pais>());
            new TipoDocumentoMap(modelBuilder.Entity<TipoDocumento>());
            new TipoEntradaMap(modelBuilder.Entity<TipoEntrada>());
            new TipoCondicionFiscalMap(modelBuilder.Entity<TipoCondicionFiscal>());
            new TipoEstadoCivilMap(modelBuilder.Entity<TipoEstadoCivil>());
            new UsuarioMap(modelBuilder.Entity<Usuario>());
            new PedidoMap(modelBuilder.Entity<Pedido>());
            new UsuarioEventoMap(modelBuilder.Entity<UsuarioEvento>());
            new UsuarioEventoFavoritosMap(modelBuilder.Entity<UsuarioEventoFavoritos>());
            new ZonaMap(modelBuilder.Entity<Zona>());
            new TipoAlertaMap(modelBuilder.Entity<TipoAlerta>());
            new IngresoMap(modelBuilder.Entity<Ingreso>());
            new PersonaMap(modelBuilder.Entity<Persona>());
            new PersonaVinculoMap(modelBuilder.Entity<PersonaVinculo>());
            new PersonaAlertaCargadaMap(modelBuilder.Entity<PersonaAlertaCargada>());
            new PersonaAlertaEmitidaMap(modelBuilder.Entity<PersonaAlertaEmitida>());
            new TipoUsuarioMap(modelBuilder.Entity<TipoUsuario>());
            new HorarioMap(modelBuilder.Entity<Horario>());
            new DiaSemanaMap(modelBuilder.Entity<DiaSemana>());
            new BoardingPassMap(modelBuilder.Entity<BoardingPass>());
            new AerolineaMap(modelBuilder.Entity<Aerolinea>());
            new IATAAeropuertoMap(modelBuilder.Entity<IATAAeropuerto>());
            new IATAPaisMap(modelBuilder.Entity<IATAPais>());

            var mapper = new Npgsql.NpgsqlSnakeCaseNameTranslator();

            foreach (var entity in modelBuilder.Model.GetEntityTypes())
            {
                // modify column names
                foreach (var property in entity.GetProperties())
                {
                    property.Relational().ColumnName = mapper.TranslateMemberName(property.Relational().ColumnName);
                }

                // modify table name
                entity.Relational().TableName = mapper.TranslateMemberName(entity.Relational().TableName);

                // move asp_net tables into schema 'identity'
                if (entity.Relational().TableName.StartsWith("asp_net_"))
                {
                    entity.Relational().TableName = entity.Relational().TableName.Replace("asp_net_", string.Empty);
                    entity.Relational().Schema = "identity";
                }
            }
        }

        public void Seed()
        {
            // this.Database.Migrate();
            new Seeder().Seed();
        }
    }

Thanks!!!

[ajcvickers]

@neumartin This code works for me, assuming that NpgsqlSnakeCaseNameTranslator correctly creates a snake_case name. Can you check that NpgsqlSnakeCaseNameTranslator is generating snake_case names? If not, then please follow up with the owner of that class. Otherwise, please file a new issue with a complete project or code listing that reproduces what you are seeing.

[neumartin]

I make an example here:

https://github.com/neumartin/SnakeCaseTest/

But in the example works fine, forget my question.
Thanks!

[neumartin]

The problem was the migrations.
I deleted all migrations and create new one and works fine!

[AuthorProxy]

Previous example not work with keys and indexes, here is modified version that includes it:

using System;
using System.Text.RegularExpressions;
using Microsoft.EntityFrameworkCore;
using Microsoft.EntityFrameworkCore.Metadata;
using Npgsql;

namespace Database.Customization
{
    public class PostgreDbContext : DbContext
    {
        private static readonly Regex _keysRegex = new Regex("^(PK|FK|IX)_", RegexOptions.Compiled);

        public PostgreDbContext(DbContextOptions options)
            : base(options)
        {
        }

        protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            base.OnModelCreating(modelBuilder);
            FixSnakeCaseNames(modelBuilder);
        }

        private void FixSnakeCaseNames(ModelBuilder modelBuilder)
        {
            var mapper = new NpgsqlSnakeCaseNameTranslator();
            foreach (var table in modelBuilder.Model.GetEntityTypes())
            {
                ConvertToSnake(mapper, table);
                foreach (var property in table.GetProperties())
                {
                    ConvertToSnake(mapper, property);
                }

                foreach (var primaryKey in table.GetKeys())
                {
                    ConvertToSnake(mapper, primaryKey);
                }

                foreach (var foreignKey in table.GetForeignKeys())
                {
                    ConvertToSnake(mapper, foreignKey);
                }

                foreach (var indexKey in table.GetIndexes())
                {
                    ConvertToSnake(mapper, indexKey);
                }
            }
        }

        private void ConvertToSnake(INpgsqlNameTranslator mapper, object entity)
        {
            switch (entity)
            {
                case IMutableEntityType table:
                    var relationalTable = table.Relational();
                    relationalTable.TableName = ConvertGeneralToSnake(mapper, relationalTable.TableName);
                    if (relationalTable.TableName.StartsWith("asp_net_"))
                    {
                        relationalTable.TableName = relationalTable.TableName.Replace("asp_net_", string.Empty);
                        relationalTable.Schema = "identity";
                    }

                    break;
                case IMutableProperty property:
                    property.Relational().ColumnName = ConvertGeneralToSnake(mapper, property.Relational().ColumnName);
                    break;
                case IMutableKey primaryKey:
                    primaryKey.Relational().Name = ConvertKeyToSnake(mapper, primaryKey.Relational().Name);
                    break;
                case IMutableForeignKey foreignKey:
                    foreignKey.Relational().Name = ConvertKeyToSnake(mapper, foreignKey.Relational().Name);
                    break;
                case IMutableIndex indexKey:
                    indexKey.Relational().Name = ConvertKeyToSnake(mapper, indexKey.Relational().Name);
                    break;
                default:
                    throw new NotImplementedException("Unexpected type was provided to snake case converter");
            }
        }

        private string ConvertKeyToSnake(INpgsqlNameTranslator mapper, string keyName) =>
            ConvertGeneralToSnake(mapper, _keysRegex.Replace(keyName, match => match.Value.ToLower()));

        private string ConvertGeneralToSnake(INpgsqlNameTranslator mapper, string entityName) =>
            mapper.TranslateMemberName(ModifyNameBeforeConvertion(mapper, entityName));

        protected virtual string ModifyNameBeforeConvertion(INpgsqlNameTranslator mapper, string entityName) => entityName;
    }
}

[okinoh]

@AuthorProxy
Your full example works fine on the first migrations add and database update,
but when I add a table and do migrations add again, I get a migration file which includes CreateTable(s) of all existing tables. That causes relation "mytablename" already exists on the second database update run.
My EF core version: 2.1.4-rtm-31024

Edit:
Forgive me, I found out that it was caused by my Solution & Project directory structure.
I had my .csproj file in the same directory of which .sln is in.

[williamdenton]

update for efcore3, the Relational() methods have been removed in 469177a, small tweaks in here to make the code compile again

property.Relational().ColumnName = ConvertGeneralToSnake(mapper, property.Relational().ColumnName);

changes to

property.SetColumnName(ConvertGeneralToSnake(mapper, property.GetColumnName()));

heres the complete extension

public static class ModelBuilderExtensions
{
	static readonly Regex _keysRegex = new Regex("^(PK|FK|IX)_", RegexOptions.Compiled);

	public static void UseSnakeCaseNames(this ModelBuilder modelBuilder)
	{
		var mapper = new NpgsqlSnakeCaseNameTranslator();

		foreach (var table in modelBuilder.Model.GetEntityTypes())
		{

			ConvertToSnake(mapper, table);

			foreach (var property in table.GetProperties())
			{
				ConvertToSnake(mapper, property);
			}

			foreach (var primaryKey in table.GetKeys())
			{
				ConvertToSnake(mapper, primaryKey);
			}

			foreach (var foreignKey in table.GetForeignKeys())
			{
				ConvertToSnake(mapper, foreignKey);
			}

			foreach (var indexKey in table.GetIndexes())
			{
				ConvertToSnake(mapper, indexKey);
			}
		}
	}

	static void ConvertToSnake(INpgsqlNameTranslator mapper, object entity)
	{
		switch (entity)
		{
			case IMutableEntityType table:
				table.SetTableName(ConvertGeneralToSnake(mapper, table.GetTableName()));
				break;
			case IMutableProperty property:
				property.SetColumnName(ConvertGeneralToSnake(mapper, property.GetColumnName()));
				break;
			case IMutableKey primaryKey:
				primaryKey.SetName(ConvertKeyToSnake(mapper, primaryKey.GetName()));
				break;
			case IMutableForeignKey foreignKey:
				foreignKey.SetConstraintName(ConvertKeyToSnake(mapper, foreignKey.GetConstraintName()));
				break;
			case IMutableIndex indexKey:
				indexKey.SetName(ConvertKeyToSnake(mapper, indexKey.GetName()));
				break;
			default:
				throw new NotImplementedException("Unexpected type was provided to snake case converter");
		}
	}

	static string ConvertKeyToSnake(INpgsqlNameTranslator mapper, string keyName) =>
		ConvertGeneralToSnake(mapper, _keysRegex.Replace(keyName, match => match.Value.ToLower()));

	static string ConvertGeneralToSnake(INpgsqlNameTranslator mapper, string entityName) =>
		mapper.TranslateMemberName(entityName);
}

[roji]

For anyone interesting in this, check out EFCore.NamingConventions. If anything is missing or not working for you, please file an issue in that repo.