Create the database with Entity Framework Core migrations

In module 9, Vehicles API Controller: Mock Data, we created the Vehicles controller and an HTTP GET method to return static mocked data. And we have been working with mock data to build the Angular application ever since.

In this module we are going to create a real database using Entity Framework Core migrations, seed the database with real data, and then modify the Vehicles controller to send back the real data as a JSON array of vehicle results.

Let’s get started!

Install the required EF Core Nuget Packages

In order to start using Entity Framework Core in our project. The first thing we need to do is install it.

We will need three packages for our project:

• Microsoft.EntityFrameworkCore
• Microsoft.EntityFrameworkCore.Tools
• Microsoft.EntityFrameworkCore.SqlServer

Installing packages with Nuget Package Manager

A lot of books and tutorials will say the easiest way to install and update Nuget Packages is too use the Nuget Package Manager. I find this way to be extremely error prone, especially if you flip flop back and forth between using NuGet Package Manager and the Package Manager Console. But if you want to go this route, right click on the FredsAPI project and select, “Manage Nuget Packages…”

Once the nuget package manager opens, select the browse tab, set the package source to nuget.org, search for the required package (Microsoft.EntityFrameworkCore in the screenshot below), click on the required package in the results, and select the latest version for our base framework. We are running .Net 7.0. The latest version for .Net 7.0 shown is 7.0.13. So we would select that.

Once again this is not my recommended approach. I prefer to use PowerShell and Nuget.org which I will explain in a minute.

Installing packages with Package Manager Console

A second option is to use the Package Manager Console also from within Visual Studio. To use this option, select Tools -> NuGet Package Manager -> Package Manager Console from the top menu in Visual Studio.

The Package Manager Console will open at the bottom of Visual Studio with a Command Prompt.

To install the first Package, Microsoft.EntityFrameworkCore, you would type in the command:

Install-Package Microsoft.EntityFrameworkCore

Installing packages with PowerShell and Nuget.org

My recommended approach is to search Nuget.org for the needed package so you can see the exact name of the package and all of the available versions. You can then copy the needed PowerShell command to the clip board and paste it into PowerShell to run it. This is the approach we are going to use.

Open a browser and go to https://www.nuget.org. In the search box type Microsot.EntityFrameworkCore and click the hour glass search icon to search for the package.

In the search results click on the Microsoft.EntityFrameworkCore link.

Clicking on this link brings you to the Microsoft.EntityFrameworkCore package page. Next click on the Versions tab.

Click the latest version available for the .Net 7.0 framework which is 7.0.13. The .Net CLI command we are going to use in PowerShell is shown at the top with the correct version we want to use.

dotnet add package Microsoft.EntityFrameworkCore --version 7.0.13

Click the Copy to Clipboard icon to copy the above command to the clipboard.

Now open a Command Prompt, and type in the following commands to navigate to the FredsAPI project and install the Microsoft.EntityFrameworkCore package. Remember you can paste the second line from the clipboard.

cd C:\Development\FredsCars\FullStack\Module22\FredsCarsAPI
dotnet add package Microsoft.EntityFrameworkCore --version 7.0.13

You would then follow the same process to search for and install the last two needed packages.
Run the following two commands to install these packages.

dotnet add package Microsoft.EntityFrameworkCore.Tools --version 7.0.13
dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 7.0.13

To see the installed packages in Visual Studio, in the FredsAPI project expand Dependencies and then Packages and you should see all three packages installed with version 7.0.13.

Add a Column TypeName Data Annotation to the Data Model

We are going to create an EntityFramework Core migration and generate the database soon. But if we were to run the command to create a migration at this point, based on the current data model and C# Vehicle POCO, we would get a precision error message similar to that in the following image.

This is waring us that our data could be truncated if we don’t decide on a proper column definition. Let’s nip this in the bud right now and add a Data Annotation attribute to the Price property in our Vehicle Entity.

Open the Vehicle.cs file and modify it with the code below in bold blue font.

FredsCarsAPI/Models/Vehicle.cs

using System.ComponentModel.DataAnnotations.Schema;

namespace FredsCarsAPI.Models
{
    public enum Status
    {
        New,
        Used
    }
    
