Create Northwind Traders Code First with Entity Framework Core – Part 1

In this 2 part series, I’ll show you how to create a code first version of Northwind using Entity Framework Core. We’ll start by creating the database from scripts, scaffolding the model within a project, adding a migration for the initial create, and finally, adding a migration to seed the data from scripts. Once completed, you’ll have a code first version of Northwind that you can run anywhere – Windows, Linux, or Mac.

Prerequisites

• Visual Studio 2017
• .NET Core

Create the solution

We will begin by creating a new ASP.NET Core Web Application.

1. Open Visual Studio 2017 and click File | New | Project…
2. Under .NET Core, select ASP.NET Core Web Application (.NET Core)
3. Set Name to NorthwindTraders
4. Click OK

   

   Pro Tip: You may not see the .NET Core project templates. If this is the case, launch the Visual Studio Installer and ensure you have installed the .NET Core cross-platform development component.

5. Select Web API

6. Set Authentication to No Authentication

7. Click OK

   

Next, we will create a Class Library (.NET Core) project to contain our model and data classes.

1. Right-click on the NorthwindTraders solution and click Add | New Project…
2. Within .NET Core select Class Library (.NET Core)
3. Set Name to NorthwindTraders.Data
4. Click OK

   

5. Within the newly created NorthwindTraders.Data project, delete the Class1.cs file.

Now we’ll add a reference from the NorthwindTraders project to our NorthwindTraders.Data project.

1. Within the NorthwindTraders project, right-click on Dependencies and click Add Reference…
2. Under Projects | Solution, check the NorthwindTraders.Data project
3. Click OK

   

Finally, we’ll install the necessary NuGet packages to use Entity Framework Core in NorthwindTraders.Data.

1. Open the Package Manager Console (View | Other Windows | Package Manager Console)
2. Set the Default Project to NorthwindTraders.Data

   

3. Execute the following commands:

   Install-Package Microsoft.EntityFrameworkCore.SqlServer
   Install-Package Microsoft.EntityFrameworkCore.SqlServer.Design
   Install-Package Microsoft.EntityFrameworkCore.Tools
   

We are now ready to start building the Northwind data model. Before you continue, run a quick build (Ctrl + Shift + B) to ensure there are no errors.

Creating the data model

We will generate the data model by scaffolding from an existing database. To do so we will use a number of T-SQL scripts. Click here to download these scripts now.

This archive contains three scripts:

1. Northwind.sql creates the Northwind tables, views, and stored procedures
2. DataSeed_Up.sql seeds the database with data
3. DataSeed_Down.sql removes all data from the database

Extract the scripts in a known location, we will use them in the following steps.

1. Within Visual Studio 2017, open SQL Server Object Manager (View | SQL Server Object Manager)
2. Expand the SQL Server item you would like to use, right-click on Databases and click Add New Database

   

3. Set Database Name to Northwind

4. Click OK
5. Click File | Open | File… (Ctrl + O)
6. Locate and select the Northwind.sql script

7. Click Open

   

8. Click Connect

9. Select the relevant server and Northwind database

10. Click Connect

    

11. Click Execute (Ctrl + Shift + E)

    

Your new Northwind database has been created, and we will now use this database to scaffold our data model.

1. Open the Package Manager Console and set the Default project to NorthwindTraders.Data
2. Execute the following command (specifying the relevant connection string):

   Scaffold-DbContext -Connection "Server=(LocalDb)\MSSqlLocalDb;Database=Northwind;Trusted_Connection=True;" -Provider "Microsoft.EntityFrameworkCore.SqlServer" -DataAnnotations
   

   Pro Tip: If you receive a “The term ‘Scaffold-DbContext’ is not recognized as the name of a cmdlet” error, restart Visual Studio and try again.

The NorthwindTraders.Data project now contains a new NorthwindContext and entities for each table within the database, such as Customers, Orders and Products.

Next Steps

In part 1 of this series, I guided you through the steps to create a new Northwind solution, set up a new Northwind database, and scaffold the data model by using Entity Framework Core. You now have a code first version of Northwind, but we still have a long way to go. In the next part, I’ll cover the following:

• Refactoring the data model. We will change the pluralisation of our entities and rename some of our navigation properties.
• Creating data migrations. We will add an initial-create migration, responsible for creating the database.
• Seeding the data model. We will use a script to seed our database and discuss an alternate approach that supports many providers.
• Test drive Northwind. We’ll create a simple Web API to test drive our new Northwind database.

I hope you’ve found this post useful. Feel free to reply with any questions, comments, or improvements.