.NET Aspire SQL Server Entity Framework Core integration
Artikkeli
Includes:
Hosting integration and
Client integration
SQL Server is a relational database management system developed by Microsoft. The .NET Aspire SQL Server Entity Framework Core integration enables you to connect to existing SQL Server instances or create new instances from .NET with the mcr.microsoft.com/mssql/server container image.
In your app host project, call AddSqlServer to add and return a SQL Server resource builder. Chain a call to the returned resource builder to AddDatabase, to add SQL Server database resource.
C#
var builder = DistributedApplication.CreateBuilder(args);
var sql = builder.AddSqlServer("sql")
.WithLifetime(ContainerLifetime.Persistent);
var db = sql.AddDatabase("database");
builder.AddProject<Projects.ExampleProject>()
.WithReference(db)
.WaitFor(db);
// After adding all resources, run the app...
Huomautus
The SQL Server container is slow to start, so it's best to use a persistent lifetime to avoid unnecessary restarts. For more information, see Container resource lifetime.
When .NET Aspire adds a container image to the app host, as shown in the preceding example with the mcr.microsoft.com/mssql/server image, it creates a new SQL Server instance on your local machine. A reference to your SQL Server resource builder (the sql variable) is used to add a database. The database is named database and then added to the ExampleProject. The SQL Server resource includes default credentials with a username of sa and a random password generated using the CreateDefaultPasswordParameter method.
When the app host runs, the password is stored in the app host's secret store. It's added to the Parameters section, for example:
To add a data volume to the SQL Server resource, call the WithDataVolume method on the SQL Server resource:
C#
var builder = DistributedApplication.CreateBuilder(args);
var sql = builder.AddSqlServer("sql")
.WithDataVolume();
var db = sql.AddDatabase("database");
builder.AddProject<Projects.ExampleProject>()
.WithReference(db)
.WaitFor(db);
// After adding all resources, run the app...
The data volume is used to persist the SQL Server data outside the lifecycle of its container. The data volume is mounted at the /var/opt/mssql path in the SQL Server container and when a name parameter isn't provided, the name is generated at random. For more information on data volumes and details on why they're preferred over bind mounts, see Docker docs: Volumes.
Varoitus
The password is stored in the data volume. When using a data volume and if the password changes, it will not work until you delete the volume.
Add SQL Server resource with data bind mount
To add a data bind mount to the SQL Server resource, call the WithDataBindMount method:
C#
var builder = DistributedApplication.CreateBuilder(args);
var sql = builder.AddSqlServer("sql")
.WithDataBindMount(source: @"C:\SqlServer\Data");
var db = sql.AddDatabase("database");
builder.AddProject<Projects.ExampleProject>()
.WithReference(db)
.WaitFor(db);
// After adding all resources, run the app...
Tärkeä
Data bind mounts have limited functionality compared to volumes, which offer better performance, portability, and security, making them more suitable for production environments. However, bind mounts allow direct access and modification of files on the host system, ideal for development and testing where real-time changes are needed.
Data bind mounts rely on the host machine's filesystem to persist the SQL Server data across container restarts. The data bind mount is mounted at the C:\SqlServer\Data on Windows (or /SqlServer/Data on Unix) path on the host machine in the SQL Server container. For more information on data bind mounts, see Docker docs: Bind mounts.
Add SQL Server resource with parameters
When you want to explicitly provide the password used by the container image, you can provide these credentials as parameters. Consider the following alternative example:
C#
var builder = DistributedApplication.CreateBuilder(args);
var password = builder.AddParameter("password", secret: true);
var sql = builder.AddSqlServer("sql", password);
var db = sql.AddDatabase("database");
builder.AddProject<Projects.ExampleProject>()
.WithReference(db)
.WaitFor(db);
// After adding all resources, run the app...
When the .NET Aspire app host runs, the server's database resources can be accessed from external tools, such as SQL Server Management Studio (SSMS) or MSSQL for Visual Studio Code. The connection string for the database resource is available in the dependent resources environment variables and is accessed using the .NET Aspire dashboard: Resource details pane. The environment variable is named ConnectionStrings__{name} where {name} is the name of the database resource, in this example it's database. Use the connection string to connect to the database resource from external tools. Imagine that you have a database named todos with a single dbo.Todos table.
The SQL Server hosting integration automatically adds a health check for the SQL Server resource. The health check verifies that the SQL Server is running and that a connection can be established to it.
To get started with the .NET Aspire SQL Server Entity Framework Core integration, install the 📦 Aspire.Microsoft.EntityFrameworkCore.SqlServer NuGet package in the client-consuming project, that is, the project for the application that uses the SQL Server Entity Framework Core client.
In the Program.cs file of your client-consuming project, call the AddSqlServerDbContext extension method on any IHostApplicationBuilder to register a DbContext for use via the dependency injection container. The method takes a connection name parameter.
The connectionName parameter must match the name used when adding the SQL Server database resource in the app host project. In other words, when you call AddDatabase and provide a name of database that same name should be used when calling AddSqlServerDbContext. For more information, see Add SQL Server resource and database resource.
To retrieve ExampleDbContext object from a service:
C#
publicclassExampleService(ExampleDbContext context)
{
// Use context...
}
You have more flexibility when you create the database context in this way, for example:
You can reuse existing configuration code for the database context without rewriting it for .NET Aspire.
You can use Entity Framework Core interceptors to modify database operations.
You can choose not to use Entity Framework Core context pooling, which may perform better in some circumstances.
If you use this method, you can enhance the database context with .NET Aspire-style retries, health checks, logging, and telemetry features by calling the EnrichSqlServerDbContext method:
The .NET Aspire SQL Server Entity Framework Core integration provides multiple configuration approaches and options to meet the requirements and conventions of your project.
Use connection string
When using a connection string from the ConnectionStrings configuration section, you provide the name of the connection string when calling builder.AddSqlServerDbContext<TContext>():
The EnrichSqlServerDbContext won't make use of the ConnectionStrings configuration section since it expects a DbContext to be registered at the point it's called.
The .NET Aspire SQL Server Entity Framework Core integration supports Microsoft.Extensions.Configuration. It loads the MicrosoftEntityFrameworkCoreSqlServerSettings from configuration files such as appsettings.json by using the Aspire:Microsoft:EntityFrameworkCore:SqlServer key. If you have set up your configurations in the Aspire:Microsoft:EntityFrameworkCore:SqlServer section you can just call the method without passing any parameter.
The following is an example of an appsettings.json file that configures some of the available options:
You can also pass the Action<MicrosoftEntityFrameworkCoreSqlServerSettings> delegate to set up some or all the options inline, for example to turn off the metrics:
If you want to register more than one DbContext with different configuration, you can use $"Aspire.Microsoft.EntityFrameworkCore.SqlServer:{typeof(TContext).Name}" configuration section name. The json configuration would look like:
Then calling the AddSqlServerDbContext method with AnotherDbContext type parameter would load the settings from Aspire:Microsoft:EntityFrameworkCore:SqlServer:AnotherDbContext section.
Here are the configurable options with corresponding default values:
Name
Description
ConnectionString
The connection string of the SQL Server database to connect to.
DbContextPooling
A boolean value that indicates whether the db context will be pooled or explicitly created every time it's requested
MaxRetryCount
The maximum number of retry attempts. Default value is 6, set it to 0 to disable the retry mechanism.
DisableHealthChecks
A boolean value that indicates whether the database health check is disabled or not.
DisableTracing
A boolean value that indicates whether the OpenTelemetry tracing is disabled or not.
DisableMetrics
A boolean value that indicates whether the OpenTelemetry metrics are disabled or not.
Timeout
The time in seconds to wait for the command to execute.
Client integration health checks
By default, .NET Aspire client integrations have health checks enabled for all services. Similarly, many .NET Aspire hosting integrations also enable health check endpoints. For more information, see:
Integrates with the /health HTTP endpoint, which specifies all registered health checks must pass for app to be considered ready to accept traffic
Observability and telemetry
.NET Aspire integrations automatically set up Logging, Tracing, and Metrics configurations, which are sometimes known as the pillars of observability. For more information about integration observability and telemetry, see .NET Aspire integrations overview. Depending on the backing service, some integrations may only support some of these features. For example, some integrations support logging and tracing, but not metrics. Telemetry features can also be disabled using the techniques presented in the Configuration section.
Logging
The .NET Aspire SQL Server Entity Framework Core integration uses the following Log categories:
Liity tapaamissarjaan ja luo skaalattavia tekoälyratkaisuja, jotka perustuvat reaalimaailman käyttötapauksiin muiden kehittäjien ja asiantuntijoiden kanssa.
Learn about the database systems that .NET Aspire can connect to using built-in integrations. Then see how to configure connections to, and store data in, relational and nonrelational databases.
Hallitse SQL Server -tietokantainfrastruktuuria pilvipalveluissa, paikallisissa tietokannoissa ja hybridirelaatiotietokannoissa Microsoft PaaS -relaatiotietokantatarjonnan avulla.