    public class Vehicle
    {
        public int Id { get; set; }
        public Status Status { get; set; }
        public string Year { get; set; } = string.Empty;
        public string Make { get; set; } = string.Empty;
        public string Model { get; set; } = string.Empty;
        public string Color { get; set; } = string.Empty;
        [Column(TypeName = "decimal(18,2)")]
        public double Price { get; set; }
        public string VIN { get; set; } = string.Empty;

        public int VehicleTypeId { get; set; }
        public VehicleType VehicleType { get; set; } = null!;
    }
}

In the code above we have brought in the System.ComponentModel.DataAnnotations.Schema namespace with a using statement at the top of the file so we can use the Column attribute on the Price property.

We add the Column attribute with a TypeName parameter to the Price property like this:

[Column(TypeName = "decimal(18,2)")]
public double Price { get; set; }

In the two code lines above, we are using the Column attribute and TypeName parameter to tell EF Core to correctly map the Price property to the currency type when it creates the database. We will see this in action soon.

---

While we are here we could choose to add some Required Validators to most of the properties in the Vehicle and VehicleType Entities. But let’s save that for later when we get more into validation shall we?

A required validator for the Vehicle VIN property would look something like the following. But again let’s save that for when we get to validation.

[Required]
public string VIN { get; set; } = string.Empty;

Use the Table Data Annotation to pluralize database table names

When we run our first migration and create the database, EF Core by default will create two tables with the names of our two POCO Entity objects; Vehicle, and VehicleType. A lot of developers would rather see these names pluralized in the database in the form of Vehicles and VehicleTypes.

We can use the Table Data Annotation to make this happen. Modify the Vehicle.cs once more with the code below.

FredsCarsAPI/Models/Vehicle.cs

using System.ComponentModel.DataAnnotations.Schema;

namespace FredsCarsAPI.Models
{
    public enum Status
    {
        New,
        Used
    }

    [Table("Vehicles")]
    public class Vehicle
    {
        public int Id { get; set; }
        public Status Status { get; set; }
        public string Year { get; set; } = string.Empty;
        public string Make { get; set; } = string.Empty;
        public string Model { get; set; } = string.Empty;
        public string Color { get; set; } = string.Empty;
        [Column(TypeName = "decimal(18,2)")]
        public double Price { get; set; }
        public string VIN { get; set; } = string.Empty;

        public int VehicleTypeId { get; set; }
        public VehicleType VehicleType { get; set; } = null!;
    }
}

The code change above should be very simple to understand and self explanatory. We are just telling EF Core to name the database table Vehicles instead of Vehicle.

Now let’s do the same thing for VehicleType. Open the VehicleType.cs file and make the modificaton shown below.

FredsCarsAPI/Models/VehicleType.cs

using System.ComponentModel.DataAnnotations.Schema;

namespace FredsCarsAPI.Models
{
    [Table("VehicleTypes")]
    public class VehicleType
    {
        public int Id { get; set; }
        // ["Car", "Truck", "Jeep"]
        public string Name { get; set; } = string.Empty;
    }
}

Create the DbContext

In this step we are going to create the DbContext. This step is crucial to EF Core doing it’s job; Creating the database and acting as a bridge between our C# POCO Entity objects and the database while the web application is running.

First, create a folder called Data on the root of the FredsCarsAPI project.

In the new Data folder, create a class called ApplicationDbContext and fill it with the contents below.

using FredsCarsAPI.Models;
using Microsoft.EntityFrameworkCore;

namespace FredsCarsAPI.Data
{
    public class ApplicationDbContext : DbContext
    {
        public ApplicationDbContext (DbContextOptions<ApplicationDbContext> options) 
            : base(options) 
        { }

        public DbSet<Vehicle> Vehicles => Set<Vehicle>();
        public DbSet<VehicleType> VehicleTypes => Set<VehicleType>();
    }
}

In the code above, our new ApplicationDbContext class inherits from EF Core’s DbContext class. Its constructor takes in a DbContextOptions class. Once we register ApplicationDbContext in the DI container, the options will tell it to use the SQL Server data provider.

At the bottom of the code we create two DbSets; one for Vehicles and one for VehicleTypes. These DbSets can be used to query, create, and update instances of Vehicle and VehicleType. ApplicationDbContext tracks any changes, and saves changes to the database.

Define the Connection String

The next thing we need to do is set the connection string in the appsettings for development. Open the appsettings.Development.json file. Remember appsettings.Development.json is initially hidden in Solution Explorer so you have to click on the arrow to the left of appsettings.json to expand it and see the development version of the file.

