Reverse Engineer: Table/Schema Exclusion

[rowanmiller]

Have the ability to specify tables/schemas to be excluded, useful for excluding AapNet* tables etc.

[lajones]

See also this comment: #5126 (comment)

[Starkie]

Are there any news on the implementation of this functionality?

I think it would be useful, for example, when a table causes an error in the generation of the scaffold. I had this happen in #13528. As a workaround, all tables had to be referenced manually using the --table flag, excluding the two that caused the exception.

[ErikEJ]

Maybe EF Core Power Tools can help you? (Persists the table selection)

[Starkie]

The extension is only available for VS2017, right? I mostly use VS Code and run commands with the CLI (Linux user). 😓
Thanks for the suggestion though.

[ajcvickers]

While there is some value in doing this at the command line (i.e for cross-plat), that value is diminished by the Power Tools, which seem to be the preferred choice of most people doing reverse engineering. This combined with table/schema selection being possible anyway means that we've decided not to implement this.

However, we would not be against a well-written and robust community contribution to implement this.

[Starkie]

After looking up the code, I think that with a bit of guidance, this could be an easy implementation. I'll give it a shot over the weekend.

[Starkie]

I have been working on it over the weekend, with the idea of adding a parameter --exclude-tables. This parameter "flows" alongside the values of --tables.

To propagate these values to the database providers, the IDatabaseModelFactory interface must be modified: the excludeTables parameter must be added to the Create methods. This will be a breaking change for every provider, and I'm a bit worried about that.

https://github.com/aspnet/EntityFrameworkCore/blob/45cbc40aee746e27d69d391dc6457ecfc008a1b4/src/EFCore.Relational/Scaffolding/IDatabaseModelFactory.cs#L37

Could it be implemented as an optional parameter, to avoid the breaking change? Or is it prefered to update the providers to support this feature?

If needed, a PR could be created to better review the change.

[ajcvickers]

@bricelam @smitpatel Is breaking IDatabaseModelFactory the way to go here?

[bricelam]

Yeah, probably. Should add excludeSchemas at the same time.

BUT, I still feel like it’s better to add a command/API to get a list of all the tables and implement exclusion at a higher level. (e.g. via a PowerShell/bash script) The Power Tools already work this way.

[ajcvickers]

Re-opening so we can discuss @bricelam's comments in triage.

[ErikEJ]

Being able to get the table list would be great, in Power Tools I currently have to use a custom way per ADO.NET provider to get the table list.

[Starkie]

Could a list subcommand be added to the database command, that returns the list of tables (and/or schemas)? It could also have an option to return unformatted output, so it might be used as an API by the PowerTools.

The command woud be invoked internally before a scaffolding operation if the --exclude-tables or --exclude-schemas parameters were used.

[ajcvickers]

@Starkie We discussed this in triage and it seems like a reasonable idea. The only caveat is if it is too slow, but if the command specifically gets just table/schema name, and not everything else associated with the table, then it's probably okay.

Also, see #14602 which is about allowing new command functionality without always breaking the interface.

[ErikEJ]

@ajcvickers Is this still open for a reason? (closed-wont-fix) ?
Is this about adding a feature to get a list of all tables/schemas ?

[ajcvickers]

@ErikEJ I believe it is about getting a list, yes. This could potentially be used by tooling to automate more things. I think somebody said that your tooling already does this, but maybe it makes sense to move the code you have into EF?
/cc @bricelam

[bricelam]

We should probably file a new issue for adding a command (and API) to get a list of tables...

[ErikEJ]

@bricelam I would like to have a look at this, but before I begin it raises a number of design questions.

[bricelam]

If you draft a proposal, I can present it to the team in a design meeting.

[kuntalbose]

Hi
Any update ?are you add a option for exclude table?the option If we can write like bellow format its better ,
-- [option] table1,table2,table3....etc

Thankx

[ajcvickers]

@ikuntalb This issue is in the Backlog milestone. This means that it is not going currently scheduled for the next release. We have not finished planning for 5.0, so this may change. However, keep in mind that there are many other high priority features with which it will be competing for resources.

[mm-ryo]

Hi,
How do I ignore views when i run scaffold?
In EF core 3 migration, it will generate table for view. thanks

[ErikEJ]

@robinNode you can specify -table options to include only the tables you want. You can also use EF Core Power Tools, gives you a GUI to select tables/views, and persists your selection.

[florinciubotariu]

Hi.
Unfortunately I couldn't use EF Core Power Tools so I've decided to go on another route. I'll post here my solution, maybe it will help others with the same problem.

Since I'm using SQL Server, I've decorated SqlServerDatabaseModelFactory using Scrutor. Extend the functionality as you like:

public class CustomSqlServerDatabaseModelFactory : IDatabaseModelFactory
 {
   private IDatabaseModelFactory databaseModelFactory;

   private static readonly List<string> ExcludedTables = new List<string>
   {
     "__MigrationHistory",
     "Exceptions"
   };

   public CustomSqlServerDatabaseModelFactory(IDatabaseModelFactory databaseModelFactory)
   {
     this.databaseModelFactory = databaseModelFactory;
   }

   public DatabaseModel Create(string connectionString, DatabaseModelFactoryOptions options)
   {
     var databaseModel = databaseModelFactory.Create(connectionString, options);

     RemoveTables(databaseModel);

     return databaseModel;
   }

   public DatabaseModel Create(DbConnection connection, DatabaseModelFactoryOptions options)
   {
     var databaseModel = databaseModelFactory.Create(connection, options);

     RemoveTables(databaseModel);

     return databaseModel;
   }

   private static void RemoveTables(DatabaseModel databaseModel)
   {
     var tablesToBeRemoved = databaseModel.Tables.Where(x => ExcludedTables.Contains(x.Name)).ToList();

     foreach (var tableToRemove in tablesToBeRemoved)
     {
       databaseModel.Tables.Remove(tableToRemove);
     }
   }
 }

And the implementation of IDesignTimeServices:

public class CustomEFDesignTimeServices : IDesignTimeServices
{
    public void ConfigureDesignTimeServices(IServiceCollection serviceCollection)
    {
      serviceCollection.Decorate<IDatabaseModelFactory, CustomSqlServerDatabaseModelFactory>();
    }
}

I've also created a blog post with more details which can be read here.

[ErikEJ]

@florinciubotariu why were you unable to use EF Core Power Tools?

[florinciubotariu]

I had to implement a custom pluralizer (to be as similar as possible to EF 6) and I saw the EF Core Power Tools didn't pick up my implementation of IDesignTimeServices. If I recall correctly, I had problems with tables ending in Data and Media, even though I was using the EF 6 Pluralizer. Is this a known limitation?

[ErikEJ]

@florinciubotariu You can opt in to use the Legacy pluralizer, should be 100% EF6 compatible.

[ErikEJ]

@florinciubotariu If it is not compatible with the EF6 pluralizer, let me know (via a repro) and I will get it fixed

[florinciubotariu]

@ErikEJ

EF Core 5 preview - checked
Use tables and column names directly from the database - checked
EF 6 Pluralizer - checked

EF 6:

    public virtual DbSet<XItemMedia> XItemMedias { get; set; }
    public virtual DbSet<XTestDrive> XTestDrives { get; set; }
    public virtual DbSet<XCSS> XCSSes { get; set; }

EF Core Power Tools:

    public virtual DbSet<XItemMedium> XItemMedia { get; set; }
    public virtual DbSet<XTestDrife> XTestDrives { get; set; }
    public virtual DbSet<XCSS> XCSSes { get; set; }

last one is ok but when using:

EF Core 5 preview - checked
Pluralize and singularize generated object names - checked
EF 6 Pluralizer - checked

I get:

EF 6:

    public virtual DbSet<XCSS> XCSSes { get; set; }
    public virtual DbSet<XBundlePurchas> XBundlePurchases { get; set; }

EF Core Power Tools

     public virtual DbSet<XCss> XCsses { get; set; }
     public virtual DbSet<XBundlePurcha> XBundlePurchas { get; set; }

I can't use Pluralize and singularize generated object names because I have column names ending in ID in my tables.
I hope that next week I will come with a repro on EF Core Power Tools repository.

[ErikEJ]

In the latest daily, you can combine UseDatabaseNames and Pluralization with EF Core 5. Looking forward to your repro in the EF Power Tools repository

[jcummings2]

Hi Any update ?are you add a option for exclude table?the option If we can write like bellow format its better , -- [option] table1,table2,table3....etc

Thankx

Good question and after reading through all the comments I am still a bit confused on what this issue is trying to do. The title still indicates an exclude option but some of the discussion seems to go in different directions. Based on numerous other issues that have been closed as duplicates, I gather the point may still be adding an exclude option. In any case, I sure hope the exclude option is still the purpose.

[MikeMcWilliams]

Is this feature ready? I need to exclude a table.

[ErikEJ]

It is in EF Core Power Tools CLI, yes. But not in the standard CLI tooling @MikeMcWilliams

[MikeMcWilliams]

It is in EF Core Power Tools CLI, yes. But not in the standard CLI tooling @MikeMcWilliams

Thanks @ErikEJ. That unfortunately is not an option for me, I am using Microsoft.AspNetCore.DataProtection.EntityFrameworkCore in my partial because scaffolding does not include using Microsoft.AspNetCore.DataProtection.EntityFrameworkCore; in its dbcontext output.

I am only looking to skip the DataProtectionKeys table; but I wouldn't need it if the scaffolding detected the DataProtectionKeys table and then added the supporting using statement.

See, I have a partial dbcontext that inherits from IDataProtectionKeyContext which resides in the 'Microsoft.AspNetCore.DataProtection.EntityFrameworkCore' namespace. Since the scaffolding does not seem to support this natively, the DataProtectionKeys table is scaffolded; so I am trying to tell scaffolding to skip DataProtectionKeys.

Is there a way to tell the scaffolding that I'm using DataProtection? This seems like it would be a very common practice for apps behind a load balancer and would be used often.

[ErikEJ]

@MikeMcWilliams How would the naive tooling support help here???

[MaherJendoubi]

Is there any update for this feature?

[ajcvickers]

This issue is in the Backlog milestone. This means that it is not planned for the next release (EF Core 8.0). We will re-assess the backlog following the this release and consider this item at that time. However, keep in mind that there are many other high priority features with which it will be competing for resources. Make sure to vote (👍) for this issue if it is important to you.