Reverse Engineering: Enable pluralization/singularization when scaffolding a model from a database

[abelepereira]

Database scaffolding has no pluralization service and the entity models are generated mapping the exact name of database objects. My suggestion is to enable this pluralization through a DI service that could be overridden at the ef CLI like this:

dnx ef dbcontext scaffold "Server=myserver;Database=Foo;user id=me;password=pwd" EntityFramework.SqlServer --use-pluralization "MyAssembly.PlurarizationService, MyAssembly"

And on Razor templates this could be exposed as:

...
@:namespace @Model.Namespace
@:{
@:    public class  @PluralizationService.Singularize(Model.EntityType.Name)
@:    {
...

I think the default service could be the one shipped with EF6 (System.Data.Entity.Design.PluralizationServices.PluralizationService)

[anpete]

We could consider this inflector (which I wrote a long time ago) - https://github.com/srkirkland/Inflector

It is very lightweight (single class, ~200 loc). MIT license.

[rowanmiller]

EF Team Triage: Pluralization caused a lot of issues in EF6, where even a simple bug fix can break existing models. For that reason we are not going to have pluralization on by default in EF7. That said, we are going to have a simple way to get the model before it is passed to code generators so that you can run any logic over the model to manipulate it. Pluralization would be exactly the kind of thing that should be simple to do (and a good way to verify our work).

This ability was tracked as part of some larger work, but I opened #3077 to track this piece.

[divega]

Reopening this issue to follow up on the discussion we had in triage. We would like to support pluralization for collection navigation properties and singularization of tables names in scaffolding (which is what the original request was about). We need to discuss if this is really part of the potentially more complex extensibility feature described in #3077 or something we have out of the box.

[gdoron]

@divega I think it is a feature almost all EF < 7 users used so it will be a shame not to have it in EF 7.
But it should be an extension method on DbContext whether it should use the pluralization strategy or not.

[divega]

@gdoron this issue is only about using pluralization/singularization in scaffolding, i.e. in the process of reverse engineering a database into an EF Core model.

Past experience has demonstrated that using pluralization to automatically create table names based on the entity types is not a good idea: it means any bug fix in the pluralizer becomes a breaking change for the runtime, so we are not planning to do that. One idea we have talked about is to create a convention that uses the name of the DbSet property on the derived DbContext to name the table. Would that work for you?

[gdoron]

@divega it most definitely would and I think it's the best solution (even if the pluralizer had no bugs at all) because this is the way I use it in the code, I expect it to be the same in the DB.

Even if for some weird reason someone has a class name completely different than the DbSet propery name, e.g. Post class with DbSet Comments. I would still say that the DbSet name should be that table name and in this case the table name should be Comments.

[rowanmiller]

@gdoron stick this code at the start of OnModelCreating and it will give you what you are after 😄

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
    var dbSets = GetType().GetProperties()
        .Where(p => p.PropertyType.Name == "DbSet`1")
        .Select(p => new
        {
            PropertyName = p.Name,
            EntityType = p.PropertyType.GenericTypeArguments.Single()
        })
        .ToArray();

    foreach (var type in modelBuilder.Model.GetEntityTypes())
    {
        var dbset = dbSets.SingleOrDefault(s => s.EntityType == type.ClrType);
        if(dbset != null)
        {
            type.Relational().TableName = dbset.PropertyName;
        }

    }

    // Other configuration code...
}

[gdoron]

Thanks @rowanmiller, in case someone else finds it helpful, I wrapped it in an extension method:

public static void UseDbSetNamesAsTableNames(this DbContext dbContext, ModelBuilder modelBuilder)
{

    var dbSets = dbContext.GetType().GetRuntimeProperties()
        .Where(p => p.PropertyType.Name == "DbSet`1")
        .Select(p => new
                            {
                                PropertyName = p.Name,
                                EntityType = p.PropertyType.GenericTypeArguments.Single()
                            })
        .ToArray();

    foreach (var type in modelBuilder.Model.GetEntityTypes())
    {
        var dbset = dbSets.SingleOrDefault(s => s.EntityType == type.ClrType);
        if (dbset != null)
        {
            type.Relational().TableName = dbset.PropertyName;
        }

    }
}

And used it like this:

protected override void OnModelCreating(ModelBuilder builder)
{
    base.OnModelCreating(builder);
    this.UseDbSetNamesAsTableNames(builder);

But I get an error says cannot find the object "Posts"

I checked what EF is trying to execute with dnx ef migrations script 20160211201857_second -o c:\temp\rename.txt and pasted it here.
EF is using the new name Posts before it was renamed, so that's a bug.

Since it's a just a test application I created for a lecture I will present in the office next week I don't mind using dnx ef database 0 and start all over again.
But out of curiosity what should I do if it is a real production application? fiddle with the __EFMigrationsHistory table?

Thank you very much Rowan, I really like what you did with EF 7 and in ASP.NET 5 in general!

[smitpatel]

Renaming issue is dupe of #3990
Its ordering issue. You can manually edit generated migration to use older name in operations before the rename operation is executed. Sql script is generated from migration file so once migration file is modified it will generate correct script. (i.e. using older names before rename operation is called)

[gdoron]

@rowanmiller Should be noted that Type.GetProperties is not available on dnxcore.
Are there alternatives?

[smitpatel]

@gdoron - try using type.GetRuntimeProperties()

[divega]

@gdoron You should probably use code like what we have in DbSetFinder to accomplish this (or even use our internal type in the extension for now).

Anyway, in the interest of keeping this work item actionable for the scaffolding part, I have created a separate issue for using the DbSet property names for tables names without having to write any extra code: #4540.

Consider creating a new issue if you hit any bugs that aren't covered by existing issues 😄

[rowanmiller]

Talk with @anpete about using a 3rd party pluralizer

[rowanmiller]

Clearing up to discuss whether we want to have pluralization baked into the default experience, or whether we are ok with just having the hook to wire in your own.

[divega]

@maumar @rowanmiller just to make sure, was this closed intentionally or was there something else we wanted to do?

[maumar]

@divega accident I think I goofed something up with that last commit

[rowanmiller]

Discussed and decided that we are ok with just having the hook for the moment. We'd like to avoid taking a dependency on a pluralization service. We may reconsider in the future if we see folks having issues with using a third party service.

[SidShetye]

What's the definitive final guide on singularization of entity classes and pluralization of collections? Documentation is empty (as of now) and across the last 3 years there are multiple offered solutions on stackoverflow and github; esp as EF Core has evolved (e.g. dbsets now take the table names i.e. pluralized)

For some background, we're looking at EF Core 2.1 from EF 6. We don't trust migration wizardry anywhere close to our QA or production databases, so use proper SQL scripts to manage DB schema and then reverse engineering/scaffold the model back into code. IMHO since pluralization of collections and singularization of entity classes is standard practice, the default out-of-box product experience should address it.

[ErikEJ]

@SidShetye "EF Core Power Tools" reverse engineer has pluralization

[SidShetye]

@ErikEJ that looks great. We used IDesignTimeServices and IPluralizer with a custom class that pluralized and singularized strings (i.e. acting as an inflector).

My request is have this on by default; as an out-of-box feature that makes EF Core even better. It's literally a few hours of engineering time but adds so much value for folks following the reverse engineering workflow, that the value/invest fraction should be worth it (obv. the EF Core team knows their constraints best).

Plus there are multiple well-licensed implementations (here and here and even your own one here :) ) to accelerate this.

[bricelam]

We talked about adding one as part of #11160. Not sure what we decided...

See also Pluralization in EF Core.