Once you have opened the appsettings.Development.json file modify it with the contents below.

FredsCarsAPI/appsettings.Development.json

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  },
  "ConnectionStrings": {
    "ApplicationContext": "Server=(localdb)\\mssqllocaldb;Database=FredsCars;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

In the code above we are setting the server to localDb. This is a light version of SQL Server that is run in user mode and requires no administration. This approach is great for speedy and rapid development and works great with migrations. We also set the database name to FredsCars. This is what EF Core will name the database when we use the first migration to create it.

Register the dbContext

We’re almost there. The next thing we are going to do is register the dbContext in the IO Container or DI (Dependancy Injection) as a service. So any time a component like our Vehicles controller needs the dbContext, DotNet will take care of it by creating an instance and injecting it as a paramater to the component’s constructor. This is called constructor injection.

Open the Program.cs file and modify it with the code below in bold blue font.

FredsCarsAPI/Program.cs

using FredsCarsAPI.Data;
using Microsoft.EntityFrameworkCore;
using System.Text.Json.Serialization;

var builder = WebApplication.CreateBuilder(args);

// Add services to the container.
builder.Services.AddDbContext<ApplicationDbContext>(options =>
    options.UseSqlServer(
        builder.Configuration
        .GetConnectionString("ApplicationContext")));

builder.Services.AddControllers()
    .AddJsonOptions(x =>
    {
        x.JsonSerializerOptions.Converters.Add(new JsonStringEnumConverter());
    });

... existing code ...

In the above code we have registered the ApplicationDbContext class as a service. Now, when DI instantiates an instance of the DbContext, it passes a DbContextOptionsBuilder to the DbContext’s constructor telling it to use SQL Server using the DbContextOptionsBuilder.UseSqlServer() method. UseSqlServer() takes in a connection string as a parameter and we use the configuration system’s GetConnectionString() method to fetch the connection string we just defined in the last section in the appsettings.development.json file called ApplicationContext.

Create the first Migration: Initial

In a second we are going to use the DotNet EF Tools to create our first migration. So check if you have the EF Tools installed by running the following command.

dotnet ef --version

If you have the EF tools installed, you should see something similar to the following screenshot.

In the screenshot above, you can see I do have the EF Core Command-line tools installed. If you don’t have the EF Core Command-line tools installed yet, run the following command.

dotnet tool install --global dotnet-ef

Create the “Initial” migration

Ok, we finally have all of the pieces together and can now create our first migration. Open a command prompt and navigate to the FredsCarsAPI project and run the command below.

dotnet ef migrations add "Initial"

When the command finishes running, you’ll be able to see in Solution Explorer that a Migrations folder was created on the root of the FredsCarsAPI project, and inside of the new folder is a file named something similar to 20230927120928_Initial.cs as shown in the screenshot below. It took the name we specified in the last command we ran, Initial, and prefixed it with a timestamp and an underscore. You’re time stamp will be different of course.

Create the database

Now that we have the first migration ready to go we can use the update command to generate the database. In your command prompt, make sure you are still in the FredsCarsAPI project directory and run the following command.

dotnet ef database update

Once the command finishes running there will be a file called FredsCars.mdf in your C:\Users\[user name] folder.

Inspect the Database

We can inspect the database using SQL Server Object Explorer right from within Visual Studio. In Visual Studio select View from the top menu and select SQL Server Object Explorer.

Now from the SSOX (SQL Server Object Explorer) window, you should be able to expand your localDB connection, expand the FredsCars database, and from there expand the tables. From there you can expand the Vehicles and VehicleTypes tables (pluralized from our Table Data Annotations) and see the columns created from our Data Model.

Now right click on the Vehicles Table and select View Designer. A screenshot of the Vehicles Design window is below.

In the screenshot above we can see that EF Core migrations made the ID column the primary key by the key icon to the left of it. It knows to do this for any column we name Id. It also made the VehicleTypeId column a foreign key to the VehicleTypes table. Perfect! This accurately reflects our Data Model.

If you follow the same process to view the VehicleTypes table design, you will see that EF Core migrations also made the Id column the primary key for the table.

Using Secrets Storage

Let’s backtrack a little bit to when we set the connection string in appsettings.development.json.

As we already know, we can have an appsettings.json file specific to all of our environments; development, stage, production, and any custom environments we add. Any connection strings or key values we put in one of these environments’ appsettings.json file will override the main appsettings values. We used the appropriate file to set our connection string, appsettings.development.json, since that is the environment we are working in right now.

However, there is a slight problem with this approach in storing our connection string. Right now we are using Windows Authentication in our connection string. But if we want to create a specific user in the future, say, FredsCarsReadOnlyUser, and use an Id and Password in the connection string, it would be exposed in the appsettings.development.json file on the server. And when we check it in with source control like git, other developers could download our password creating a security risk.

So, in this section we are going to address the issue with User Secrets. We are going to create a secrets.json file that will override or add to any values in app.development.json just as app.development.json adds to or overrides appsettings.json.

Create the secrets.json file

In Solution Explorer right click on the FredsCarsAPI project and select Manage User Secrets.

Visual Studio will create a unique id for your UserSecrets and add a UserSecretsId element to the PropertyGroup element in the FredsCarsAPI.csproj file.

Double click the FredsCarsAPI project in Solution Explorer to view the project properties file.

FredsCarsAPI/FredsCarsAPI.csproj

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

  <PropertyGroup>
    <TargetFramework>net7.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <UserSecretsId>3834f113-f101-4881-9aeb-283b873ec088</UserSecretsId>
  </PropertyGroup>
... existing code ...

A new secrets.json file will be created in a folder named after the newly created UserSecretsId and open in Visual Studio where you can edit it. The pathway to the file will be:

C:\Users\[Your-User-Name]\AppData\Roaming\Microsoft\UserSecrets\[unique-id]\secrets.json

Of course to navigate to the file in File Explorer, replace [Your-User-Name] with your user folder name and [unique-id] with the unique id Visual studio created in FredsCars.csproj.

Change the UserSecretsId value

The UserSecretsId Visual Studio creates is kind of cryptic for the human eye to read and understand. Let’s change the value of UserSecretsId from the uniquely generated id to “FredsCarsAPI”.
Double click on the FredsCarsAPI project in Solution Explorer to open the FredsCarsAPI.csproj file and make the edit below.

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

  <PropertyGroup>
    <TargetFramework>net7.0</TargetFramework>
    <Nullable>enable</Nullable>
    <ImplicitUsings>enable</ImplicitUsings>
    <UserSecretsId>FredsCarsAPI</UserSecretsId>
  </PropertyGroup>
... existing code ...

Now rename the UserSecretsId folder in File Explorer from the unique id to “FredsCarsAPI”.

Now, close the secrets.json file and reopen it again by right clicking on the FredsCarsAPI project and selecting Manage User Secrets. If you hover your mouse over the secrets.json file tab, you see the parent folder in path has been renamed to FredsCarsAPI. Now we can easily locate it and move it to another machine if we ever need to.

Move the connection string

Now that we have the secrets.json file all setup and ready to go, let’s cut the connection string from appsettings.development.json and paste it into secrets.json.

Once you’ve made the changes, appsettings.development.json should look like this.

{
  "Logging": {
    "LogLevel": {
      "Default": "Information",
      "Microsoft.AspNetCore": "Warning"
    }
  }
}

And, secrets.json should look like this.

{
  "ConnectionStrings": {
    "ApplicationContext": "Server=(localdb)\\mssqllocaldb;Database=FredsCars;Trusted_Connection=True;MultipleActiveResultSets=true"
  }
}

In the next section we are going to seed the database and this will help test if the connection string is still working from its new location.

Seed the Database

Now that we have a database that reflects our Data Model, we need to fill it with some data. We are going to, “Seed”, the database with test data. And configure the application to only seed the database if we are running the application in the development environment, and there are no existing records in the database. So let’s get started!

Create a class file named SeedData.cs in the FredsCarsAPI/Models folder and fill it with the contents below.

FredsCarsAPI/Models/SeedData.cs

using FredsCarsAPI.Data;
using Microsoft.EntityFrameworkCore;

namespace FredsCarsAPI.Models
{
    public class SeedData
    {
        public static void Initialize(IServiceProvider serviceProvider)
        {
            using (var context = new ApplicationDbContext(
                serviceProvider
                .GetRequiredService<DbContextOptions<ApplicationDbContext>>())) 
            {
                if (context.Vehicles.Any() || context.VehicleTypes.Any())
                {
                    return;
                }

                context.VehicleTypes.AddRange(
                    new VehicleType
                    {
                        Name = "Car"
                    },
                    new VehicleType
                    {
                        Name = "Truck"
                    },
                    new VehicleType
                    {
                        Name = "Jeep"
                    }
                );
                context.SaveChanges();

                var carTypeId = context.VehicleTypes
                    .FirstOrDefault(vt => vt.Name == "Car")!.Id;
                var truckTypeId = context.VehicleTypes
                    .FirstOrDefault(vt => vt.Name == "Truck")!.Id;
                var jeepTypeId = context.VehicleTypes
                    .FirstOrDefault(vt => vt.Name == "Jeep")!.Id;

                context.Vehicles.AddRange(
                    // Cars
                    new Vehicle
                    {
                        Status = Status.New,
                        Year = "2021",
                        Make = "Dodge",
                        Model = "Challenger",
                        Color = "Frostbite",
                        Price = 64164,
                        VIN = "2C3CDZFJ8MH631199",
                        VehicleTypeId = carTypeId
                    },
                    new Vehicle
                    {
                        Status = Status.Used,
                        Year = "2020",
                        Make = "Ford",
                        Model = "Escape",
                        Color = "Oxford White",
                        Price = 22999,
                        VIN = "1FMCU0F63LUC25826",
                        VehicleTypeId = carTypeId
                    },
                    new Vehicle
                    {
                        Status = Status.New,
                        Year = "2021",
                        Make = "Dodge",
                        Model = "Durange",
                        Color = "Black",
                        Price = 50557,
                        VIN = "1C4RDJDG5MC837730",
                        VehicleTypeId = carTypeId
                    },
                    new Vehicle
                    {
                        Status = Status.New,
                        Year = "2021",
                        Make = "Nissan",
                        Model = "Niro",
                        Color = "Blue",
                        Price = 24960,
                        VIN = "2XYZT67JTF24AZG856",
                        VehicleTypeId = carTypeId
                    },
                    new Vehicle
                    {
                        Status = Status.New,
                        Year = "2021",
                        Make = "Kia",
                        Model = "Stinger",
                        Color = "Gray",
                        Price = 36090,
                        VIN = "6FG146B89624AZ7952",
                        VehicleTypeId = carTypeId
                    },
                    new Vehicle
                    {
                        Status = Status.New,
                        Year = "2021",
                        Make = "Kia",
                        Model = "Stinger",
                        Color = "Gray",
                        Price = 36090,
                        VIN = "6FG146B89624AZ7952",
                        VehicleTypeId = carTypeId
                    },
                    // Trucks
                    new Vehicle
                    {
                        Status = Status.New,
                        Year = "2022",
                        Make = "Ram",
                        Model = "Crew Cab",
                        Color = "Black",
                        Price = 68400,
                        VIN = "3C6UR5DL8NG157035",
                        VehicleTypeId = truckTypeId
                    },
                    new Vehicle
                    {
                        Status = Status.Used,
                        Year = "2017",
                        Make = "Ram",
                        Model = "Crew Cab",
                        Color = "Red",
                        Price = 33000,
                        VIN = "1C6RR7PT0HS814596",
                        VehicleTypeId = truckTypeId
                    },
                    // Jeeps
                    new Vehicle
                    {
                        Status = Status.New,
                        Year = "2022",
                        Make = "Jeep",
                        Model = "Compass",
                        Color = "White",
                        Price = 34980,
                        VIN = "3C4NJDFB5NT114024",
                        VehicleTypeId = jeepTypeId
                    },
                    new Vehicle
                    {
                        Status = Status.New,
                        Year = "2022",
                        Make = "Jeep",
                        Model = "Compass",
                        Color = "Red",
                        Price = 39275,
                        VIN = "3C4NJDCB1NT118172",
                        VehicleTypeId = jeepTypeId
                    },
                    new Vehicle
                    {
                        Status = Status.New,
                        Year = "2022",
                        Make = "Jeep",
                        Model = "Grand Cherokee",
                        Color = "Pearlcoat",
                        Price = 53575,
                        VIN = "1C4RJKBG5M8201121",
                        VehicleTypeId = jeepTypeId
                    },
                    new Vehicle
                    {
                        Status = Status.New,
                        Year = "2021",
                        Make = "Jeep",
                        Model = "Wrangler Sport S",
                        Color = "Green",
                        Price = 40940,
                        VIN = "1C4GJXAN0MW856433",
                        VehicleTypeId = jeepTypeId
                    }
                );
                context.SaveChanges();
            }
        }
    }
}

In the next step we are going to call the SeedData.Initialize() method from program.cs (the startup file). program.cs will pass in an IServiceProvider object to SeedData’s static Initialize method so it can get the ApplicationDbContext from DI.

In the code above, the ApplicationDbContext is assigned to a variable named context within a using block so it will be disposed of when we are done working with the database.

Within the using block we check to make sure there are no existing records within the Vehicles or VehicleTypes tables. If there are we kick out of the Initialize method. Otherwise we continue on.

Next we use the DbContext.[TableName].AddRange() method to fill the VehicleTypes table with the Name values of Car, Truck, and Jeep. Notice we do not set the ID of each VehicleType. SQL Server will assign an Id to each VehicleType record when it is created. We then call the DbContext.SaveChanges() method so the VehicleTypes will have Ids in the database we can access before creating Vehicle records.

Next we fetch the VehicleType Ids and store them in three variables so they will be easier to work with while creating Vehicle records; carTypeId, truckTypeId, and jeepTypeId.

Finally we fill the Vehicles table with test data using the DbContext.[TableName].AddRange() method where TableName is Vehicle. We are able to set the VehicleTypeId property of each Vehicle using one of the three VehicleType Id variables we created so we don’t have to use a long lambda expression to set every single Vehicle’s VehicleTypeId. And then we call DbContext.SaveChanges() once again to save the Vehicle objects to the database. Again we did not need to set the Id properties of the Vehicle objects because SQL Server will do that for us when the Vehicles are inserted.

---

Next modify the program.cs file with the modification below in bold blue font.

FredsCarsAPI/Program.cs

... existing code ...

var app = builder.Build();

if (app.Environment.IsDevelopment())
{
    using (var scope = app.Services.CreateScope())
    {
        var services = scope.ServiceProvider;

        SeedData.Initialize(services);
    }
}

// Configure the HTTP request pipeline.
if (app.Environment.IsDevelopment())
{
    app.UseSwagger();
    app.UseSwaggerUI();
}

... existing code ...

In the code above, we call SeedData.Initialize() on startup after the web application has been built and pass in an IServiceProvider object to the Initialze() method so it can get the ApplicationDbContext from DI. However, we only do this if we are running in development.

That’s about it. The next time we run the application our Seeding strategy should fill our database tables with real data. But only the first time we run it. After that the SeedData.Initialize() method will see there is already data and kick out. If we want to reset the database we can always drop the database, re-run the dotnet ef database update command and run the project again. SeedData.Initialize() will see there is no data and do its job filling the database with our test data.

To drop and reseed the database run the following command from a command prompt and rerun the application.

dotnet ef database drop --force --context FredsCars

Run the project

Everything should now be in place to seed the database. Go ahead and run the API project in debug mode. Right click on the FredsCarsAPI project and select Debug -> Start New Instance.

A new ASP.Net Core API development console window will open and you will be able to see the Insert statements inserting our Vehicles into the database.

Inspect the Database

Finally, after all of our hard work we should be able to inspect the database and see our test data inserted into our tables. Stop the ASP.Net Core API application if it is still running by typing Ctrl+C in the console window.

From within Visual Studio, go to the SSOX window, right click on the VehicleTypes table and select View Data.

The VehicleTypes Data will open in a new tab within Visual Studio. Our Vehicle types have been inserted! And, as promised, SQL Server has created Ids for us.

Now, in the SSOX window, right click on the Vehicles table and select View Data. All of our test data has been inserted for Vehicles. Once again SQL Server has generated Ids for us. And, the VehicleTypeId column is the foriegn key to the VehicleTypes table. Remember, this column serves as the navigation property to fill VehicleType property objects for Vehicles in our data model.

Modify the Vehicles Controllers

Now we can finally replace our mock vehicle data with real data.

Let’s start with the VehicleTypes controller. Open the VehicleTypesController.cs file and replace it with the contents below.

FredsCarsAPI/Controllers/VehicleTypesController.cs

using FredsCarsAPI.Data;
using FredsCarsAPI.Models;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace FredsCarsAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class VehicleTypesController : ControllerBase
    {
        private ApplicationDbContext _context;

        public VehicleTypesController(ApplicationDbContext context)
        {
            _context = context;
        }

        [HttpGet]
        public async Task<IEnumerable<VehicleType>> GetVehicles()
        {
            var vehicleTypes = _context.VehicleTypes;

            return await vehicleTypes.ToListAsync();
        }
    }
}

