aspnetcore/tutorials/razor-pages/sql.md
By Joe Audette
:::moniker range=">= aspnetcore-10.0"
The RazorPagesMovieContext object handles the task of connecting to the database and mapping Movie objects to database records. Register the database context with the Dependency Injection container in Program.cs:
The ASP.NET Core Configuration system reads the ConnectionString key. For local development, the configuration gets the connection string from the appsettings.json file.
The generated connection string is similar to the following JSON:
[!INCLUDE managed-identities-test-non-production]
LocalDB is a lightweight version of the SQL Server Express database engine that's designed for program development. LocalDB starts on demand and runs in user mode, so there's no complex configuration. By default, LocalDB creates *.mdf database files in the C:\Users\<user>\ directory.
From the View menu, open SQL Server Object Explorer (SSOX).
:::image type="content" source="~/tutorials/razor-pages/sql/media/sql-server-object-explorer-vs2026.png" alt-text="View menu showing SQL Server Object Explorer option.":::
Right-click on the Movie table and select View Designer:
:::image type="content" source="~/tutorials/razor-pages/sql/media/view-designer-vs2026.png" alt-text="Contextual menus open on Movie table.":::
:::image type="content" source="~/tutorials/razor-pages/sql/media/movie-table-in-designer-vs2026.png" alt-text="Movie tables open in Designer.":::
Note the key icon next to ID. By default, EF creates a property named ID for the primary key.
Right-click on the Movie table and select View Data:
:::image type="content" source="~/tutorials/razor-pages/sql/media/view-movie-data-vs2026.png" alt-text="Movie table open showing table data.":::
The SQLite website states:
SQLite is a self-contained, high-reliability, embedded, full-featured, public-domain, SQL Database engine. SQLite is the most used database engine in the world.
You can download many third-party tools to manage and view a SQLite database. The following image is from DB Browser for SQLite. If you have a favorite SQLite tool, leave a comment on what you like about it.
:::image type="content" source="~/tutorials/first-mvc-app-xplat/working-with-sql/_static/dbb.png" alt-text="DB Browser for SQLite showing movie database.":::
[!NOTE] For this tutorial, 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 make 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 isn't supported. If you create a migration to remove or change a column, the
ef migrations addcommand succeeds but theef database updatecommand fails. Due to these limitations, this tutorial doesn't use migrations for SQLite schema changes. Instead, when the schema changes, the database is dropped and re-created.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:
Create a new class named SeedData in the Models folder with the following code:
If the database contains any movies, the seed initializer returns and doesn't add any movies.
if (context.Movie.Any())
{
return;
}
<a name="si"></a>
Update Program.cs with the following highlighted code:
In the preceding code, you modify Program.cs to do the following steps:
seedData.Initialize method, passing the database context instance.The following exception occurs when you don't run Update-Database:
SqlException: Cannot open database "RazorPagesMovieContext-" requested by the login. The login failed.Login failed for user 'user name'.
Delete all the records in the database so the seed method runs. Stop and start the app to seed the database. If the database isn't seeded, put a breakpoint on if (context.Movie.Any()) and step through the code.
The app shows the seeded data:
:::image type="content" source="~/tutorials/razor-pages/sql/media/seed-data-in-app.png" alt-text="Movie application open in browser showing movie data.":::
[!div class="step-by-step"] Previous: Scaffolded Razor Pages Next: Update the pages
:::moniker-end