.net core entity framework (EF Core) table naming convention

Question

.net core entity framework (EF Core) table naming convention plural to single/simple/underscore

Being a fan of single simple underscore naming convention to table names, I feel uncomfortable with the way EF core is naming tables Plural PascalCase.

Model

public class SourceType {
   ... 

DbContext

public class ApplicationDbContext : DbContext {
    public DbSet<SourceType> SourceTypes { get; set; }
    ...

This creates the table with the name SourceTypes (PascalCase and Plural)

I know I can change the generated table name by using [table('source_type')] in the model class.

But, what I need is a method to do it in global manner.

Answer 1

In EF Core 6, you can use the below extensions on the ModelBuilder:

public static class ModelBuilderExtensions
{
    public static void ApplyNamingConvention(this ModelBuilder modelBuilder)
    {
        var modelEntityTypes = modelBuilder.Model.GetEntityTypes();

        foreach (var tableConfiguration in modelEntityTypes)
        {
            // Table Naming
            tableConfiguration.SetTableName(tableConfiguration.GetTableName().ToLowerUnderscoreName());

            // Column Naming
            var columnsProperties = tableConfiguration.GetProperties();

            foreach (var columnsProperty in columnsProperties)
            {
                var isOwnedProperty = columnsProperty.DeclaringEntityType.IsOwned();

                if (isOwnedProperty && columnsProperty.IsPrimaryKey())
                    continue;

                if (isOwnedProperty)
                {
                    var ownership = columnsProperty.DeclaringEntityType.FindOwnership();
                    var ownershipName = ownership.PrincipalToDependent.Name;
                    var columnName = $"{ownershipName}_{columnsProperty.Name}";
                    columnsProperty.SetColumnName(columnName.ToLowerUnderscoreName());
                }
                else
                {
                    columnsProperty.SetColumnName(columnsProperty.Name.ToLowerUnderscoreName());
                }
            }

            // Find primary key
            var pk = tableConfiguration.FindPrimaryKey();
            pk.SetName(pk.GetName().ToLowerUnderscoreName());

            // Foreign keys
            var fks = tableConfiguration.GetForeignKeys();

            foreach (var fk in fks)
            {
                var fkName = fk.GetConstraintName().ToLowerUnderscoreName();
                fk.SetConstraintName(fkName);
            }

            // Indexes
            var idxs = tableConfiguration.GetIndexes();

            foreach (var idx in idxs)
            {
                idx.SetDatabaseName(idx.GetDatabaseName().ToLowerUnderscoreName());
            }
        }
    }
        
    public static string ToLowerUnderscoreName(this string text)
    {
        if (string.IsNullOrEmpty(text))
        {
            return text;
        }

        var builder = new StringBuilder(text.Length + Math.Min(2, text.Length / 5));
        var previousCategory = default(UnicodeCategory?);

        for (var currentIndex = 0; currentIndex < text.Length; currentIndex++)
        {
            var currentChar = text[currentIndex];

            if (currentChar == '_')
            {
                builder.Append('_');
                previousCategory = null;
                continue;
            }

            var currentCategory = char.GetUnicodeCategory(currentChar);

            switch (currentCategory)
            {
                case UnicodeCategory.UppercaseLetter:
                case UnicodeCategory.TitlecaseLetter:
                    if (previousCategory == UnicodeCategory.SpaceSeparator ||
                            previousCategory == UnicodeCategory.LowercaseLetter ||
                            previousCategory != UnicodeCategory.DecimalDigitNumber &&
                            previousCategory != null &&
                            currentIndex > 0 &&
                            currentIndex + 1 < text.Length &&
                            char.IsLower(text[currentIndex + 1]))
                    {
                        builder.Append('_');
                    }

                    currentChar = char.ToLower(currentChar, CultureInfo.InvariantCulture);
                    break;

                case UnicodeCategory.LowercaseLetter:
                case UnicodeCategory.DecimalDigitNumber:
                    if (previousCategory == UnicodeCategory.SpaceSeparator)
                    {
                        builder.Append('_');
                    }
                    break;

                default:
                    if (previousCategory != null)
                    {
                        previousCategory = UnicodeCategory.SpaceSeparator;
                    }
                    continue;
            }

            builder.Append(currentChar);
            previousCategory = currentCategory;
        }

        return builder.ToString();
    }
}

And in the DbContext, I have:

    protected override void OnModelCreating(ModelBuilder builder)
    {
        builder.ApplyNamingConvention();
    }

Answer 2

TL&DR: The SnakeCase Solution does NOT work for Identity Framework; Manually create them using the method below;

Explanation: The SnakeCase() function works for the majority of issues. However, there are times where this method will not format your database tables correctly. One very popular example is the Identity Framework. In those cases, it's recommended that you manually name the tables;

FIX:

protected override void OnModelCreating(ModelBuilder modelBuilder)
        {
            //Configure default schema
            base.OnModelCreating(modelBuilder);

            modelBuilder.Entity<IdentityUser>().ToTable("asp_net_users");
            modelBuilder.Entity<IdentityUserToken<string>>().ToTable("asp_net_user_tokens");
            modelBuilder.Entity<IdentityUserLogin<string>>().ToTable("asp_net_user_logins");
            modelBuilder.Entity<IdentityUserClaim<string>>().ToTable("asp_net_user_claims");
            modelBuilder.Entity<IdentityRole>().ToTable("asp_net_roles");
            modelBuilder.Entity<IdentityUserRole<string>>().ToTable("asp_net_user_roles");
            modelBuilder.Entity<IdentityRoleClaim<string>>().ToTable("asp_net_role_claims");
}

Answer 3

In short

Extend ModelBuilder with an extension method, do some regex, and call the method in you DbContext

Edited: You can also use this 3rd party library EFCore.NamingConventions

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
      => optionsBuilder
        .UseNpgsql(...)
        .UseSnakeCaseNamingConvention();

In Detail

Create an extension for ModelBuilder class

public static class ModelBuilderExtensions 
{
    public static void SetSimpleUnderscoreTableNameConvention(this ModelBuilder modelBuilder)
    {
        foreach (IMutableEntityType entity in modelBuilder.Model.GetEntityTypes())
        {                        
            Regex underscoreRegex = new Regex(@"((?<=.)[A-Z][a-zA-Z]*)|((?<=[a-zA-Z])\d+)");            
            entity.Relational().TableName = underscoreRegex.Replace(entity.DisplayName(), @"_$1$2").ToLower();
        }
    }
}

Call this method in you DbContext

public class ApplicationDbContext : DbContext
{
    public DbSet<SourceType> SourceTypes { get; set; }
    ...
   
    protected override void OnModelCreating(ModelBuilder builder)
    {
        base.OnModelCreating(builder);
        ...
        builder.SetSimpleUnderscoreTableNameConvention();
    }
}

I hope this will help any developers like me not to waste time searching for the solution. :)

Answer 4

I know the question is old and has been answered, but this NuGet (EFCore.NamingConventions) could be interesting.

This is a NuGet package that handles the naming convention as simple as

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
    => optionsBuilder
        .UseNpgsql(...)
        .UseSnakeCaseNamingConvention();

It also supports:

• UseSnakeCaseNamingConvention: FullName becomes full_name
• UseLowerCaseNamingConvention: FullName becomes fullname
• UseCamelCaseNamingConvention: FullName becomes fullName
• UseUpperCaseNamingConvention: FullName becomes FULLNAME

Answer 5

Faraj's answer not works on ThreeCapitalWords, result is three_capitalwords.

Here is my solution, based on this answer:

    /// <summary>
    ///
    /// </summary>
    /// <param name="preserveAcronyms">If true, 'PrepareXXRecord' converted to 'prepare_xx_record',
    /// otherwise to 'prepare_xxrecord'</param>
    public static void SetSimpleUnderscoreTableNameConvention(this ModelBuilder modelBuilder, bool preserveAcronyms)
    {
        foreach (IMutableEntityType entity in modelBuilder.Model.GetEntityTypes())
        {
            var underscored = AddUndercoresToSentence(entity.DisplayName(), preserveAcronyms);
            entity.Relational().TableName = underscored.ToLower();
        }
    }

    private static string AddUndercoresToSentence(string text, bool preserveAcronyms)
    {
        if (string.IsNullOrWhiteSpace(text))
            return string.Empty;
        var newText = new StringBuilder(text.Length * 2);
        newText.Append(text[0]);
        for (int i = 1; i < text.Length; i++)
        {
            if (char.IsUpper(text[i]))
                if ((text[i - 1] != '_' && !char.IsUpper(text[i - 1])) ||
                    (preserveAcronyms && char.IsUpper(text[i - 1]) &&
                     i < text.Length - 1 && !char.IsUpper(text[i + 1])))
                    newText.Append('_');
            newText.Append(text[i]);
        }
        return newText.ToString();
    }

It also converts acronyms: PrepareXXRecord to prepare_xx_record.