In the code above we have replaced the static VehicleType data by taking in the DbContext via DI in the constructor, and in the GetVehicles HTTP GET method we simply get all of the VehicleTypes from the DbContext and then return them as we convert them with the ToListAsync() method.

We can use the asynchronous ToListAsync method instead of the synchronous ToList method since we made this method asynchronous with the async and await keywords. We should always try to make our methods asynchronous when working with IO. Asyncronous programming is covered in more depth in Part Two, ASP.Net Core: Dynamic Applications, Data and Frameworks.

If you run a test on the VehicleTypes GET method in Swagger, you’ll see the results are the same as before. Except this time we are returning real data from the database.

---

Now let’s do the same for the Vehicles controller. Open the VehiclesController.cs file and modify it with the contents below.

FredsCarsAPI/Controllers/VehiclesController.cs

using FredsCarsAPI.Data;
using FredsCarsAPI.Models;
using FredsCarsAPI.Models.DTOs;
using Microsoft.AspNetCore.Http;
using Microsoft.AspNetCore.Mvc;
using Microsoft.EntityFrameworkCore;

namespace FredsCarsAPI.Controllers
{
    [Route("api/[controller]")]
    [ApiController]
    public class VehiclesController : ControllerBase
    {
        private ApplicationDbContext _context;

