Reverse engineering a subset of tables for Entity Framework

Question

I'm using EF, and I've got the Entity Framework Power Tools extension, which lets me reverse engineer classes based on tables in a given database.

We have a rather extensive DB, with a lot of tables that I do not need to represent. Is there any simple way to select a subset of these, and reverse engineer only those?

Answer 1

If your database is SQL Server, or SQL Server CE 4.0 then you can use the "Entity Framework Reverse POCO Generator" available at visualstudiogallery.msdn.microsoft.com

It does table filtering through the use of TableFilterExclude, TableFilterInclude.

The way the filters work are as follows:

1. Read the schema
2. Remove any tables that match the exclude filter, if present.
3. Include any tables that match the include filter, if present.

Example:

TableFilterExclude = new Regex("billing|report");
TableFilterInclude = new Regex("company");

Given the following tables:

• some_table
• company
• company_billing_annual
• company_billing_ledger
• company_reports
• company_events
• another_table.

Any table with billing or report in the name are immediately excluded. Any table with company in the name are included.

You are left with:

• company
• company_events

Answer 2

I found a solution which works, though it requires a couple of "extra" steps:

In the project, right click and select Add > new Item. Select Data on the left, and ADO.NET Entity Data Model from the items list.

From the dialog / wizard that shows up now, you can select a DB to generate a model from, and in this case, you can select only the tables, views and stored Procedures and functions you need.

After adding the model, you can expand it in the solution explorer, and you should find the classes you need under YourModelName.edmx --> YourModelName.tt

Note: You won't be able to drag and drop the source-files out, but you can right click and choose copy class. You can then paste them where you need them, and they will appear with a "1" appended to their names. You will therefore need to do some light refactoring (changing the name of the files, the classes, and the namespaces), but it is still fairly straight forward.

Now you can delete the original model (the edmx-file), and everything under it.

This is not a perfect solution, but it`s still easier than having to generate a huge number of table-representations and wait for VS to complete the job, especially if you've got a lot of unnecessary tables, views, etc.

Answer 3

As of now, there is no way to exclude tables out-of-the-box with EF Power Tools. They say this feature request is already in the backlog but is unclear if it's going to make it into the RTM.

I've seen two main approaches to work around this:

1. Tweak the Reverse Engineering templates to ignore all unwanted tables (although I personally find this messy). If you want to get started on what are the moving parts, here's an article from Rowan Miller (http://romiller.com/2012/05/09/customizing-reverse-engineer-code-first-in-the-ef-power-tools/)

2. Create a dummy DB with only the tables you're interested in and let the reverse engineering do its magic. An alternative to this is to have a "special db user" that only has access to the relevant tables and use it when connecting.

Answer 4

An old question with an answer but it's the only one that comes up from google search so I figured I'd post my solution for Entity Framework Power Tools filter a subset of tables.

• After installing VSIX EF Power tools go ahead and right click a solution and select Entity Framework --> Custom Reverse Engineer Templates. A few .tt files will be generated under Sln-root/CodeTemplates/ReverseEngineerCodeFirst, open each one of them.

• Context.tt - find the foreach loop at line 27 and paste code for filtering inside of the foreach loop:

  // START Filter for specific tables only
  string[] filterTables = new string[] { "Motorcycle", "Person" };
  if (!filterTables.Contains(set.ElementType.Name.ToString()))
  {
      continue;   
  }
  // END Filter for specific tables only
  

This will escape generating public DbSet< lines for tables not specified in your filterTables array.

In line 38 inside foreach loop paste the same code as above. This will escape generating modelBuilder.Configurations.Add.

• Entity.tt - find line 6 and paste code which will return empty and escape generating file for tables not in filterTables array:

  // START Filter for specific tables only
  string[] filterTables = new string[] { "Motorcycle", "Person" };
  if (!filterTables.Contains(efHost.EntityType.Name.ToString()))
  {
      return "";  
  }
  // END Filter for specific tables only
  

• Mapping.tt - find line 14 and paste the same code as above:

  // START Filter for specific tables only
  string[] filterTables = new string[] { "Motorcycle", "Person" };
  if (!filterTables.Contains(efHost.EntityType.Name.ToString()))
  {
      return "";  
  }
  // END Filter for specific tables only
  

• Save all .tt files and regenerate the model with "Reverse Engineer Code First".