In this article, we are showing how to practically implement the “Database First” approach in Entity Framework Core 7 using GUI tools EFCorePowerTools. EF7 model can be “reverse engineered” from the database using EFCorePowerTools.
1. Introduction
Entity Framework Core is giving preference to “Code First” approach and a bit neglects the “Database First” approach, which resulted, among other things, that the GUI interface for “Database First” in Visual Studio is not officially implemented (as of May 2023). Users are officially pointed to the use of Command Line (CLI) for “reverse engineering” database schema into C# Entity classes. Usage of Command Line commands is always difficult to remember and non-intuitive compared to GUI usage.
However, Open Source Visual Studio Extension EFCorePowerTools is available that provides GUI tool that is not officially supplied. It is very usable and appears to have even more functionality than the “official” command line “CLI EF Core tools”.
This article aims to outline practical steps to “reverse engineer” a database and create EF7 entity classes for usage in serious applications.
1.1. Importance of the “Database First” approach
Many information systems, particularly banking, are “data-centered” where the database plays the central role. Applications are organized around databases, which are the center of the universe in such organizations. Changes to databases are often done by independent Database Analysts or Business Analysts and applications need to cope with changes to database schemas and adapt their Data Access Layer (DAL) to changes done to the database by other actors. Typically, there are several applications in use, some legacy, using ADO.NET technology or similar. Talking about the “Code First” approach in such a situation is not realistic. The only possible approach is “Database First” for the application that uses EF/.NET and also other applications around the database need to apply their own analogous “Database First” steps, like recreating the model for ADO.NET, etc. Therefore, it is a big disappointment that the Microsoft Entity Framework team so rudely neglected the “Database First” approach in EF7 Core and pushed it to the command line (CLI). However, unofficial GUI EFCorePowerTools tools are available.
1.2. EFCorePowerTools
In this article, we will investigate EFCorePowerTools [3], which is a GUI tool to implement the “Database First” approach for EF7 Core. That is not an “official” Microsoft tool, but a “community open source”. It is in my opinion a highly usable tool and offers even support for Stored Procedures, that are not supported by the “official” command line “CLI EF Core tools” at the time of writing this article (May 2023).
1.3. CLI EF Core Tools
Microsoft is providing “official” command line “CLI EF Core tools” for the “Database First” approach. They are not the subject of this article. You can find them described in my article [6].
2. Sample Project
2.1. Sample Console .NET7 Application
We created a sample Console .NET7 application which we will use. Please use NuGet package manager to add the following packages (dependencies) to the application, as in the screenshot:
Microsoft.EntityFrameworkCore.DesignMicrosoft.EntityFrameworkCore.SqlServer
2.2. Sample SqlServer Database
We will be using a sample SqlServer database Northwind. Since there are many database objects, we will focus on only one
- table “
Customers”, - view “
Invoices”, - stored procedure “
CustOrdersOrders”
They are outlined in the screenshots. We will just check how “reverse engineering” went for those objects.
3. Installing EFCorePowerTools
EFCorePowerTools are available as a free Visual Studio extension:
4. Reverse Engineering Entities (Scaffolding)
4.1. Database First – Creating Model
Now is the time to do actual work. Below are screenshots showing how to create EF model. I like my entity names to be as similar to table names as possible, so that is the reason for some flags used. Also, I do not want a database connection string embedded into the code, I want to use a configuration file and load it from there. Also, I want my model to be in a separate folder named NorthwindDB.
And here is the generated model in our application:
Note that there are created entities for 1) table “Customers”, 2) view “Invoices” (green).
For support for 3) stored procedure “CustOrdersOrders”, several classes have been generated (orange).
Also, the database context file NorthwindContext has been generated (yellow).
4.2. Support for Stored Procedures
EFCorePowerTools provide support for Stored Procedures and that is missing in the “official” command line “CLI EF Core tools” at the time of writing this article (May 2023). There are articles on some forums claiming that some people saw some limitations of the ability of EFCorePowerTools to support Stored Procedures in some cases, like when a Stored Procedure invokes another stored procedure. That is why it is sad that the “Database First” approach is not officially fully supported by Microsoft. They were fully supported in EF6 design GUI tools (From .NET 4.8 Framework).
It is a bit of a problem if one migrates to EF7 Core, and finds that only available “community open source” tools have bugs from time to time.
For example, in my production system on SqlServer, I have maybe 300 stored procedures and other guys from other teams sometimes change some stored procedures, and if tooling is not helping me with those changes, I need to manually detect them or wait for bugs to be reported to get notified that something changed.
4.3 Alternative command for model creation – preferring attributes
Here is an alternative command for Reverse Engineering (Scaffolding) entities, if you prefer your entity classes to be configured using attributes (as opposed to the preferred fluent API which is the default)
5. Reading Configuration Files
To make the application more professional, we will place the database connection string into the config file appsettings.json and create the factory method to create DbContext. We do not want to make changes to NorthwindContext class, since changes will be lost if EF model is regenerated.
{
"ConnectionStrings": {
"NorthwindConnection": "Data Source=.;User Id=sa;
Password=dbadmin1!;Initial Catalog=Northwind;Encrypt=False",
}
}
You will need to install more packages from NuGet package manager:
internal class NorthwindContextFactory : IDesignTimeDbContextFactory<NorthwindContext>
{
static NorthwindContextFactory()
{
IConfiguration config = new ConfigurationBuilder()
.SetBasePath(Directory.GetCurrentDirectory())
.AddJsonFile("appsettings.json", true, true)
.Build();
connectionString = config["ConnectionStrings:NorthwindConnection"];
Console.WriteLine("ConnectionString:"+connectionString);
}
static string? connectionString = null;
public NorthwindContext CreateDbContext(string[] args)
{
var optionsBuilder = new DbContextOptionsBuilder<NorthwindContext>();
optionsBuilder.UseSqlServer(connectionString);
return new NorthwindContext(optionsBuilder.Options);
}
}
Here is a look at the application now:
6. Testing Application
We will create some code to test our EF-generated model. Here is the test code:
using Example2.NorthwindDB;
using Example2;
Console.WriteLine("Hello from Example2");
using NorthwindContext ctx =
new NorthwindContextFactory().CreateDbContext(new string[0]);
Console.WriteLine("Table Customers ==================================");
var tableCustomers = ctx.Customers.Where(p => p.Country == "Germany");
foreach (var customer in tableCustomers)
{
Console.WriteLine("Customer Name: " + customer.ContactName);
}
Console.WriteLine("View Invoices ==================================");
var viewInvoices = ctx.Invoices.Where(p => p.ShipCity == "Graz");
foreach (var invoice in viewInvoices)
{
Console.WriteLine("ShipName: " + invoice.ShipName);
}
Console.WriteLine
("Stored Procedure CustOrdersOrders ==================================");
List<CustOrdersOrdersResult>? custOrders =
await ctx.GetProcedures().CustOrdersOrdersAsync("ALFKI");
foreach (var custOrder in custOrders)
{
Console.WriteLine("OrderID: " + custOrder.OrderID);
}
And here is the execution result:
7. Changes to the Database
So, what if you change your database schema? You need to regenerate EF model and the above process will overwrite existing classes. That is the reason you cannot put any code into generated classes since the new EF model generation will erase your changes. You can and need to, however, exploit the fact that those classes are created as partial so you can extend generated classes with custom partial classes.
8. Conclusion
We showed how “Database First” EF7 model classes generation works by using GUI tool EFCorePowerTools.
It looks like “community open source” EFCorePowerTools are more powerful than “official Microsoft” command line tools “CLI EF Core tools”, not just because of GUI interface, but they also offer support for Stored Procedures, which command line tools do not have at the time of writing this article (May 2023).
It is interesting to read at [5] comment from Arthur Vickers, Engineering Manager for .NET Data and Entity Framework at Microsoft that:
- “Visual tool such as the Model Browser - this is something we have no plans to implement.”
- “…visual tools (especially those in Visual Studio) are very expensive to both build and maintain… not sure the ROI would be worth it…”
So, Microsoft does not have the intention to implement GUI “Database First” EF7 Core tools and is satisfied that the “open source community” is providing one. Sadly, “official Microsoft” command line tools “CLI EF Core tools” even do not support Stored Procedures, so we need to rely on “community open source” EFCorePowerTools for such support.
What is coming to my mind is “what if” scenarios, that is “what if” authors of “community open source” EFCorePowerTools get tired of maintaining/developing that tool, and Microsoft upgrades EF Core to version 8, 9, and so on. Users will find themselves without proper tools to work with. That is why I would like to have “official Microsoft” maintained tools available, with all the options, including support for Stored Procedures, be it GUI or command line tools.
9. References
10. History
- 26th May, 2023: Initial version