        public VehiclesController(ApplicationDbContext context)
        {
            _context = context;
        }


        [HttpGet]
        public async Task<IEnumerable<VehicleDTO>> GetVehicles()
        {
            var vehicles = await _context.Vehicles
                .Include(v => v.VehicleType)
                .ToListAsync();
            var vehicleDTOs = new List<VehicleDTO>();

            foreach (var vehicle in vehicles)
            {
                vehicleDTOs.Add(ConvertVehicleToDTO(vehicle));
            }
     
            return vehicleDTOs;
        }

        private VehicleDTO ConvertVehicleToDTO(Vehicle vehicle)
        {
            return new VehicleDTO
            {
                Id = vehicle.Id,
                Status = vehicle.Status,
                Year = vehicle.Year,
                Make = vehicle.Make,
                Model = vehicle.Model,
                Color = vehicle.Color,
                Price = vehicle.Price,
                VIN = vehicle.VIN,
                VehicleType = vehicle.VehicleType.Name
            };
        }
    }
}

In the code above we again take in the DbContext via construction injection and use it in the GetVehicles HTTP Get method to fetch all the vehicles and assign them to vehicles variable.

We use the LINQ Include() method to load the VehicleType property for each Vehicle. Otherwise VehicleType would be null for each Vehicle and we would get an exception in the upcoming foreach block. And again we use the ToListAsync() method to fetch the Vehicle data asyncronously.

