Skip to content

Reverse Engineering

Jump to bottom
Erik Ejlskov Jensen edited this page Nov 10, 2024 · 298 revisions

Requirements

Reverse engineering requires Visual Studio 2022 (amd64) and the .NET 6 x64 (for EF Core 6 or 7) or .NET 8 x64 (for EF Core 8) runtime.

If you do not use Visual Studio, a cross platform dotnet tool for reverse engineering is available, more information here

Generate entity classes, derived DbContext and mapping for an existing database

EF Core allows you to use the Scaffold-DbContext or the dotnet ef dbcontext scaffold commands to reverse engineer a database and create entity and DbContext classes for your database tables.

This approach has several disadvantages, however:

  • You must add design time components to your project, that you can easily end up deploying to production, thus increasing your app footprint (size) for no reason
  • It is not easy to specify which tables to scaffold, and there is no easy way to generate only entity or DbContext classes.

In addition, the reverse engineering offered in EF Core Power Tools has a many additional options, so you can tailor the generated code.

Reverse Engineer - launch

Select a project in Solution Explorer.

Right click the project and select Add, New Item, Data - (or Ctrl+Shift+A) and pick EF Core Database First Wizard.

Or right click the project and select EF Core Power Tools - Reverse Engineer.

Choose data source

Connect to SQL Server 2012 or later, Azure SQL DB (also MFA login), SQLite, PostgreSQL, MySQL, Firebird or Oracle, a SQL Server Database project or a .dacpac file:

You can remove unwanted database connections from this dropdown by clicking the ➖ button.

To use SQL Server/Azure SQL DB, choose the SQL Server provider in the Add dialog.

To use another DDEX provider than the default SQL Server provider, do as follows: Choose Add => Add Database Connection => Change button, and then pick either PostgreSQL Database or SQLite or Firebird or Oracle ODP.NET

To use PostgreSQL or SQLite install the EF Core Power Pack which includes these DDEX providers: VisualStudio.Data.Sqlite and Npgsql PostgreSQL Integration

To use Oracle, install the Oracle Visual Studio Tools, and make sure to use the ODP.NET Managed driver when connecting to the database.

To use FireBird, install the BlackbirdSql DDEX and SqlEditor for Firebird.

To use MySQL, use a custom connection as described below.

To use a .dacpac, the tool will list any .dacpac files and Database projects found in your solution. You can also choose an additional .dacpac file from disk.

You can also target another EF Core version from this dialog.

Custom connections

You can use the drop-down button to add custom data sources, for MySQL, that does not have a proper or current DDEX provider. The connections created with this dialog are stored securely in Credential Manager

Choose database objects

You can then choose which tables, views (and even SQL Server stored procedures and functions) to include in your model. If you are adding or removing items, make sure to select all the items that you want to be part of your DbContext in this dialog.

You can also exclude individual columns via the checkboxes in this dialog CLI: excludedColumns , and rename tables/views and their columns by pressing the F2 key. For an example of mapping enum values to a column by excluding the base column, see this.

You can also exclude indexes (SQL Server only), for example to work around this EF Core bug - add a list of ExcludedIndexes strings to your table object in efpt.config.json. CLI: excludedIndexes

Notice that any changes you make in this dialog will be persisted in .config.json files in your project.

Choose options

In the options dialog, you can choose

DbContext class and namespaces

  • the name of the DbContext class CLI: names/dbcontext-name code sample
  • the namespace to be used for the generated code CLI: names/dbcontext-namespace code sample
  • optional subfolder path for entity classes in the current project (by default Models) CLI: file-layout/output-path code sample

See this example to get an idea of the customization options.

To generate entity classes in a project folder, and the DbContext in the root folder, see the tip here

To use completely different namespaces for entities and DbContext, see this sample

What to generate

  • whether to generate both DbContext and entity classes, or just one or the other CLI: code-generation/type

Naming of entity classes/DbSet

  • pluralize or singularize generated names (entity class names singular and DbSet names plural) CLI: code-generation/use-inflector code sample
  • use table and column names from the database CLI: code-generation/use-database-names code sample

Other options

  • to use DataAnnotation attributes rather than the fluent API (as much as possible) CLI: code-generation/use-data-annotations code sample
  • to customize code using templates CLI: code-generation/use-t4 (see below)
  • to not include the current connection string in the generated code CLI: code-generation/enable-on-configuring code sample
  • optionally add the relevant EF Core packages to the project code sample

Choose advanced options

