EntityFramework Core database first approach pluralizing table names

Question

We have existing database with pluralized table names. For Example Documents. I am trying to use new EF Core and Asp.Net Core with database first approach based on this article here

I run the following command to create models from the existing database

Scaffold-DbContext "Server=(local);Database=MyDatabase;Trusted_Connection=True;"Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

However when I run scaffolding command it creates models with plural names. For example Documents table converted to model name Documents.

How do I change this so it can use singular naming convention for model? (I cannot alter table names in database)

Note that I have read few post on SO related to same issue but they mostly concerned with code first approach. I am using database first approach.

Crabwise

What happens when you rename the model? – Colly

@PetreTurcu when you scaffold again the renamed model still exists, but a new class is also generated with your 'old' database table name. – Eluvium

This did the trick: bricelam.net/2018/03/02/efcore-pluralization.html – Slifka

Answer 1

The EF Core tools do not currently include options to customise the output of reverse engineering an existing database in the way that you describe. Currently, your only realistic option is to modify the generated code.

Huxham

For years we have been defining pluralized tables and EF would generate singular entities, which made sense while developing. Why this drastic change? now I have to write var doc = new Documents() even though its single entity – Crabwise

EF Core's convention is to use a DbSet name for a table if one exists, and if not, it uses the entity's class name. There is no pluralization service in Core. You could say that the reveng tooling is consistent in reverse. If you want to suggest a change in the current behaviour (or a config option for the CLI tools) you should consider creating an issue at the EF Core Github repo – Huxham

They do support this now, the only thing you have to do is implement a hook and a inflector: https://mcmap.net/q/281550/-entityframework-core-database-first-approach-pluralizing-table-names – Eluvium

Answer 2

In Entity Framework Core v2 they introduced a pluralizer hook. Where you can pluralize or singularize your objects yourself.

You can do it by adding this to your project:

public class MyDesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection services)
    {
        services.AddSingleton<IPluralizer, MyPluralizer>();
    }
}

public class MyPluralizer : IPluralizer
{
    public string Pluralize(string name)
    {
        return Inflector.Inflector.Pluralize(name) ?? name;
    }

    public string Singularize(string name)
    {
        return Inflector.Inflector.Singularize(name) ?? name;
    }
}

More info: What's new in EF core 2

You can use this class as your Inflector:

using System.Collections.Generic;
using System.Text.RegularExpressions;

namespace DMP.Generator
{
    public static class Inflector
    {
        #region Default Rules

        static Inflector()
        {
            AddPlural("$", "s");
            AddPlural("s$", "s");
            AddPlural("(ax|test)is$", "$1es");
            AddPlural("(octop|vir|alumn|fung)us$", "$1i");
            AddPlural("(alias|status)$", "$1es");
            AddPlural("(bu)s$", "$1ses");
            AddPlural("(buffal|tomat|volcan)o$", "$1oes");
            AddPlural("([ti])um$", "$1a");
            AddPlural("sis$", "ses");
            AddPlural("(?:([^f])fe|([lr])f)$", "$1$2ves");
            AddPlural("(hive)$", "$1s");
            AddPlural("([^aeiouy]|qu)y$", "$1ies");
            AddPlural("(x|ch|ss|sh)$", "$1es");
            AddPlural("(matr|vert|ind)ix|ex$", "$1ices");
            AddPlural("([m|l])ouse$", "$1ice");
            AddPlural("^(ox)$", "$1en");
            AddPlural("(quiz)$", "$1zes");

            AddSingular("s$", "");
            AddSingular("(n)ews$", "$1ews");
            AddSingular("([ti])a$", "$1um");
            AddSingular("((a)naly|(b)a|(d)iagno|(p)arenthe|(p)rogno|(s)ynop|(t)he)ses$", "$1$2sis");
            AddSingular("(^analy)ses$", "$1sis");
            AddSingular("([^f])ves$", "$1fe");
            AddSingular("(hive)s$", "$1");
            AddSingular("(tive)s$", "$1");
            AddSingular("([lr])ves$", "$1f");
            AddSingular("([^aeiouy]|qu)ies$", "$1y");
            AddSingular("(s)eries$", "$1eries");
            AddSingular("(m)ovies$", "$1ovie");
            AddSingular("(x|ch|ss|sh)es$", "$1");
            AddSingular("([m|l])ice$", "$1ouse");
            AddSingular("(bus)es$", "$1");
            AddSingular("(o)es$", "$1");
            AddSingular("(shoe)s$", "$1");
            AddSingular("(cris|ax|test)es$", "$1is");
            AddSingular("(octop|vir|alumn|fung)i$", "$1us");
            AddSingular("(alias|status)$", "$1");
            AddSingular("(alias|status)es$", "$1");
            AddSingular("^(ox)en", "$1");
            AddSingular("(vert|ind)ices$", "$1ex");
            AddSingular("(matr)ices$", "$1ix");
            AddSingular("(quiz)zes$", "$1");

            AddIrregular("person", "people");
            AddIrregular("man", "men");
            AddIrregular("child", "children");
            AddIrregular("sex", "sexes");
            AddIrregular("move", "moves");
            AddIrregular("goose", "geese");
            AddIrregular("alumna", "alumnae");

            AddUncountable("equipment");
            AddUncountable("information");
            AddUncountable("rice");
            AddUncountable("money");
            AddUncountable("species");
            AddUncountable("series");
            AddUncountable("fish");
            AddUncountable("sheep");
            AddUncountable("deer");
            AddUncountable("aircraft");
        }

