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: https://github.com/aspnet/EntityFramework/issues/5126#issuecomment-213009189

[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). :sweat: 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.

[ErikEJ]

The purpose of this feature is to provide a list of available tables and views in the specified database. This list can be used by tools to help the user pick what tables/views to scaffold.

Powershell: 

 Get-Database "Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer

dotnet ef:

 dotnet ef database list "Server=(localdb)\mssqllocaldb;Database=Blogging;Trusted_Connection=True;" Microsoft.EntityFrameworkCore.SqlServer

The list can be provided in two ways (one must be chosen for implementation)

1: The command calls the current Create() method on the relevant IDatabaseModelFactory implementation, and gets the DatabaseModel back, and can produce the list from it.

2: A new, more lightweight method is added to the IDatabaseModelFactory, and must be implemented by each provider. This lightweight method returns A: either a "light" DatabaseModel with just the needed information, B: a new structcture just for this purpose C: a modified version of DatabaseModel with a new HasPrimaryKey bool property on each Table object.

(I vote for 1, despite some performance concerns)

The result should be a list of: schemaName, tableName, hasPrimaryKey (bool)

[ErikEJ]

@bricelam Any news?

[bricelam]

Still on my TODO list. Been busy locking down 3.0

[loganmarshall1]

Can I also suggest, in addition to exclude schema, tables that we add columns ?

For Us, We add prefix to columns/tables 'zz' for ones we're eventually deprecating but we still want to keep the data in the db. Would be nice to not pull these into our vs projects to force devs to use new columns. I think the old Reverse Poco tt generator for .NET 4.7 had regex include/exclude.

Is it easier & more robust to users to add --excludeTableRegex foo --excludeSchemaRegex foo2 --excludeColumnRegex foo3 ?

[bricelam]

@ErikEJ I discussed this with the team, and we like the idea of re-using the existing DatabaseModel and IDatabaseModelFactory. We should also add a flag or something to DatabaseModelFactoryOptions to let providers know we only need the schema and names of objects so they can optimize for perf if they need to.

[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?