In the last module we modeled the domain and set up our entity classes using the C# POCO’s Vehicle and VehicleType.
In this module, we are going to install EF Core and create the database.
EF Core is installed via Nuget Packages. The specific packages we need are:
Microsoft.EntityFrameworkCore.SqlServer
and
Microsoft.EntityFrameworkCore.DesignMicrosoft.EntityFrameworkCore.SqlServer is the EF Core database provider package for Microsoft SQL Server.
We also need Microsoft.EntityFrameworkCore.Design for the Entity Framework Core Tools to work.
The Microsoft.EntityFrameworkCore.Design package is required for either command-line (DotNet CLI) or Package Manager Console-based tooling, and is a dependency of dotnet-ef and Microsoft.EntityFrameworkCore.Tools.
The Microsoft.EntityFrameworkCore.Tools package is for PowerShell tooling that works in the Visual Studio Package Manager Console (PMC). We won’t be using the PMC. Instead we will be using the DotNet CLI command line. So we do not need this package.
The Entity Framework Core tools help with design-time development tasks. They’re primarily used to manage Migrations and scaffolding of a DbContext and entity types by reverse engineering the schema of a database.
Scaffolding is a way of automating certain tasks like the creation of a DbContext, controllers, and views. We are not using scaffolding in this chapter. We are creating everything manually by hand so we can learn how MVC really works. We may get into scaffolding in the next chapter on Razor Pages, which is another ASP.Net Core Development framework.
Install the EF Core Nuget Package
There are three ways to install and manage packages in ASP.Net.
- Nuget Package Manager: A GUI based system for developers who are not experienced typing commands at a command line or who prefer a GUI interface and wizard.
- Package Manager Console: A command line/PowerShell environment inside of the Visual Studio IDE where DotNet commands can be typed to install packages and DotNet EF commands can be used to create migrations, and create and update databases. These commands have similar but slightly different syntax then using the DotNet CLI in a separate console window.
- DotNet CLI (Command Line Interface) via PowerShell and Nuget.org.
In my experience the Nuget Package Manager is very error prone. The Package Manager Console is also error prone and to me feels very crammed up in a window inside of the Visual Studio IDE.
For those reasons, in this book we will use the DotNet CLI to install packages, create migrations, and create and update databases.
Introducing NuGet.org
I find the NuGet.org site to be very helpful whenever there is a need to install a package for one of my ASP.Net Core applications.
NuGet.org is a free service that hosts public NuGet packages. It is the primary host for public NuGet packages, which are .NET open-source libraries that developers use.
Open up a new browser and point it to https://www.nuget.org.
Enter Microsoft.EntityFrameworkCore.SqlServer into the search textbox and click the search button.
Click the link for the Microsoft.EntityFrameworkCore.SqlServer package.
Click the Versions tab to see all of the versions available for the package.
We want to pick the latest version of this package for the version of .Net we are using. We are using .Net 9. The latest version of the package for .Net 9 is 9.0.0. So that is the one we will use.
If we were using .Net 8, we would look for the latest version of .Net 8 which is 8.0.11 (at the time of this writing).
Go ahead and click on the link for the latest version of the package for .Net 9.
Click the copy button to copy the command to install the package to the clipboard.
Now, open up the Visual Studio PowerShell window and navigate to the FredsCars project.
cd C:\Development\FredsCars\MVC\Module10\FredsCars\FredsCarsNOTE: The above folder structure is the one I am using for development. I have all of my development projects under a folder called Development on the root of the C drive. Then a FredsCars folder. Next the name of the subfolder represents the technology I am building FredsCars in. Then the module I am working in. Then the FredsCars sln folder. And finally the FredsCars project file.
You will need to adjust your path accordingly.
Now, paste the command we copied from NuGet.org into the command line to install the EF SQL Server database provider package into the FredsCars project.
dotnet add package Microsoft.EntityFrameworkCore.SqlServer --version 9.0.0The above command says to use the DotNet add package command to install version 9.0.0 of the package into the FredsCars project. If we left off the –version switch, the command would just get the latest version of the package.
Click Enter to run the command.
Once the command is done running, you can see the package was installed in Solution Explorer by expanding Dependencies and looking under Packages.
Now run the following command to install the Microsoft.EntityFrameworkCore.Design package.
Remember you can search for the package in Nuget.org the same way we did for Microsoft.EntityFrameworkCore.SqlServer and copy the command to the clipboard.
dotnet add package Microsoft.EntityFrameworkCore.Design --version 9.0.0Once the command completes, you should see both needed packages in the Solution Explorer.
Install the EF Core tools
In addition to the two EF Core packages we just installed, we also need to install a third package called a tools package in order to carry out EF Core-related tasks, like creating and applying database migrations.
NOTE: There are two sets of tools available. The .Net Core (DotNet) command-line interface (CLI) tools, and the Package Manager Console (PMC) tools. As discussed above when installing the EF Core SqlServer package, we prefer the DotNet CLI over the PMC. So, that is the way we will go.
Uninstall the current version of dotnet ef
Open a console window and run the following command to uninstall any existing version of dotnet ef.
dotnet tool uninstall --global dotnet-ef
In the screenshot above I have just uninstalled my old version of dotnet-ef for .Net version 8.
Install the current version of dotnet ef
Run the following command in your console window.
dotnet tool install --global dotnet-ef --version 9.0.0The above command installs version 9.0.0 of the dotnet-ef tools package globally. Most developers prefer to install this package one time globally so they don’t have to install it for every project.
NOTE: You can search for dotnet-ef in NuGet.org and copy and paste the command just as we did for the first two packages.
The above screenshot shows version 9.0.0 of the dotnet-ef tools package was successfully installed and the dotnet-ef command is now available to me globally.
Configure Entity Framework Core
Now that we have installed the dotnet-ef tools CLI, we can use it to create the database based off of the Vehicle and VehicleType C# POCO classes we created earlier. But first, we need to take care of a few more plumbing details.
Create the Database Context
The first thing we need to do is create the Database Context.
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 FredsCarsDbContext and fill it with the contents below.
FredsCars/Data/FredsCarsDbContext.cs
using FredsCars.Models;
using Microsoft.EntityFrameworkCore;
namespace FredsCars.Data
{
public class FredsCarsDbContext : DbContext
{
public FredsCarsDbContext(DbContextOptions<FredsCarsDbContext> options)
: base(options)
{}
public DbSet<Vehicle> Vehicles => Set<Vehicle>();
public DbSet<VehicleType> VehicleTypes => Set<VehicleType>();
}
}In the code above, our new FredsCarsDbContext class inherits from EF Core’s DbContext class which acts as a session with the database and can be used to query and save instances of your entities. Its constructor takes in a DbContextOptions class.
Once we register FredsCarsDbContext in the DI (Dependency Injection) container, the options we specify there will tell it to use the SQL Server data provider. (We will talk more about DI when we register FredsCarsDbContext in Program.cs in the Add Services section. And we will learn a lot more about DI when we get into the repository pattern for our controllers to make it easier to unit test them. Stay tuned!)
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 entity instances of Vehicle and VehicleType. FredsCarsDbContext tracks any changes, and saves changes to the database.
C# constructors
In C#, a constructor is a special method that’s called when a class instance is created:
Syntax: A constructor’s name is the same as the name of its type, and it doesn’t include a return type.
Purpose: A constructor’s primary purpose is to set the initial state of an object by assigning values to its fields and properties.
// FredsCarsDbContext constructor
public FredsCarsDbContext(DbContextOptions<FredsCarsDbContext> options)
: base(options) // pass options to base DbContext class
{
// initial state here of any needed fields and properties
}Register the FredsCarsContext
The next thing we are going to do is register the dbContext in the IO Container or DI (Dependency Injection) system as a service. So any time a component like our Vehicles controller needs the dbContext, ASP.Net Core will take care of it by creating an instance of the FredsCarsDbContext and injecting it as a parameter to the component’s constructor. This is called constructor injection.
Modify Program.cs with the code below.
FredsCars/Program.cs
using FredsCars.Data;
using Microsoft.EntityFrameworkCore;
var builder = WebApplication.CreateBuilder(args);
// Add Services
builder.Services.AddControllersWithViews();
builder.Services.AddDbContext<FredsCarsDbContext>(opts =>
{
opts.UseSqlServer(
builder.Configuration["ConnectionStrings:FredsCarsMvcConnection"]
);
});
var app = builder.Build();
// Configure the HTTP request pipeline.
app.UseStaticFiles();
/*** Add endpoints for contoller actions and
the default route ***/
app.MapDefaultControllerRoute();
app.Run();
In the above code we have registered the FredsCarsDbContext 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 WebApplicatonBuilder’s Configuration property to read the connection string to the database from the appsettings.js file which we will define in the next section.
C# Generics
We are at a good point in the development process to start talking about Generics in C#.
Generics are a feature that allows developers to create classes, methods, and other code that can work with multiple data types.
Take for instance, the constructor in the FredsCarsDbContext class.
public FredsCarsDbContext(DbContextOptions<FredsCarsDbContext> options)
: base(options)
{}The parameter to the constructor method is named options. And options is of type DbContextOptions<FredsCarsDbContext>
This can be read as DbContextOptions of type T where T is FredsCarsDbContext
That means DbContextOptions can represent the DbContextOptionsBuilder specifications for any DbContext we define back in Program.cs.
Meanwhile, back in Program.cs, we also register the FredsCarsDbContext service using the generic AddDbContext method.
builder.Services.AddDbContext<FredsCarsDbContext>(opts =>
{
opts.UseSqlServer(
builder.Configuration["ConnectionStrings:FredsCarsMvcConnection"]
);
});This can be read as AddDbContext of type T where T is FredsCarsDbContext.
The options parameter of the method, named opts, is a DbContextOptionsBuilder. Again, we use the UseSqlServer method of the DbContextOptionsBuilder to tell the FredsCarsDbContext to use SqlServer and set the connection string from the configuration file.
So say we need a second DbContext called FCMySqlDbContext to work with data from an old database; A scenario where the old database was migrated from MySql to SqlServer. But, we still have tables in the old DB we need to get data from.
First, we would need to install the Entity Framework Core data provider for MySql just as we did for SqlServer.
dotnet add package Pomelo.EntityFrameworkCore.MySql --version 9.0.0-preview.2.efcore.9.0.0The latest version of the data provider for MySql for .Net 9 is still in preview mode.
Next, we define a DbContext class called FCMySqlDbContext where type T in DbContextOptions is of type FCMySqlDbContext.
public class FCMySqlDbContext : DbContext
{
public FCMySqlDbContext(DbContextOptions<FCMySqlDbContext> options)
: base(options)
{ }
public DbSet<Feature> Features => Set<Feature>();
}Here imagine the Features DbSet is pointing to a list of features a Vehicle can have in the old database such as GPS, heated steering wheel, and so forth.
Finally, we register the second DbContext, FCMySqlDbContext, in Program.cs.
builder.Services.AddDbContext<FCMySqlDbContext>(opts =>
{
opts.UseMySql(
builder.Configuration["ConnectionStrings:FCMySqlConnection"]
);
});So, we can see that DbContextOptions<T> and builder.Services.AddDbContext<T> both are generic because they can work with multiple data types, in this case, both FredsCarsDbContext and FCMySqlDbContext.
NOTE: If you typed in the above code examples in the “C# Generics” section, delete them from your project.
Define the Connection String
When we registered the DbContext, FredsCarsDbContext, as a service in Program.cs, we read the connection string to the database using the Configuration property of the WebApplicationBuilder object. The Configuration property returns the ConfigurationManager object.
builder.Services.AddDbContext<FredsCarsDbContext>(opts =>
{
opts.UseSqlServer(
builder.Configuration["ConnectionStrings:FredsCarsMvcConnection"]
);
});The ConfigurationManager
The ConfigurationManager is used to read configuration settings like connection strings from various sources. Some of these sources are as follows:
appSettings.json: The default configuration source. Typically used to store settings for connection strings and logging.
appSettings.{Environment}.json: settings defined in appsettings.{environment}.json files override settings in the base appsettings.json file.
The three typical appsetting environment files used are: appSettings.development.json
appSettings.staging.json
apSettings.production.json
command-line Arguments:
dotnet run --ConnectionString="my-connection-string"User Secrets (during development): We will learn about User Secrets in a later module.
Environment in ASP.Net Core
Environment is a first class concept in ASP.Net Core. ASP.Net Core comes with three environments out the box: Development, Staging, and Production. It is also possible to create custom environments like Testing.
When we run our application from a console window using the dotnet run command, the feedback tells us what environment we are running in.
In the above screenshot we see that we are running in the Development environment on our development machines.
However, we could run our application in another environment using the environment switch in the dotnet run command.
dotnet run --environment ProductionThe dotnet run command runs our project in Development by default because that is what is defined in the launchSettings.json file.
FredsCars/Properties/launchSettngs.json
{
"$schema": "https://json.schemastore.org/launchsettings.json",
"profiles": {
"http": {
"commandName": "Project",
"dotnetRunMessages": true,
"launchBrowser": true,
"applicationUrl": "http://localhost:40080",
"environmentVariables": {
"ASPNETCORE_ENVIRONMENT": "Development"
}
},
"https": {
"commandName": "Project",
"dotnetRunMessages": true,
"launchBrowser": true,
"applicationUrl": "https://localhost:40443;http://localhost:40080",
"environmentVariables": {
"ASPNETCORE_ENVIRONMENT": "Development"
}
}
}
}
You could also set the ASPNETCORE_ENVIRONMENT environment variable on the server in Windows in the Environment Variables.
The following is a summary of precedence of where the environment is set.
Summary of Precedence:
- Environment Variable (
ASPNETCORE_ENVIRONMENT) — highest precedence. - Launch Settings (
launchSettings.json) — used if the environment variable is not set. - Programmatically set values — lower precedence than the environment variable.
- Default —
Productionif no other values are set.
Create appsettings.development.json
As we just learned, we can set up an appsettings.json file specific to any environment the application is running in. So, next we need to create an appsettings.development.json file so it can point to our development database. Then when we deploy the application we can set a different connection string in either an appsettings.production.json file or the base appsettings.json file to point to our production database.
Right click on the FredsCars project in Solution Explorer and select Add -> New Item....
In the Add New Item dialogue, search for “App Settings File”, select the App Settings File result in the middle pane, name the file appsettings.development.json, and click the add button.
You can see the newly created json file in Solution Explorer. Notice how appsettings.development.json is nested under the base appsettings.json file.
Add the connection string setting
Modify the contents of appsettings.develoment.json.
*Make sure the connection string is all on one line. If you break the string into multiple lines you will get an error.
FredsCars/appsettings.develpment.json
{
"Logging": {
"LogLevel": {
"Default": "Information",
"Microsoft.AspNetCore": "Warning"
}
},
"AllowedHosts": "*",
"ConnectionStrings": {
"FredsCarsMvcConnection": "Server=(localdb)\\MSSQLLocalDB;Database=FredsCarsMvc;Trusted_Connection=True;MultipleActiveResultSets=true"
}
}In the configuration code above, we have added a ConnectionStrings section with one connection string, FredsCarsMvcConnection.
The Server keyword points to localdb\\MSSQLLocalDB.
NOTE: Sometimes during the SQL Server and Visual Studio installs, your localdb database may get named differently for one reason or another. You can double check the name of your localdb server in SSOX in Visual Studio.
Escape characters in C#
There are two backslashes (\\) in this string because in C# the backslash (\) is the escape character. It is used to represent special characters in a string. So, the backslash character itself also needs to be escaped.
Here are some common escape sequences in C#:
\n– New line\r– Carriage return\t– Tab\\– Backslash\'– Single quote\"– Double quote
LocalDb
LocalDB is a lightweight version of Microsoft SQL Server, specifically designed for developers. It is primarily used for development and testing purposes because it provides a simple way to work with SQL Server databases on local machines without needing to install a full version of SQL Server.
When using LocalDB, you can create and migrate your database using EF Core commands such as dotnet ef migrations add and dotnet ef database update (which we will see shortly).
LocalDB runs in user mode and doesn’t require administrative privileges to install or manage.
The Database keyword in the connection string points to a database called FredsCarsMvc. So when we create the database, Entity Framework Core will create a database with that name, FredsCarsMvc.
"ConnectionStrings": {
"FredsCarsMvcConnection": "Server=(localdb)\\MSSQLLocalDB;Database=FredsCarsMvc;Trusted_Connection=True;MultipleActiveResultSets=true"
}Add the Initial Migration
Now that we have installed the EF Core packages (including our database provider for SQL Server), installed the EF Core tools, and configured all the plumbing for Entity Framework Core, we are finally ready to create the database. Entity Framework Core will use the C# POCO classes (or entity classes) we created for Vehicle and VehicleType to design the schema of the database. It does this using Migrations.
Migrations in Entity Framework Core are a way to define the database schema based on the model of the application defined with C# entity classes. They allow developers to define and apply changes to the database schema as their application evolves.
This is particularly useful when you are working with code-first development, where the database schema is generated based on the structure of your C# classes (entity models).
We are using code-first development in this book as opposed to database-first development (where a database already exists).
Open a console window, point the command prompt to the FredsCars project folder, and run the following command.
dotnet ef migrations add InitialIn the above command we are using the dotnet-ef tools to add our first migration called Initial. We could name the migration anything we want but, it is common convention to name the first migration Initial.
Once the command completes, there is a new folder under the FredsCars project called Migrations with two new files.
The first file is the migration we just created. It’s file name will be a date-time stamp followed by an underscore character (‘_’) and then what we named the migration, in this case, Initial.
yyyyddmm[time]_Initial.cs
If you open up the Initial migration C# code file you see a method called ‘Up‘ which creates a database table called VehicleTypes and a table called Vehicles based off of our entity classes in our model.
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "VehicleTypes",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column<string>(type: "nvarchar(max)", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_VehicleTypes", x => x.Id);
});
migrationBuilder.CreateTable(
name: "Vehicles",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Status = table.Column<int>(type: "int", nullable: false),
Year = table.Column<string>(type: "nvarchar(max)", nullable: false),
Make = table.Column<string>(type: "nvarchar(max)", nullable: false),
Model = table.Column<string>(type: "nvarchar(max)", nullable: false),
Color = table.Column<string>(type: "nvarchar(max)", nullable: false),
Price = table.Column<double>(type: "float", nullable: false),
VIN = table.Column<string>(type: "nvarchar(max)", nullable: false),
VehicleTypeId = table.Column<int>(type: "int", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Vehicles", x => x.Id);
table.ForeignKey(
name: "FK_Vehicles_VehicleTypes_VehicleTypeId",
column: x => x.VehicleTypeId,
principalTable: "VehicleTypes",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateIndex(
name: "IX_Vehicles_VehicleTypeId",
table: "Vehicles",
column: "VehicleTypeId");
}There is also a ‘Down‘ method to reverse the migration if we make a mistake. The Down method in this file drops ( or removes) the newly created tables.
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "Vehicles");
migrationBuilder.DropTable(
name: "VehicleTypes");
}To run the Down method, from the command line we would run
// removes the last migration
dotnet ef migrations removeThe second file is a snapshot of the database schema and is named [DbContextName]ModelSnapshot.cs, in this case FredsCarsDbContextModelSnapshot.cs.
Every time we run the application, it will check if the Model matches the current snapshot, if not it will throw an error. I find a lot of times when I get this error I just forgot to run the dotnet ef database update command after creating a new migration. This command updates the database to match the model after each new migration we create. We will be using this command in one of our upcoming steps shortly.
Fluid API and OnModelCreating: Override default table names
One thing I notice when I look at the code in the Initial migration is that the table names are pluralized. They match the DbSet property names in our DbContext. The property name for our Vehicle DbSet is named Vehicles and the property name for VehicleType DbSet is VehicleTypes.
This makes sense from a coding stand point. Once we get to coding, it will make more sense to search for a single Vehicle from a property named Vehicles (plural) then from a collection called Vehicle (singular). The default behavior of Entity Framework Core, however, is to name the tables after the property name of a DbSet, not the entity class type it represents. A lot of developers, including myself, prefer database table names to be singular rather then plural. This is not mandatory or a convention but rather just a preference.
In the following steps, let’s remove the original Initial migration, modify FredsCarsDbContext with the EF Core Fluid API to name the database tables in a singularized fashion, recreate the Initial migration, and finally create our database.
In a console window with the command prompt pointing to your FredsCars project folder, run the following command.
dotnet ef migrations removeThe feedback in the console window will tell us that the Initial migration file and the model snapshot is being removed. Also the migrations folder will be removed from the FredsCars project in Solution Explorer.
The ef migrations remove command removes the last migration. Since we only had one migration, both files and the migrations folder itself was removed.
Open the FredsCarsDbContext class and modify it with the code below.
using FredsCars.Models;
using Microsoft.EntityFrameworkCore;
namespace FredsCars.Data
{
public class FredsCarsDbContext : DbContext
{
public FredsCarsDbContext(DbContextOptions<FredsCarsDbContext> options)
: base(options)
{}
public DbSet<Vehicle> Vehicles => Set<Vehicle>();
public DbSet<VehicleType> VehicleTypes => Set<VehicleType>();
protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity<Vehicle>().ToTable("Vehicle");
modelBuilder.Entity<VehicleType>().ToTable("VehicleType");
}
}
}In the above code, we are overriding a method called OnModelCreating from the base DbContext class in the child FredsCarsDbContext class.
We override the default behavior of OnModelCreating (using the C# override keyword) to further configure the model that was discovered by convention from the entity types exposed in our DbSet properties of FredsCarsDbContext.
The OnModelCreating method takes in a ModelBuilder object via DI and we use that to configure what the database table names for our entity types are to be. And, here we are specifying to name them in a singular fashion using literal strings in the ToTable method of the Entity property of the ModelBuilder object.
modelBuilder.Entity<Vehicle>().ToTable("Vehicle");
modelBuilder.Entity<VehicleType>().ToTable("VehicleType");These two lines of code are an example of using the Fluid API in Entity Framework Core.
The Fluent API in Entity Framework Core is a powerful way to configure the behavior of entity models and relationships using code. As we just saw, it provides a way to specify model configuration by overriding the OnModelCreating method in the application’s DbContext class. This approach is often used as an alternative or complement to using data annotations directly in your entity classes. We will see examples of data annotations later when get into model validation.
Ok, now that we have fixed the table names in our model, let’s recreate the Initial migration.
Run the following command again in the console window.
dotnet ef migrations add InitialThe Migrations folder, the Initial migration, and the snapshot are all recreated in the FredsCars project. Open up the Initial migration C# class file and let’s inspect it one more time to see the changes.
FredsCars/Migrations/20250102134539_Initial.cs (Your timestamp will be different in the filename in your project.)
using Microsoft.EntityFrameworkCore.Migrations;
#nullable disable
namespace FredsCars.Migrations
{
/// <inheritdoc />
public partial class Initial : Migration
{
/// <inheritdoc />
protected override void Up(MigrationBuilder migrationBuilder)
{
migrationBuilder.CreateTable(
name: "VehicleType",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Name = table.Column<string>(type: "nvarchar(max)", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_VehicleType", x => x.Id);
});
migrationBuilder.CreateTable(
name: "Vehicle",
columns: table => new
{
Id = table.Column<int>(type: "int", nullable: false)
.Annotation("SqlServer:Identity", "1, 1"),
Status = table.Column<int>(type: "int", nullable: false),
Year = table.Column<string>(type: "nvarchar(max)", nullable: false),
Make = table.Column<string>(type: "nvarchar(max)", nullable: false),
Model = table.Column<string>(type: "nvarchar(max)", nullable: false),
Color = table.Column<string>(type: "nvarchar(max)", nullable: false),
Price = table.Column<double>(type: "float", nullable: false),
VIN = table.Column<string>(type: "nvarchar(max)", nullable: false),
VehicleTypeId = table.Column<int>(type: "int", nullable: false)
},
constraints: table =>
{
table.PrimaryKey("PK_Vehicle", x => x.Id);
table.ForeignKey(
name: "FK_Vehicle_VehicleType_VehicleTypeId",
column: x => x.VehicleTypeId,
principalTable: "VehicleType",
principalColumn: "Id",
onDelete: ReferentialAction.Cascade);
});
migrationBuilder.CreateIndex(
name: "IX_Vehicle_VehicleTypeId",
table: "Vehicle",
column: "VehicleTypeId");
}
/// <inheritdoc />
protected override void Down(MigrationBuilder migrationBuilder)
{
migrationBuilder.DropTable(
name: "Vehicle");
migrationBuilder.DropTable(
name: "VehicleType");
}
}
}Ah yes, I like what I see. The table names for the Vehicle and VehicleType entities will now be created in singular fashion.
Update the Database
Anytime we create a new migration, we need to then run the ef database update command.
The dotnet ef database update command is used in Entity Framework Core to apply pending migrations to the database. It updates the database schema to match the current model and migration files.
Let’s go ahead and run that command now to create our database. Type the following command in a console window with the command prompt pointing to the FredsCars project folder and hit Enter.
dotnet ef database updateOnce the command completes, there will be two new files in your users folder in windows located at c:\Users\[your-username].
FredsCarsMvc.mdf: This is the actual SqlServer file. MDF (Master Database File) is the primary file format used by Microsoft SQL Server.FredsCarsMvc_log.ldf: The transaction log file that records database changes.
Inspect the Database
After updating the database for the first time for our first migration the database was created in the files listed above. We can inspect the database using tools right in the Visual Studio IDE using the SQL Server Object Explorer (SSOX).
To open the SSOX, click View in the upper menu and select SQL Server Object Explorer.
In the SSOX window expand the (localdb)\MSSQLLocalDB database server node. Under it expand the Databases folder, the FredsCarsMvc database icon node, and then the Tables folder.
There we can see the two database tables that were created to represent and correspond to the entity classes in our application Model, Vehicle and VehcileType.
Inspect the VehicleType table
Right click on the VehicleType table and select View Designer.
A new tab will open in Visual Studio called dbo.VehicleType [Design].
In the screenshot above, you can see the column names of the table in the upper left pane match the property names of the VehicleType entity class in our application’s model; Id and Name.
To the left of the Id column is an icon that looks like a key. This signifies that Id is the primary key of the table. The values in this column are unique to each row. So it can be used to search for a VehicleType. The Id column is also marked as an integer, so the primary key values will be 1, 2, 3, and so on. For instance, if we search the VehicleType table for a row where the VehicleType’s Id is 2, we may get back a record where the Name column value is “Truck”. Id is unique because no other row will have an Id with a value of 2.
Notice, the Name column is of type nvarchar(MAX). This is the SQL Server datatype that matches up with a C# string datatype. So, the string Name property in the VehicleType entity class was assigned the nvarchar(MAX) SQL Server datatype in the table when Entity Framework Core created it.
In the upper right pane the Keys, Constraints, and other concepts we will cover as we go are broken out. Once again the Id column is shown to be the primary key here.
The bottom pane shows the SQL Server CREATE TABLE statement that was used to create the table. The great thing about Entity Framework Core is that we don’t need to know SQL. As you can see EF Core generates the SQL for us. But it definitely helps to know a little SQL to understand what is going on behind the scenes and to help with troubleshooting and performance optimization if need be. A good place to start learning SQL is w3schools.
Inspect the Vehicle table
Right click on the Vehicle table and select View Designer.
A new tab will open in Visual Studio called dbo.Vehicle [Design].
Again there will be three panes in the tab window.
The upper left pane shows the Vehicle table’s column names and SQL Server datatypes that match up with the Vehicle entity class property names.
Again, the Id column is a unique integer marked as the primary key by the key icon.
The Status column uses an SQL Server int datatype to represent the Status enum type back in the Vehicle entity class in C#.
public enum Status
{
New, // Value of 0
Used // Value of 1
}In C#, enums are 0-based by default. The first enumerator in an enum has the value 0 unless explicitly assigned a different value.
The Year, Make, Model, and Color columns are all SQL Server types of nvarchar(MAX) to match their C# counterpart string properties.
The Price column has an SQL Server datatype of float to match the C# double datatype of the Price property in the Vehicle entity class.
The VIN column is an nvarchar(MAX) to match a C# string.
The VehicleTypeId column has an int SQL Server datatype and is a Foreign Key to the VehicleType table.
a Foreign Key is a column (or a set of columns) in one table that establishes a relationship between data in two tables. It acts as a constraint, ensuring that the value in the foreign key column matches a value in the primary key column of another table or is null. This helps maintain referential integrity between the two tables.
So when we are coding, and we want to know what category (or vehicle type) our vehicle is, the VehicleTypeId column is how we find out (or how Entity Framework Core figures it out).
The upper right pane shows the table’s Keys, Check Constraints, Indexes, Foreign Keys and Triggers.
There is one Key, the table’s primary key on the Id column named PK_Vehicle.
There is one Index named IX_Vehicle_VehicleTypeId on the VehicleTypeId column. Indexes optimize searching in databases like SQL Server.
There is one Foreign Key called FK_Vehicle_VehicleType_VehicleTypeId. You can see from the naming convention that this is a foreign key (FK) that creates a relationship from the Vehicle table to the VehicleType table using the VehicleTypeId column in the principal Vehicle table to find the correct Id column in the secondary VehicleType table.
The bottom pane shows the SQL that was used to create the Vehicle table.
CREATE TABLE [dbo].[Vehicle] (
[Id] INT IDENTITY (1, 1) NOT NULL,
[Status] INT NOT NULL,
[Year] NVARCHAR (MAX) NOT NULL,
[Make] NVARCHAR (MAX) NOT NULL,
[Model] NVARCHAR (MAX) NOT NULL,
[Color] NVARCHAR (MAX) NOT NULL,
[Price] FLOAT (53) NOT NULL,
[VIN] NVARCHAR (MAX) NOT NULL,
[VehicleTypeId] INT NOT NULL,
CONSTRAINT [PK_Vehicle] PRIMARY KEY CLUSTERED ([Id] ASC),
CONSTRAINT [FK_Vehicle_VehicleType_VehicleTypeId] FOREIGN KEY ([VehicleTypeId]) REFERENCES [dbo].[VehicleType] ([Id]) ON DELETE CASCADE
);
GO
CREATE NONCLUSTERED INDEX [IX_Vehicle_VehicleTypeId]
ON [dbo].[Vehicle]([VehicleTypeId] ASC);Database Relationships
When we inspected the two database tables above, we looked at the relationship between the two tables created by the foreign key column (the VehicleTypeId in the Vehicle table) pointing to the Id column in the VehicleType table.
In database speak, this is called a one-to-many relationship.
In our design, one VehicleType can be assigned to many Vehicles. But each Vehicle can only have one VehicleType.
This is visualized in the diagram below. In the diagram, the ‘1’ next to the VehicleType table signifies the ‘one’ side of the relationship. The Asterix character next to the Vehicle table, ‘*‘, signifies the ‘many’ side of the relationship.
There are three types of relationships in relational databases like SQL Server.
A relational database is a type of database that organizes and stores data in a structured format using tables, which consist of rows and columns. Each table represents a specific entity (e.g., customers, products) and contains data attributes (columns) and records (rows). The core concept behind relational databases is the use of relations to establish meaningful connections between data.
One-to-One: Each record in Table A corresponds to one record in Table B. And, each record in Table B corresponds to one record in Table A.
One-to-Many: Each record in Table A corresponds to multiple records in Table B. And each record in Table B corresponds to one record in Table A.
The one VehicleType to many Vehicles relationship we just looked at above is an example of a one-to-many relationship.
Many-to-Many: Records in Table A correspond to multiple records in Table B, and vice versa (usually implemented via a junction or bridge table).
Summary of Steps
We covered a lot of ground in this chapter. But, I don’t want the simplicity of the steps we just covered to get lost in the weeds.
So, here is a summary of the steps we followed to setup Entity Framework Core and create the database.
- Install the EF Core packages.
- Install the dotnet ef tools package globally.
- Create the DbContext. (Use EF Core Fluent API to modify table names in the DbContext OnModelCreating method if you prefer.)
- Register the DbContext.
- Create the database connection string in appsettings.development.json.
- Add the first migration using the
dotnet ef migrations addcommand. - Run the
ef database updatecommand to create the database.
What’s Next
In this module we set up and configured EF Core and created the database.
In the next module we are going to load the database with some test data so we have some Vehicles and VehicleType records to play with as we start to develop the application features.