        #endregion

        private class Rule
        {
            private readonly Regex _regex;
            private readonly string _replacement;

            public Rule(string pattern, string replacement)
            {
                _regex = new Regex(pattern, RegexOptions.IgnoreCase);
                _replacement = replacement;
            }

            public string Apply(string word)
            {
                if (!_regex.IsMatch(word))
                {
                    return null;
                }

                return _regex.Replace(word, _replacement);
            }
        }

        private static void AddIrregular(string singular, string plural)
        {
            AddPlural("(" + singular[0] + ")" + singular.Substring(1) + "$", "$1" + plural.Substring(1));
            AddSingular("(" + plural[0] + ")" + plural.Substring(1) + "$", "$1" + singular.Substring(1));
        }

        private static void AddUncountable(string word)
        {
            _uncountables.Add(word.ToLower());
        }

        private static void AddPlural(string rule, string replacement)
        {
            _plurals.Add(new Rule(rule, replacement));
        }

        private static void AddSingular(string rule, string replacement)
        {
            _singulars.Add(new Rule(rule, replacement));
        }

        private static readonly List<Rule> _plurals = new List<Rule>();
        private static readonly List<Rule> _singulars = new List<Rule>();
        private static readonly List<string> _uncountables = new List<string>();

        public static string Pluralize(this string word)
        {
            return ApplyRules(_plurals, word);
        }

        public static string Singularize(this string word)
        {
            return ApplyRules(_singulars, word);
        }

#if NET45 || NETFX_CORE
        [MethodImpl(MethodImplOptions.AggressiveInlining)]
#endif
        private static string ApplyRules(List<Rule> rules, string word)
        {
            string result = word;

            if (!_uncountables.Contains(word.ToLower()))
            {
                for (int i = rules.Count - 1; i >= 0; i--)
                {
                    if ((result = rules[i].Apply(word)) != null)
                    {
                        break;
                    }
                }
            }

            return result;
        }

        public static string Titleize(this string word)
        {
            return Regex.Replace(Humanize(Underscore(word)), @"\b([a-z])",
                                 delegate (Match match)
                                 {
                                     return match.Captures[0].Value.ToUpper();
                                 });
        }

        public static string Humanize(this string lowercaseAndUnderscoredWord)
        {
            return Capitalize(Regex.Replace(lowercaseAndUnderscoredWord, @"_", " "));
        }

        public static string Pascalize(this string lowercaseAndUnderscoredWord)
        {
            return Regex.Replace(lowercaseAndUnderscoredWord, "(?:^|_)(.)",
                                 delegate (Match match)
                                 {
                                     return match.Groups[1].Value.ToUpper();
                                 });
        }

        public static string Camelize(this string lowercaseAndUnderscoredWord)
        {
            return Uncapitalize(Pascalize(lowercaseAndUnderscoredWord));
        }

        public static string Underscore(this string pascalCasedWord)
        {
            return Regex.Replace(
                Regex.Replace(
                    Regex.Replace(pascalCasedWord, @"([A-Z]+)([A-Z][a-z])", "$1_$2"), @"([a-z\d])([A-Z])",
                    "$1_$2"), @"[-\s]", "_").ToLower();
        }

        public static string Capitalize(this string word)
        {
            return word.Substring(0, 1).ToUpper() + word.Substring(1).ToLower();
        }

        public static string Uncapitalize(this string word)
        {
            return word.Substring(0, 1).ToLower() + word.Substring(1);
        }

        public static string Ordinalize(this string numberString)
        {
            return Ordanize(int.Parse(numberString), numberString);
        }

