Post
Ask Question

Entity Framework 7 – Database First – Using EFCorePowerTools

This article shows 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.

Introduction

Entity Framework Core gives preference to the “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, which provides a 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.

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 rudely neglected the “Database First” approach in EF7 Core and pushed it to the command line (CLI). However, unofficial GUI EFCorePowerTools tools are available.

EFCorePowerTools

In this article, we will investigate EFCorePowerTools [3], a GUI tool to implement the “Database First” approach for EF7 Core. That is not an “official” Microsoft tool but a “community open source”. In my opinion, 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).

CLI EF Core tools

Microsoft is providing an “official” command line, “CLI EF Core tools” for the “Database First” approach. They are not the subject of this article.

Sample Project


Sample Console .NET7 application

We created a sample Console .NET 7 application which we will use. Please use the NuGet package manager to add the following packages (dependencies) to the application, as in the screenshot:

  • Microsoft.EntityFrameworkCore.Design
  • Microsoft.EntityFrameworkCore.SqlServer

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.

Installing EFCorePowerTools

EFCorePowerTools are available as a free Visual Studio extension:

Reverse Engineering Entities (Scaffolding)


Database First – creating the model

Now is the time to do actual work. Below are screenshots showing how to create the 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

  • table “Customers”
  • view “Invoices” (green)
  • stored procedure “CustOrdersOrders”
  • Several classes have been generated (orange).

Also, the database context file NorthwindContext has been generated (yellow).

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 founds that only available “community open source” tools have bugs from time to time.

For example, in my production system on SQL Server, I have maybe 300 stored procedures, and other guys from other teams sometimes change some stored procedures. If tooling is not helping me with those changes, I need to manually detect them or wait for bugs to be reported to be notified that something changed.

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 the 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 the 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:

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:

Changes to the database

So, what if you change your database schema? You need to regenerate the EF model; the above process will overwrite existing classes. That is the reason you can not 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.

Conclusion

We showed how “Database First” EF7 model class generation works 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 the 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 [5] comment from Arthur Vickers, Engineering Manager for .NET Data and Entity Framework at Microsoft, that:

  • “Visual tools 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.

References


Similar Articles