Sunday, December 13, 2020

EF Core Power Tools & .NET 5.0

The "EF Core Power Tools" is an open source project on GitHub started by Erik Ejlskov Jensen. It is an extension that you can add to Visual Studio 2019. In this post I will try and introduce you to this very useful tool. We will be using the SQL-Server Northwind database running in a container for sample data. 

Companion Video: 

Let's get started:

Running a docker container with SQL-Server Northwind sample database

I will use a docker image that contains the SQL-Server Northwind database. Credit goes to kcornwall for creating this docker image.

To pull & run the Northwind database in a docker container, run the following command in a terminal window:

docker run -d --name nw -p 1444:1433 kcornwall/sqlnorthwind

The above command does the following:

Docker image:  kcornwall/sqlnorthwind
Container Name
Ports (-p):  Port 1433 in container is exposed as port 1444 on the host computer
Password:  The sa password is Passw0rd2018. This was determined from the Docker Hub page for the image.
-d:  Starts the container in detached mode

This is what I experienced after I ran the above command:

docker run
Let us make sure that the container is running. Execute this command to ensure that the container is running OK.

docker ps

The following confirmed to me that the container is indeed running:

docker ps

Install "EF Core Power Tools" extension into Visual Studio 2019

We will test "EF Core Power Tools" using a C# console application. Start Visual Studio and create a new project based on the "Console App (.NET Core) C#" template. You can name the app whatever you like.

Edit the .csproj file to make sure it is using the latest version of .NET Core. At the time of writing this post, my .csproj file looked like:

<Project Sdk="Microsoft.NET.Sdk">

I changed TargetFramework netcoreapp3.1 to net5.0. The end result was:

<Project Sdk="Microsoft.NET.Sdk">

Rebuild your application to make sure all is OK.

In Visual Studio 2019, click on Extensions >> Manage Extensions.

Enter "EF Core Power Tools" in the filter field. This causes the tool to be the first item in the list. Click on Download beside "EF Core Power Tools".
EF Core Power Tools

Exit Visual Studio 2019 for this extension to get installed. When you click Modify on the following dialog, the extension gets installed.
Finally, click on the Close button.

Using EF Core Power Tools

Restart Visual Studio 2019 and open the console application that you had previously created. Now, when you right-click on the project node in "Solution Explorer", you will see "EF Core Power Tools".
EF Core Power Tools in Visual Studio 2019

Let us reverse engineer the Northwind database that is currently running in a docker container. Click on: EF Core Power Tools >> Reverse Engineer. Then, click on the first Add button.
choose database connection
On the next "Connection Properties" dialog, enter the following data:

Data source: Choose: Microsoft SQL Server (SqlClient)
Server name: localhost, 1444
User name: sa
Password: Passw0rd2018
Save my password: Checked
Select or enter a database name: Choose: Northwind

Connection properties

Click on OK. On the "Choose Database Connection" dialog, make sure you check "Use EF Core 5" before clicking on the OK button.
Use EF Core 5

All the artifacts in the database are shown in the next dialog. These include Tables, Views, and Stored Procedures. To keep it simple, choose only the Categories & Products tables.

SZelect Objects

After selecting Categories & Products tables, click on OK. The next dialog allows you to customize the way that the reverse-engineered code gets generated in your application. I set the following values:

Context name: NorthwindContext
Entity Types path: Models/NW
DbContext path Data The NorthwindContext.cs file will be placed in the Data folder
Pluralize or singularize generated object names (English) Checked Entity class names will be appropriately pluralized or singularized
Use DataAnnotation attributes to configure the model Checked Data Annotations will be used instead of Fluid APIs
Include connection string in generated code Checked Connection string will be hard-coded inside the NorthwindContext.cs. Of course, this is bad practice.
Install the EF Core provider package in the project Checked The package Microsoft.EntityFrameworkCore.SqlServer be automatically installed.

Generate EF Core Model

Click on OK. After the reverse engineering process is completed, you will see the following confirmation dialog:

Model generated successfully

Click OK. The files in Solution Explorer will look like this:

Solution explorer

As expected, The NorthwindContext.cs file is placed in the Data folder and the database model classes are placed in the Models folder. The efpt.config.json file contains the choices that were made while configuring the reverse-engineering process so that, if you do it again, it remembers what you did before.

Replace your Main() method in Program.cs with the following C# code:

using (NorthwindContext context = new NorthwindContext()) {
  var query = context.Products
      .Include(c => c.Category);

  foreach (var p in query) {

Run your application and you should see the following output:

EF Core 5.0 diagnostics

Entity Framework 5.0 is providing us with some diagnostics features.  I will let you know of two such features:

1) ToQueryString() - EF Core 5.0 comes with SQL-based brother to ToString() method for LINQ-queries. This method is called ToQueryString() and it returns provider-specific SQL without connecting to database server. Let's use ToQueryString() with our query object. Modify our code by adding a WriteLine() statement to inspect the query statement that is actually being sent to the database. Insert the statement in boldface below to the Main() method in Program.cs:

using (NorthwindContext context = new NorthwindContext()) {
  var query = context.Products
      .Include(c => c.Category);


  foreach (var p in query) {

Run your application and you will see the following output:


Note the raw query at the top. This is displayed before the query is sent to the database for processing.

2) LogTo() - Comment out the Console.WriteLine() code that we added earlier. Open the  the Data/NorthwindContext.cs file in the editor. Around line 31, add the option shown below in boldface to the optionsBuilder:

  .UseSqlServer("Data Source=localhost,1444;Initial Catalog=Northwind;Persist Security Info=True;User ID=sa;Password=Passw0rd2018")
  .LogTo(Console.WriteLine, Microsoft.Extensions.Logging.LogLevel.Information);

This causes logging to be sent to the console. Run your app and you should see the following diagnostics information:


.NET 5.0 Syntactic Sugar

We can simplify Program.cs using some of the new features in C# 9. Go ahead and delete the namespace, class and Main() method declarations so that Program.cs looks like this:

using Microsoft.EntityFrameworkCore;
using System;
using TestEFPowerTools.Data;

using (NorthwindContext context = new NorthwindContext()) {
    var query = context.Products
        .Include(c => c.Category);

    foreach (var p in query) {

The program runs just like before and it is much more simplified. I call this syntactic sugar.


Once you are done, you can stop and remove the Northwind docker container with the following command:

docker rm -f nw


I hope you found this article valuable and hope you join me again in future articles.

No comments:

Post a Comment