        public static string Ordinalize(this int number)
        {
            return Ordanize(number, number.ToString());
        }

#if NET45 || NETFX_CORE
        [MethodImpl(MethodImplOptions.AggressiveInlining)]
#endif
        private static string Ordanize(int number, string numberString)
        {
            int nMod100 = number % 100;

            if (nMod100 >= 11 && nMod100 <= 13)
            {
                return numberString + "th";
            }

            switch (number % 10)
            {
                case 1:
                    return numberString + "st";
                case 2:
                    return numberString + "nd";
                case 3:
                    return numberString + "rd";
                default:
                    return numberString + "th";
            }
        }


        public static string Dasherize(this string underscoredWord)
        {
            return underscoredWord.Replace('_', '-');
        }
    }
}

The inflector class can be found here

For scaffolding I use the dotnet ef command.

The following NuGet packages are included in my project:

  <ItemGroup>
    <PackageReference Include="Microsoft.EntityFrameworkCore" Version="2.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Design" Version="2.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.SqlServer" Version="2.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.1" />
    <PackageReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.1" />
    <PackageReference Include="Microsoft.VisualStudio.Web.CodeGeneration.Tools" Version="2.0.1" />
  </ItemGroup>
  <ItemGroup>
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools" Version="2.0.1" />
    <DotNetCliToolReference Include="Microsoft.EntityFrameworkCore.Tools.DotNet" Version="2.0.1" />
  </ItemGroup>

Eluvium

This hook is only used to singularize entity type names and pluralize DbSet names. It doesn't actually do anything to the table names as explain here github.com/aspnet/EntityFramework.Docs/blob/master/…. But thanks for the code above! It opened my world to EF Core 2.0 a bit more. – Heptastich

I have a strange error here : "A new guard page for the stack cannot be created" – Lallans

I was struggling getting this to work, but it seems like running scaffold-dbcontext in the package manager console doesn't invoke my MyDesignTimeServices class. Once I copied the ItemGroup stuff for the .csproj and ran it via cmd line, everything worked. Thanks! – Gastrology

Answer 3

You can use Bricelam.EntityFrameworkCore.Pluralizer. To use the package, simply install it. The pluralizer will be used when reverse engineering a model from an existing database.

// Tools > NuGet Package manager > Package Manager Console:
PM> Install-Package Bricelam.EntityFrameworkCore.Pluralizer

Then run your Scaffold-DbContext ... commands. Thats it!

Scaffold-DbContext "ConnectionStr..." Microsoft.EntityFrameworkCore.SqlServer -OutputDir Models

Circumscissile

Works great! What is still unsolved for me though is, that where they exist more than one capital letters in a table name (e.g. GPSSettings) it generates all except the first letters lowercase (e.g. Gpssettings.cs) – Arcadia

I get the error "Could not load file or assembly 'Bricelam.EntityFrameworkCore.Pluralizer, Culture=neutral, PublicKeyToken=null'. The system cannot find the file specified." When using this... Why on earth is this not built in to Entity Framework Core? It just leads me to think Core is still not close to being ready for real applications yet. It seems such a backward step from .Net Framework 4.7 – Lest

I like that it's not baked in to EF Core and is extensible, but I agree it's ridiculous that the same algorithm that was baked into EF6 was not provided as a Microsoft.EntityFrameworkCore.Pluralizer NuGet package. Currently migrating a .NET FW EF6 project to .NET Core 2.2 and EF Core and the models are generated differently causing tons of errors in the client code that consumes them. MS literally left us hung out to dry on this one. :-( If I'm missing something, someone please provide a link and make me eat my words. – Bosk

Answer 4

Reverse engineering does not currently singularize (is that a word?) table names for entity types, or pluralize them for navigation properties. This is issue #3060. Until then you'll have to manually change them to what you want after generating the code.

Bast

I guess one could create a script to do that based on an existing dictionary of word endings, that one could run after the model generation until it is handled by the framework – Publius

Oh! It is interesting that you didn't mention your own package! I saw your name after i posted my answer below ;D – Circumscissile

Answer 5

Incredible but true.

After reading the documentation for the Scaffold-DbContext, I have found that there's a parameter that says:

-NoPluralize --- Don't use the pluralizer. Added in EF Core 5.0.

I decided to give it a try with my scaffolding command this way in order to pluralize the entity names:

PM> Scaffold-DbContext -Project "MyProject.Data" -StartupProject "MyProject.Data" "Server=.;Database=myDB;Trusted_Connection=True;Integrated Security=true;MultipleActiveResultSets=true;" -Provider Microsoft.EntityFrameworkCore.SqlServer -Context MyDbContext -ContextDir . -OutputDir Entities -Force -NoPluralize

And voila! It worked.

The weird thing is that sounds like the parameter suppose to do the opposite, so I'm kind of confused. However, this solves the issue.

Heatstroke

For those using the CLI, the option is --no-pluralize – Smackdab