EF Power Tools tutorial

Reverse Engineer a SQL-Server database with EF Power Tools

Note
Before starting, EF Power tool does not support EF Core 5, only EF Core 6 and EF Core 7.

In this article learn how to create a database with three tables in SSMS (SQL-Server Management Studio) followed by creating POCO classes which will represent tables in the new database.

Once the POCO classes ¹ have been created with EF Power Tools ² we will write a few queries.

You will need Microsoft Visual Studio 2022 or higher and Microsoft SSMS.

Create the database

1. Open SSMS
2. Open a connection to (localdb)\MSSQLLocalDB
3. From Object Explorer, right click on Databases
4. Select New database
5. Enter NorthWindContacts for the database name, click OK
6. Right click on NorthWindContacts, select New query
7. Open NorthWindContacts.sql, copy the contents
8. Back in SSMS, paste the contents into the new query
9. Click Execute
10. Right click on NorthWindContacts and select Refesh, there should be three tables.

Install EF Core Power Tools

1. With Visual Studio closes, download EF Power Tools from the following link.
2. Double click on the download, follow the prompts

Open Visual Studio to this solution

1. Right click on the top node in Solution Explorer
2. Select create a new Console project (check do not use top down statements) named NorthWindContactsApp
3. Right click on the project name in Solution Explorer, select Manage NuGet packages
4. On the Browse tab, paste in Microsoft.EntityFrameworkCore.SqlServer, click Install or use the NuGet command prompt Install-Package Microsoft.EntityFrameworkCore.SqlServer --version 7.0.2
5. Right click on the project name in Solution Explorer
6. Select EF Power Tools
7. Select Reverse Engineer
8. Configure a new database connection (see figure 1) and version of EF Core
9. Set connection properties (see figure 2)
10. Accept (see figue 3)
11. Select tables (see figure 4)
12. Next, set several settings (see figure 5)
    1. Context name: NorthWindContext
    2. Namespace: NorthWindContactsApp
    3. EntityTypes: Models
    4. DbCotext path: Data
    5. Check Include connection string in generated code (okay for learning but should not be done for a real app)

Figure 1

Figure 2

Figure 3

Figure 4

Figure 5

Add another package to the project

Add the NuGet package Spectre.Console to the project

Write code to get data

Open Program.cs and replace the contents with the following which will select the top 20 contacts into a table shown below

using Microsoft.EntityFrameworkCore;
using NorthWindContactsApp.Data;
using Spectre.Console;

namespace NorthWindContactsApp
{
    internal class Program
    {
        static void Main(string[] args)
        {
            using var context = new NorthWindContext();

            var contacts = context.Contacts
                .Include(x => x.ContactTypeIdentifierNavigation)
                .Include(x => x.ContactDevices)
                .Take(20)
                .ToList();


            var table = CreateTable();

            foreach (var contact in contacts)
            {
                if (contact.ContactDevices.Any())
                {
                    table.AddRow(contact.ContactId.ToString(), 
                        contact.FirstName, 
                        contact.LastName, 
                        contact.ContactTypeIdentifierNavigation.ContactTitle, 
                        contact.ContactDevices.FirstOrDefault()!.PhoneNumber);
                }
                else
                {
                    table.AddRow(contact.ContactId.ToString(), 
                        contact.FirstName, 
                        contact.LastName, 
                        contact.ContactTypeIdentifierNavigation.ContactTitle, 
                        "[red](none)[/]");
                }
            }

            AnsiConsole.Write(table);
            Console.ReadLine();
        }
        private static Table CreateTable()
        {
            return new Table()
                .RoundedBorder().BorderColor(Color.LightSlateGrey)
                .AddColumn("[b]Id[/]")
                .AddColumn("[b]First[/]")
                .AddColumn("[b]Last[/]")
                .AddColumn("[b]Title[/]")
                .AddColumn("[b]Phone[/]")
                .Alignment(Justify.Center)
                .Title("[white on blue]Contacts[/]");
        }
    }
}

Run the project and the following shows the top 20 contacts

Connection string

In the above instructions the connection string is hard coded into the DbContext. When working say with ASP.NET Core the connection string is moved to the appsetting.json file under a section ConnectionStrings.

Then to get the connection string we have code such as the following will is not showing configuration as each developer may do this differently e.g. use hard coded path or environment variables.

builder.Services.AddDbContextPool<Context>(options =>
    options.UseSqlServer(configuration.GetConnectionString("DefaultConnection")));

Next steps

• Study the code to get an understanding how things work
• Read Microsoft docs on EF Core
• Remove the connection string from NorthWindContext.cs and place into appsettings.json. Look at the project ModelCommentsApp, appsettings.json has the connection string. A NuGet package, ConfigurationLibrary is used to set the connection string in BookContext.cs in OnConfiguring and note there are several options to log or not log EF Core operations.

Note In the above steps we defaulted to setting the database connection string in the DDbContext. Take time to learn how to place the connection string into a configuration file such as appsettings.json. This was not gone over as the focus is how to use EF Power Tools.

Learn more about EF Power Tools

📖 See Erikj's Wiki

You also might like

Working with Console projects

1. A POCO entity is a class that doesn't depend on any framework-specific base class. It is like any other normal .NET CLR class, which is why it is called "Plain Old CLR Objects". ↩

2. Useful design-time DbContext features, added to the Visual Studio Solution Explorer context menu. When right-clicking on a C# project, the following context menu functions are available: Reverse Engineer - Generates POCO classes. ↩