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

Welcome back! In part 1 of this series we created the solution and scaffolded the data model based on an existing database. In part 2 we will clean-up the data model by improving the naming of entities and navigation properties, we will create two data migrations – one to create the database and another to seed the database, and finally, we will create a simple controller to test drive our completed solution.

Renaming the entities

The code that has been scaffolded does not follow a naming convention. Unlike previous versions, EF Core does not support singularization or pluralization during scaffolding. Instead, it uses the name exactly as specified in the database. So for the Customers table, it has generated a Customers class, rather than a Customer class. I have found two options to fix this issue:

1. Refactor | Rename entity classes
2. Follow this guide – EF Core 1.1 Pluralization in Reverse Engineer

In this case, I recommend the first option, as there are only 11 entities this will be relatively quick. However, if you were dealing with a large number of entities, the second option would be much more productive.

Follow these steps to rename the file and class names:

1. Right-click on Categories.cs
2. Click Rename
3. Type the new name Category
4. Press Enter
5. When prompted, click Yes
   

This will automatically update the file and class names, along with any references. Repeat the above steps for the remaining files list in the table below.

Refactoring the navigation properties

In some cases, the code that has been scaffolded uses poorly named navigation properties, e.g. InverseReportsToNavigation. To improve this we can just rename the navigation properties and update any associated usages.

Follow these steps to rename the navigation properties:

1. Open Employee.cs
2. Find the following lines:

   [InverseProperty("InverseReportsToNavigation")]
   public virtual Employee ReportsToNavigation { get; set; }
   
   [InverseProperty("ReportsToNavigation")]
   public virtual ICollection<Employee> InverseReportsToNavigation { get; set; }
   

   1. Update the code as follows:

   [InverseProperty("DirectReports")]
   public virtual Employee Manager { get; set; }
   
   [InverseProperty("Manager")]
   public virtual ICollection<Employee> DirectReports { get; set; }
   

3. Open Order.cs
4. Find the following line:
   public virtual Shipper ShipViaNavigation { get; set; }
5. Update the code as follows:
   public virtual Shipper Shipper { get; set; }
6. Open Shipper.cs
7. Find the following line:
   [InverseProperty("ShipViaNavigation")]
8. Update the code as follows:
   [InverseProperty("Shipper")]
9. Build the solution (Ctrl + Shift + B)

Now that we’ve cleaned up our data model, we can begin creating our data migrations.

Creating the base migration

The first migration is typically referred to as the base migration or simply, initial create. It is the migration that is responsible for creating all of the initial database tables.

Follow these steps to create the first migration:

1. Open the Package Manager Console (View | Other Windows | Package Manager Console)
2. Ensure the Default project is set to NorthwindTraders.Data
3. Execute the following command:
   Add-Migration -Name "InitialCreate"

Once complete, a new Migrations folder will be added to the project and will contain the new InitialCreate migration.

Seeding the database

Our project would not be complete without seeding the database. There are a number of ways that we can approach this including using a database initializer, however to keep it simple, we will seed the database using a migration. Within the migration, we will use T-SQL scripts to seed the database.

Follow these steps create a the new migration to seed the database:

1. Within the NorthwindTraders.Data project, right-click on the Migrations folder and click Add | Existing Item…
2. Change the File Type to All Files (*.*)
3. Find and select the SeedData_Down.sql and SeedData_Up.sql files
4. Click Add
   
5. Right-click on the NorthwindTraders.Data project and select Properties
6. Select the Resources tab and click on ‘This project does not contain a default resources file. Click here to create one.‘
7. Within Solution Explorer, drag and drop the SeedData_Down.sql and SeedData_Up.sql onto the newly created resources file
   
8. Open the Package Manager Console (setting the default project NorthwindTraders.Data) and execute the following command:
   Add-Migration -Name "SeedData"
9. From within the Migrations folder, open the newly created migration and add the following using statement:
   using NorthwindTraders.Data.Properties;
10. Update the migration as follows:

    protected override void Up(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(Resources.ResourceManager.GetString("SeedData_Up"));
    }
    
    protected override void Down(MigrationBuilder migrationBuilder)
    {
        migrationBuilder.Sql(Resources.ResourceManager.GetString("SeedData_Down"));
    }
    