Code Generation

  • Use many to many entity - preserve a many to many entity instead of skipping it - see #1148 CLI: code-generation/use-many-to-many-entity
  • Use nullable reference types CLI: code-generation/use-nullable-reference-types
  • Remove SQL default from bool columns CLI: code-generation/remove-defaultsql-from-bool-properties (to avoid them being bool? - see #160
  • Remove all navigations from entity classes (experimental) CLI: code-generation/use-no-navigations-preview
  • Remove Default DbContext constructor (only generated if "Inlcude connection string" is selected in EF Core 7+) - see #995
  • Always include all objects, useful for the Refresh context menu item

File layout

  • Custom sub-namespace for entity classes (by default path will be used) (preview) CLI: names/model-namespace code sample
  • Optional subfolder path for DbContext class in the current project, or even a full path to anywhere on your disk CLI: file-layout/output-dbcontext-path code sample
  • Custom sub-namespace for DbContext class (by default path will be used) CLI: names/dbcontext-namespace code sample
  • Split DbContext into IEntityConfiguration classes in a Configurations folder below the DbContext (preview) CLI: file-layout/split-dbcontext-preview OBSOLETE: the supported way of doing this will be via the new T4 template option, see below
  • Use schema folders for generated entity classes (experimental) CLI: file-layout/use-schema-folders-preview
  • Use schema namespaces for generated entity classes (experimental) CLI: file-layout/use-schema-namespaces-preview

Mapping

  • Map spatial types with SQL Server, PostgreSQL and MySQL CLI: type-mappings/use-spatial More info
  • Map hierarchyid with SQL Server CLI: type-mappings/use-HierarchyId
  • Map DateOnly (date) and TimeOnly (time) with SQL Server CLI: type-mappings/use-DateOnly-TimeOnly
  • Map Noda Time types with PostgreSQL, SQL Server and SQLite CLI: type-mappings/use-NodaTime
  • Use EF6 Pluralizer - by default the tool uses the Humanizer package for pluralization CLI: code-generation/use-legacy-inflector

Click OK, and C# code with the desired options will be generated in the current project.

Saving options and running the second time

After completed code generation, a file named efpt.config.json will be added to the project - this will allow the tool to load the same settings next time you run reverse engineering. You can right click the file, and choose to either re-generate the code, or edit the file.

To always include all objects (including newly added) during Refresh, you can use the advanced option Always include all objects.

For CLI, there is an option to prevent addition of objects after initial discovery: CLI: code-generation/refresh-object-lists

If you are adding or removing tables, leave any additional tables selected on subsequent runs of the tool, or all unused files will be removed. If you explicitly want to keep unselected files, remove the first line with the "autogenerated" comment from each (or rename the file). This is an example of a tool to take an existing classic Entity Framework 6 EDMX file and generate config files from it.

Post processing

If you wish to do some post processing after code generation, you can place a .cmd file named efpt.postrun.cmd in the same folder as the config file, and it will be executed each time you run code generation. It is suggested to use ANSI encoding for the file.

Extending the generated DbContext class and entity classes

All the generated code files are generated a partial classes, which will allow you to extend both the entity classes and the generated DbContext class, with overrides of existing virtual methods on the DbContext and additional, non-mapped properties on you entity classes. In addition, you can customize the generated model further, by implementing the partial OnModelCreatingPartial method. An example of how this can be used is available here. You can also add additional DataAnnotations to the entity classes in a "buddy" metadata class, as described here

Pluralization

The pluralizer will make names of DbSets plural, and names of Entity classes singular. The default pluralizer uses Humanizer for pluralization. It is possible to exclude words (for example Status and Data) from the pluralizer by modifying the efpt.config.json like this:

"UncountableWords": [
      "Status",
      "Data"
   ],

SQL Server stored procedures

The tool can map SQL Server stored procedures, scalar and table valued functions, by selecting them from the list of objects to scaffold. Support for these database object types in .dacpac's is currently in preview.

The stored procedure result mapping is limited to what SET FMTONLY can handle, so not all stored procedures are supported. There is a global option to use sp_describe_first_result_set instead, if that works better for you.

You can also enforce to use sp_describe_first_result_set instead of SET FMTONLY for individual objects (stored procedures or functions), by manually updating the efpt.config.json file:

{
    "Name": "[dbo].[MultiSet]",
    "ObjectType": 1,
    "UseLegacyResultSetDiscovery": true
},

As a workaround, you can add the missing properties in a partial class, that extends the generated empty result set class that is created if a single stored procedure result cannot be discovered.

If you know that your stored procedure does not return a result set, but an empty result set class is still generated, you can add this line to the top of your stored procedure to prevent the empty result set class from being generated:

IF NOT EXISTS(SELECT SESSIONPROPERTY('fmtonly')) 
BEGIN 
   SET FMTONLY OFF; 
   RETURN; 
END

If you use #temp tables in your stored procedure, and that prevents result set discovery, you can use the tip here to "help" expose the result set shape.

If result set discovery still fails, please let me know.

It is also possible to map the result of a stored procedure to a class (DbSet) (or any class with EF Core 8 or later) in your DbContext as follows:

{
    "Name": "[dbo].[Top 10 Customers]",
    "ObjectType": 1,
    "MappedType": "Customer"
},

Support for mapping multiple result sets per procedure is currently in preview and you must opt-in via the global Visual Studio option, see below. This feature depends on Dapper.

Sample usage of the generated code:

using (var db = new NorthwindContext())
{
        var orders = await db.Procedures.CustOrderHistAsync("ALFKI");
        foreach (var order in orders)
        {
            Console.WriteLine($"{order.ProductName}: {order.Total}");
        }

        var outOverallCount = new OutputParameter<int?>();
        var customers = await db.Procedures.SP_GET_TOP_IDSAsync(10, outOverallCount);
        Console.WriteLine($"Db contains {outOverallCount.Value} Customers.");
        foreach (var customer in customers)
        {
            Console.WriteLine(customer.CustomerId);
        }

        // Use scalar functions inline in queries:
        var result = db.Orders.Where(o => o.Reference == NorthWindContext.GetReference(o.OrderName)).ToList();
        
        var tvfTest = db.ProductsWithMinimumInStock(5).OrderBy(p => p.ProductName).ToList();
}

To generate synchronous procedures signatures, you can set this option:

"UseAsyncStoredProcedureCalls": false

By default, to avoid runtime warnings, all decimal properties in the result set classes are decorated with a TypeName attribute, this can be disabled:

"UseDecimalDataAnnotationForSprocResult": false

CLI: code-generation/use-decimal-data-annotation-for-sproc-results

SQL Server Database project (.dacpac) mapping

The tool can derive a model from a .dacpac file, this works very well for tables, but for views and stored procedure results, there may be some missing properties, due to for example computed columns. To work around this, publish the .dacpac to a live database and generate the code from the live database, or manually add the missing properties in a partial class.

The tool includes other features, that makes it easy for you to use a .dacpac file in your workflow:

  • If you have an empty C# project in your solution, the tool can launch the reverse engineeering wizard from the context menu of a Database Project (.sqlproj) - Create EF Core DbContext...

  • To create a database project from your EF Core model, you can do the following:

    • Right click the project with your DbContext, and select the View DbContext DDL SQL menu item
    • This will add a new .sql file to your DbContext project
    • Add a new Database Project (.sqlproj)
    • Right click, and select Import and Script (*.sql) and point to the .sql script generated above.

Customize code using T4 templates

If you choose "Customize code using templates" and choose C# - T4 the tool will add a CodeTemplates folder to your project, that contains an EFCore folder with DbContext.t4 and EntityType.t4 templates. The tool uses them to generate customizable context and entity classes.

This only works for EF Core 8 and later projects.

You can use this extension for a better T4 editing experience.

You can specify a custom location for your CodeTemplates folder in efpt.config.json like this (a relative path to the current project or a full path):

"UseT4": true,
"T4TemplatePath": "..\\..\\",

If you have multiple template folders in your project with the same directory structure as mentioned above, they will all be processed, for example:

/CodeTemplates/EFCore
/Views/CodeTemplates/EFCore
/Services/CodeTemplates/EFCore

For an example of adding Enum mappings, using comments in other places and replacing property names, see the sample templates here

There are links to many other customization samples in this issue

To split your DbContext into EntityTypeConfiguration classes, use the C# - T4 (DbContext split) option. This will create Entity configuration files in a Configurations folder under your DbContext class.

You can also choose to generate "POCO" classes for use with for example micro ORMs, by picking the C# - T4 (POCO) option.

If you have old templates, your will get this warning:

Customize code using Handlebars

If you choose "Customize code using templates" and pick the Handlebars options, the tool will add a CodeTemplates folder to your project, that contains CSharpDbContext and CSharpEntityType folders with Handlebars templates, and tool will use them to generate customizable context and entity classes.

You can supply your own code templates, by placing a CodeTemplates.zip file at the root of your project.

For example, there is a Properties.hbs template in CSharpEntityType/Partials that you can customize to use List instead of ICollection: public List<{{nav-property-type}}> {{nav-property-name}} { get; set; }

Add support for Lazy Loading using Handlebars

You can add support for Lazy Loading using Handlebars templates (which is a new feature in EF Core 2.1, read more here - as you can see, this feature requires that navigation properties to be "virtual" (can be overridden).

In order to add support for Lazy Loading to the generated code, modify Properties.hbs template in CSharpEntityType/Partials like this:

{{spaces 8}}public virtual ICollection<{{nav-property-type}}> {{nav-property-name}} { get; set; }

{{spaces 8}}public virtual {{nav-property-type}} {{nav-property-name}} { get; set; }

Likewise, you can also customize other aspects of the class, such as imports or the constructor, or you can inherit from a particular base class. This feature is based on the EntityFrameworkCore.Scaffolding.Handlebars project from Tony Sneed

Custom renaming with efpt.renaming.json

To customize names of tables, stored procedures, functions and columns, you can place a file named efpt.renaming.json at the root of your project.

(This file will be created for you if you use the UI based renaming feature)

You can also have a renaming file per DbContext in a project, if your rename the file following the convention for multiple DbContexts in the same project described below. Or you can have multiple renaming files in folders with a corresponding efpt.config.json file.

efpt.renaming.json sample file:

[
    {
        "ColumnPatternReplaceWith": "",
        "ColumnRegexPattern": "^(tbl)",
        "SchemaName": "dbo",
        "TablePatternReplaceWith": "",
        "TableRegexPattern": "^(tbl)",
        "Tables": [
            {
                "Columns": [],
                "Name": "tblSIMCard",
                "NewName": "SIMCard"
            }
        ],
        "UseSchemaName": false
    }
]

To preserve casing when using RegEx based renaming, add the following to efpt.config.json

"PreserveCasingWithRegex": true,

For an example of dealing with duplicate table names in different schemas, see this

For an example demonstrating how to override casing and being compatible with some Entity Framework 6 conventions, see this

For an example of a programmatic approach to create the efpt.renaming.json file, see this

For an example of a SQL based approach to create the efpt.renaming.json file see this

You can also do custom replacement of names using regular expressions, see this and this

You can force EF Core 8 to use the legacy, prefix based navigation naming with:

"UsePrefixNavigationNaming": true,

CLI: code-generation/use-prefix-navigation-naming

Using multiple DbContexts in the same project

To generate code for multiple DbContexts in the same project, make sure to use a separate project folder for each DbContext and set of Entity classes, so the files do not overlap and get overwritten.

If you have multiple DbContexts in the same project, the tool supports multiple efpt.config.json files in the project root folder, as long as they follow the "efpt.*.config.json" naming pattern. So you could have two .config.json files named for example efpt.config.json and efpt.Chinook.config.json.

Alternatively, you can have multiple files named efpt.config.json in individual folders in the project.

You can also have a renaming file (efpt.renaming.json) per DbContext in a project, if your rename the file following the same convention (or lives in the same folder as the corresponding efpt.config.json file.

Once you have multiple efpt.*.config.json files in your project, you will be prompted to select the file you want to use when you start the reverse engineer process. You can also right click the file and launch the reverse engineer tool directly from the context menu.

Advanced Visual Studio options

In addition to the options mentioned above, there are a couple of "global" options available, that affects the behaviour of the reverse engineering process. Reach these via the Options menu item or via Tools, Options.

  • Open Generated DbContext: Set this to false if you do not want the genrated DbContext to open after reverse engineering.
  • Run Cleanup of Obsolete files: Set this to false if you do not want to have the tool to remove files that are no longer in use. CLI: code-generation/soft-delete-obsolete-files

Note that only files with this exact content on first line are removed:

// <auto-generated> This file has been auto generated by EF Core Power Tools. </auto-generated>
  • Discover multiple result sets from SQL stored procedures (preview) CLI: code-generation/discover-multiple-stored-procedure-resultsets-preview
  • Use alternate result set discovery - use sp_describe_first_result_set to retrieve stored procedure result sets CLI: code-generation/use-alternate-stored-procedure-resultset-discovery
  • Merge .dacpac files that depend on each other into a single file CLI: code-generation/merge-dacpacs
Clone this wiki locally