title | author | description | ms.author | ms.custom | ms.date | uid |
---|---|---|---|---|---|---|
Part 7, add a new field |
rick-anderson |
Part 7 of tutorial series on Razor Pages. |
riande |
mvc, contperf-fy21q2 |
09/28/2020 |
tutorials/razor-pages/new-field |
:::moniker range="= aspnetcore-5.0"
View or download sample code (how to download).
In this section Entity Framework Code First Migrations is used to:
- Add a new field to the model.
- Migrate the new field schema change to the database.
When using EF Code First to automatically create a database, Code First:
- Adds an
__EFMigrationsHistory
table to the database to track whether the schema of the database is in sync with the model classes it was generated from. - If the model classes aren't in sync with the database, EF throws an exception.
Automatic verification that the schema and model are in sync makes it easier to find inconsistent database code issues.
-
Open the
Models/Movie.cs
file and add aRating
property: -
Build the app.
-
Edit
Pages/Movies/Index.cshtml
, and add aRating
field: -
Update the following pages with a
Rating
field:
The app won't work until the database is updated to include the new field. Running the app without an update to the database throws a SqlException
:
SqlException: Invalid column name 'Rating'.
The SqlException
exception is caused by the updated Movie model class being different than the schema of the Movie table of the database. There's no Rating
column in the database table.
There are a few approaches to resolving the error:
-
Have the Entity Framework automatically drop and re-create the database using the new model class schema. This approach is convenient early in the development cycle, it allows you to quickly evolve the model and database schema together. The downside is that you lose existing data in the database. Don't use this approach on a production database! Dropping the database on schema changes and using an initializer to automatically seed the database with test data is often a productive way to develop an app.
-
Explicitly modify the schema of the existing database so that it matches the model classes. The advantage of this approach is to keep the data. Make this change either manually or by creating a database change script.
-
Use Code First Migrations to update the database schema.
For this tutorial, use Code First Migrations.
Update the SeedData
class so that it provides a value for the new column. A sample change is shown below, but make this change for each new Movie
block.
See the completed SeedData.cs file.
Build the solution.
-
From the Tools menu, select NuGet Package Manager > Package Manager Console.
-
In the PMC, enter the following commands:
Add-Migration Rating Update-Database
The Add-Migration
command tells the framework to:
- Compare the
Movie
model with theMovie
database schema. - Create code to migrate the database schema to the new model.
The name "Rating" is arbitrary and is used to name the migration file. It's helpful to use a meaningful name for the migration file.
The Update-Database
command tells the framework to apply the schema changes to the database and to preserve existing data.
Delete all the records in the database, the initializer will seed the database and include the Rating
field. Deleting can be done with the delete links in the browser or from Sql Server Object Explorer (SSOX).
Another option is to delete the database and use migrations to re-create the database. To delete the database in SSOX:
-
Select the database in SSOX.
-
Right-click on the database, and select Delete.
-
Check Close existing connections.
-
Select OK.
-
In the PMC, update the database:
Update-Database
Use the following commands to add a migration for the rating field:
dotnet ef migrations add rating
dotnet ef database update
The dotnet-ef migrations add rating
command tells the framework to:
- Compare the
Movie
model with theMovie
database schema. - Create code to migrate the database schema to the new model.
The name rating
is arbitrary and is used to name the migration file. It's helpful to use a meaningful name for the migration file.
The dotnet ef database update
command tells the framework to apply the schema changes to the database and to preserve existing data.
Delete all the records in the database, the initializer will seed the database and include the Rating
field.
Skip this section if you successfully migrated the database.
In this tutorial, Entity Framework Core migrations features are used when possible. Migrations updates the database schema to match changes in the data model. However, migrations can only do the kinds of changes that the EF Core provider supports, and some provider's capabilities are limited. For example, adding a column may be supported, but removing or changing a column is not. If a migration is created to remove or change a column, the ef migrations add
command succeeds but the ef database update
command fails. Due to these limitations, you can drop and re-create the database.
The workaround for the limitations is to manually write migrations code to perform a table rebuild when something in the table changes. A table rebuild involves:
- Creating a new table.
- Copying data from the old table to the new table.
- Dropping the old table.
- Renaming the new table.
For more information, see the following resources:
- SQLite EF Core Database Provider Limitations
- Customize migration code
- Data seeding
- SQLite ALTER TABLE statement
-
Delete the migration folder.
-
Use the following commands to recreate the database.
dotnet ef database drop dotnet ef migrations add InitialCreate dotnet ef database update
Run the app and verify you can create/edit/display movies with a Rating
field. If the database isn't seeded, set a break point in the SeedData.Initialize
method.
[!div class="step-by-step"] Previous: Add Search Next: Add Validation
:::moniker-end
:::moniker range="< aspnetcore-5.0"
View or download sample code (how to download).
In this section Entity Framework Code First Migrations is used to:
- Add a new field to the model.
- Migrate the new field schema change to the database.
When using EF Code First to automatically create a database, Code First:
- Adds an
__EFMigrationsHistory
table to the database to track whether the schema of the database is in sync with the model classes it was generated from. - If the model classes aren't in sync with the database, EF throws an exception.
Automatic verification that the schema and model are in sync makes it easier to find inconsistent database code issues.
-
Open the
Models/Movie.cs
file and add aRating
property: -
Build the app.
-
Edit
Pages/Movies/Index.cshtml
, and add aRating
field: -
Update the following pages with a
Rating
field:
The app won't work until the database is updated to include the new field. Running the app without an update to the database throws a SqlException
:
SqlException: Invalid column name 'Rating'.
The SqlException
exception is caused by the updated Movie model class being different than the schema of the Movie table of the database. There's no Rating
column in the database table.
There are a few approaches to resolving the error:
-
Have the Entity Framework automatically drop and re-create the database using the new model class schema. This approach is convenient early in the development cycle, it allows you to quickly evolve the model and database schema together. The downside is that you lose existing data in the database. Don't use this approach on a production database! Dropping the database on schema changes and using an initializer to automatically seed the database with test data is often a productive way to develop an app.
-
Explicitly modify the schema of the existing database so that it matches the model classes. The advantage of this approach is to keep the data. Make this change either manually or by creating a database change script.
-
Use Code First Migrations to update the database schema.
For this tutorial, use Code First Migrations.
Update the SeedData
class so that it provides a value for the new column. A sample change is shown below, but make this change for each new Movie
block.
See the completed SeedData.cs file.
Build the solution.
-
From the Tools menu, select NuGet Package Manager > Package Manager Console.
-
In the PMC, enter the following commands:
Add-Migration Rating Update-Database
The Add-Migration
command tells the framework to:
- Compare the
Movie
model with theMovie
database schema. - Create code to migrate the database schema to the new model.
The name "Rating" is arbitrary and is used to name the migration file. It's helpful to use a meaningful name for the migration file.
The Update-Database
command tells the framework to apply the schema changes to the database and to preserve existing data.
If you delete all the records in the database, the initializer will seed the database and include the Rating
field. You can do this with the delete links in the browser or from Sql Server Object Explorer (SSOX).
Another option is to delete the database and use migrations to re-create the database. To delete the database in SSOX:
-
Select the database in SSOX.
-
Right-click on the database, and select Delete.
-
Check Close existing connections.
-
Select OK.
-
In the PMC, update the database:
Update-Database
Note
For this tutorial you, use the Entity Framework Core migrations feature where possible. Migrations updates the database schema to match changes in the data model. However, migrations can only do the kinds of changes that the EF Core provider supports, and the SQLite provider's capabilities are limited. For example, adding a column is supported, but removing or changing a column is not supported. If a migration is created to remove or change a column, the ef migrations add
command succeeds but the ef database update
command fails. Due to these limitations, this tutorial doesn't use migrations for SQLite schema changes. Instead, when the schema changes, you drop and re-create the database.
The workaround for the SQLite limitations is to manually write migrations code to perform a table rebuild when something in the table changes. A table rebuild involves:
- Creating a new table.
- Copying data from the old table to the new table.
- Dropping the old table.
- Renaming the new table.
For more information, see the following resources:
-
Delete the migration folder.
-
Use the following commands to recreate the database.
dotnet ef database drop dotnet ef migrations add InitialCreate dotnet ef database update
Run the app and verify you can create/edit/display movies with a Rating
field. If the database isn't seeded, set a break point in the SeedData.Initialize
method.
[!div class="step-by-step"] Previous: Add Search Next: Add Validation
:::moniker-end
:::moniker range=">= aspnetcore-6.0"
In this section Entity Framework Code First Migrations is used to:
- Add a new field to the model.
- Migrate the new field schema change to the database.
When using EF Code First to automatically create and track a database, Code First:
- Adds an
__EFMigrationsHistory
table to the database to track whether the schema of the database is in sync with the model classes it was generated from. - Throws an exception if the model classes aren't in sync with the database.
Automatic verification that the schema and model are in sync makes it easier to find inconsistent database code issues.
-
Open the
Models/Movie.cs
file and add aRating
property: [!code-csharp] -
Edit
Pages/Movies/Index.cshtml
, and add aRating
field: [!code-cshtml] -
Update the following pages with a
Rating
field:
The app won't work until the database is updated to include the new field. Running the app without an update to the database throws a SqlException
:
SqlException: Invalid column name 'Rating'.
The SqlException
exception is caused by the updated Movie model class being different than the schema of the Movie table of the database. There's no Rating
column in the database table.
There are a few approaches to resolving the error:
- Have the Entity Framework automatically drop and re-create the database using the new model class schema. This approach is convenient early in the development cycle, it allows developers to quickly evolve the model and database schema together. The downside is that existing data in the database is lost. Don't use this approach on a production database! Dropping the database on schema changes and using an initializer to automatically seed the database with test data is often a productive way to develop an app.
- Explicitly modify the schema of the existing database so that it matches the model classes. The advantage of this approach is to keep the data. Make this change either manually or by creating a database change script.
- Use Code First Migrations to update the database schema.
For this tutorial, use Code First Migrations.
Update the SeedData
class so that it provides a value for the new column. A sample change is shown below, but make this change for each new Movie
block.
See the completed SeedData.cs file.
Build the solution.
-
From the Tools menu, select NuGet Package Manager > Package Manager Console.
-
In the PMC, enter the following commands:
Add-Migration Rating Update-Database
The Add-Migration
command tells the framework to:
- Compare the
Movie
model with theMovie
database schema. - Create code to migrate the database schema to the new model.
The name "Rating" is arbitrary and is used to name the migration file. It's helpful to use a meaningful name for the migration file.
The Update-Database
command tells the framework to apply the schema changes to the database and to preserve existing data.
Delete all the records in the database, the initializer will seed the database and include the Rating
field. Deleting can be done with the delete links in the browser or from Sql Server Object Explorer (SSOX).
Another option is to delete the database and use migrations to re-create the database. To delete the database in SSOX:
-
Select the database in SSOX.
-
Right-click on the database, and select Delete.
-
Check Close existing connections.
-
Select OK.
-
In the PMC, update the database:
Update-Database
Use the following commands to add a migration for the rating field:
dotnet ef migrations add rating
dotnet ef database update
The dotnet-ef migrations add rating
command tells the framework to:
- Compare the
Movie
model with theMovie
database schema. - Create code to migrate the database schema to the new model.
The name rating
is arbitrary and is used to name the migration file. It's helpful to use a meaningful name for the migration file.
The dotnet-ef database update
command tells the framework to apply the schema changes to the database and to preserve existing data.
Delete all the records in the database, the initializer will seed the database and include the Rating
field.
Skip this section if you successfully migrated the database.
In this tutorial, Entity Framework Core migrations features are used when possible. Migrations updates the database schema to match changes in the data model. However, migrations can only do the kinds of changes that the EF Core provider supports, and some provider's capabilities are limited. For example, adding a column may be supported, but removing or changing a column is not. If a migration is created to remove or change a column, the ef migrations add
command succeeds but the ef database update
command fails. Due to these limitations, you can drop and re-create the database.
The workaround for the limitations is to manually write migrations code to perform a table rebuild when something in the table changes. A table rebuild involves:
- Creating a new table.
- Copying data from the old table to the new table.
- Dropping the old table.
- Renaming the new table.
For more information, see the following resources:
- SQLite EF Core Database Provider Limitations
- Customize migration code
- Data seeding
- SQLite ALTER TABLE statement
-
Delete the migration folder.
-
Use the following commands to recreate the database.
dotnet ef database drop dotnet ef migrations add InitialCreate dotnet ef database update
Run the app and verify you can create, edit, and display movies with a Rating
field. If the database isn't seeded, set a break point in the SeedData.Initialize
method.
[!div class="step-by-step"] Previous: Add Search Next: Add Validation
:::moniker-end