11. Run a quick build (Ctrl + Shift + B)

Now that the data model is complete and the data migrations are prepared, we are ready to test the solution.

Testing the solution

In this final section, we add a Web API controller to our NorthwindTraders project. This controller will query the Customers table in the database in order to demonstrate that our code first version of Northwind is ready to go.

First we need to update the generated NorthwindContext. It requires two simple changes, the first is to add a new constructor, and the second is to remove the OnConfiguring method – it is not required.

1. Within NorthwindTraders.Data, open the NorthwindContext.cs file
2. Add a new constructor as follows:
   public NorthwindContext(DbContextOptions<NorthwindContext> options) : base(options) { }
3. Remove the OnConfiguring method

With the new constructor in place, we will be able to specify relevant options (e.g. connection string) when the context is instantiated.

Next, we will need to install the Microsoft EF Core SQL Server provider.

1. Open the Package Manager Console
2. Set the Default project to NorthwindTraders
3. Execute the following command:
   Install-Package Microsoft.EntityFrameworkCore.SqlServer

Next, we will add a connection string and configure dependency injection.

1. Within the NorthwindTraders project, open the appsettings.json file, and update as follows:

   {
   "ConnectionStrings": {
     "NorthwindDatabase": 
       "Server=(LocalDb)\\MSSQLLocalDb;Database=NorthwindDb;Trusted_Connection=True;"
     },
     "Logging": {
       "IncludeScopes": false,
       "LogLevel": {
         "Default": "Warning"
       }
     }
   }
   
   

2. Change the above connection string, if necessary
3. Open the Startup.cs file and add the following using statements

   using Microsoft.EntityFrameworkCore;
   using NorthwindTraders.Data;
   
   

4. Update the ConfigureServices method as follows:

   public void ConfigureServices(IServiceCollection services)
   {
       // Add framework services.
       services.AddDbContext<NorthwindContext>(options => options.UseSqlServer(
           Configuration.GetConnectionString("NorthwindDatabase")));
   
       services.AddMvc();
   }
   

5. Build the solution (Ctrl + Shift + B)

With our NorthwindTraders project correctly configured, now is a good time to create the database.

1. Open the Package Manager Console
2. Set the Default project to NorthwindTraders.Data
3. Execute the following command:
   Update-Database

You can verify that the database was created successfully by opening SQL Server Object Explorer and locating the new database.

We will now create a new customers controller to query the database and return some data.

1. Within the NorthwindTraders project, right-click on the Controllers folder and click Add | Controller
2. Select API Controller – Empty and click Add
   
3. Set Controller name to CustomersController and click Add
   
4. Within the newly created CustomersController.cs file, add the following using statement:
   using NorthwindTraders.Data;
5. Update the code within the controller as follows:

   public class CustomersController : Controller
   {
       private readonly NorthwindContext _context;
   
       public CustomersController(NorthwindContext context)
       {
           _context = context;
       }
   
       // GET api/customers
       [HttpGet]
       public IEnumerable<Customer> Get()
       {
           return _context.Customers.Select(c =>
               new Customer
               {
                   CustomerId = c.CustomerId,
                   CompanyName = c.CompanyName,
                   ContactName = c.ContactName,
                   ContactTitle = c.ContactTitle,
                   Address = c.Address,
                   City = c.City,
                   Country = c.Country
               });
       }
   }
   

6. Press F5 to build and run the solution
7. Navigate to /api/customers (e.g. http://localhost:5210/api/customers) to verify that everything is working correctly
   

Next Steps

In part 2 of this series, I provided recommendations on refactoring the generated code including cleaning up the data context, renaming the entities, and renaming the navigation properties. We then built two migrations, the first responsible for creating the database and the second for seeding the database. Finally, we add a customers controller to the solution so that we could ensure that everything was working correctly. In case you have any issues, please refer to the following resources:

. View the source code
. Entity Framework Core Documentation
. ASP.NET Core Documentation

Thanks for reading. Please feel free to reply with any questions, comments, or improvements.