We then create a variable called vehicleDTOs of type List<VehicleDTO> to hold a list of transformed Vehicles.

Next, we loop through the vehicles with a foreach blcok converting each Vehicle object to a VehicleDTO object and add each one to the vehicleDTOs list.

Then we can simply return the vehicleDTOs variable.

Now if you run a Swagger test on the Vehicles Get service, the results should be the same as before, but again we are now returning real data instead of static mocked data.

And if you run the application and navigate to the Vehicles component, the results should look the same as before, with the same functionality; paging and sorting.

What’s Next

In this module we finally created a real database and switched out our mock data for real data coming from an SQL Server Database. This may have seemed like a lot of steps and it was one of our longest modules yet in this chapter. But it will all become habit and start to make more sense after a few times with some practice. But now we have a real concreate database and we can continuously modify it in development simply by making modifications to C# POCO objects, creating new Entity Framework migrations, and running EF update database commands.

Also at this point in the chapter we’ve hit a really big milestone. Except for some functional searching capabilities we have started to set up in our side-nav, we have a complete Vehicles webpage where the user is able to retrieve, page, and sort data (albeit client-side default paging and sorting). In the next several modules we are going to improve upon the default client-side code and start implementing some custom server-side paging and sorting. So hang on to your hat! We still have a lot of killer concepts to learn.