Reverse Engineering Quick Start
This guide shows the basic, simple steps of generating a DbContext and entity classes for a SQL Server database. For detailed instructions see the wiki
Install EF Core Power Tools via the Extensions/Manage Extensions menu inside Visual Studio.
Right click a project in Solution Explorer, select EF Core Power Tools/Reverse Engineer
Connect to your existing database via the Add button in the top right corner:
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, 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.
Notice that any changes you make in this dialog will be persisted in .config.json files in your project.
In the options dialog, you can choose
DbContext class and namespaces
- the name of the DbContext class
- the namespace to be used for the generated code
- custom sub-namespace for entity classes (by default path will be used)
- custom sub-namespace for DbContext class (by default path will be used)
Optional custom path(s) for generated files
- optional subfolder path for entity classes in the current project (for example Model)
- optional subfolder path for DbContext class in the current project, or even a full path to anywhere on your disk
See this example to get an idea of the customization options. To generate POCO classes in a project folder, and the DbContext in the root folder, see the tip here
What to generate
- whether to generate both DbContext and entity (POCO) classes, or just one or the other
Naming of POCO classes/DbSet
- pluralize or singularize generated names (POCO class names singular and DbSet names plural)
- use table and column names from the database
Other options
- to use DataAnnotation attributes rather than the fluent API (as much as possible)
- to customize code using Handlebars templates (see below)
- to not include the current connection string in the generated code
- optionally add the relevant EF Core packages to the project
- Code Generation
- Remove SQL default from bool columns (to avoid them being bool? see #160
- Use nullable reference types
- Always include all objects, useful for the Refresh context menu item
- File layout
- Split DbContext into IEntityConfiguration classes in a Configurations folder below the DbContext
- Use schema folders for generated POCO classes
- Mapping
- Use EF6 Pluralizer - by default the tool uses the Humanizer package for pluralization
- Map spatial types with SQL Server, PostgreSQL and MySQL More info
- Map Noda Time types with PostgreSQL, SQL Server and SQLite
- POCO entities
Click OK, and C# code with the desired options will be generated in the current project.
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.
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.
If you wish to do some post processing, you can place a file named efpt.postrun.cmd in the same folder as the config file, and it will be exectuted.
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
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
},If result set discovery still fails, please let me know.
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.
It is also possible to map the result of a stored procedure to a class 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.GetProcedures().CustOrderHistAsync("ALFKI");
foreach (var order in orders)
Console.WriteLine($"{order.ProductName}: {order.Total}");
var outOverallCount = new OutputParameter<int?>();
var customers = await db.GetProcedures().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();
}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.
If you choose "Customize code using Handlebars templates" 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
To customize names of DbSets and entities/properties, 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
}
]For an example of dealing with duplicate table names in different schemas, see this
For an example of a programmatic approach to create the efpt.renaming.json file, see this
For an exmaple 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
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.
In addition to the options mentioned above, there are a couple of "global" options available, that affects the behaviour of the reverse engineering process:
- 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.
- Save Connection Name: Set this to false if you want to avoid persisting the database connection name in the efpt.config.json file
- Discover multiple result sets from SQL stored procedures (preview)
- Use sp_describe_first_result_set only for stored procedure result set discovery
- Merge .dacpac files that depend on each other into a single file
- Prefer async calls (for stored procedure code generation)