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: https://youtu.be/FNXlsN3barQ
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 (--name): |
nw |
| 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 ps
The following confirmed to me that the container is indeed running:
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">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>netcoreapp3.1</TargetFramework>
</PropertyGroup>
</Project>
I changed TargetFramework netcoreapp3.1 to net5.0. The end result was:
<Project Sdk="Microsoft.NET.Sdk">
<PropertyGroup>
<OutputType>Exe</OutputType>
<TargetFramework>net5.0</TargetFramework>
</PropertyGroup>
</Project>
Rebuild your application to make sure all is OK.
In Visual Studio 2019, click on Extensions >> Manage Extensions.
Exit Visual Studio 2019 for this extension to get installed. When you click Modify on the following dialog, the extension gets installed.
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".
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.
| 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 |
Click on OK. On the "Choose Database Connection" dialog, make sure you check "Use EF Core 5" before clicking on the OK button.
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.
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. |
Click on OK. After the reverse engineering process is completed, you will see the following confirmation dialog:
Click OK. The files in Solution Explorer will look like this:
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) {
Console.WriteLine($"{p.ProductId}\t{p.ProductName}\t{p.Category.CategoryName}");
}
}
Run your application and you should see the following output:
The EF Code Power Tools are quite useful. Read more about what else you can do with it at GitHub - ErikEJ/EFCorePowerTools: Entity Framework Core Power Tools - reverse engineering, migrations and model visualization for EF Core
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);
Console.WriteLine(query.ToQueryString());
foreach (var p in query) {
Console.WriteLine($"{p.ProductId}\t{p.ProductName}\t{p.Category.CategoryName}");
}
}
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:
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) {
Console.WriteLine($"{p.ProductId}\t{p.ProductName}\t{p.Category.CategoryName}");
}
}
The program runs just like before and it is much more simplified. I call this syntactic sugar.
Cleanup
Once you are done, you can stop and remove the Northwind docker container with the following command:
docker rm -f nw
Conclusion
I hope you found this article valuable and hope you join me again in future articles.
No comments:
Post a Comment