.NET Aspire SqlServer Entity Framework Core integration
In this article, you learn how to use the .NET Aspire SqlServer Entity Framework Core integration. The Aspire.Microsoft.EntityFrameworkCore.SqlServer
library is used to:
- Registers EntityFrameworkCore DbContext service for connecting to a SQL database.
- Automatically configures the following:
- Connection pooling to efficiently managed HTTP requests and database connections
- Health checks, logging and telemetry to improve app monitoring and diagnostics
Prerequisites
- SQL database and connection string for accessing the database.
Get started
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, i.e., the project for the application that uses the SQL Server Entity Framework Core client.
dotnet add package Aspire.Microsoft.EntityFrameworkCore.SqlServer
For more information, see dotnet add package or Manage package dependencies in .NET applications.
Example usage
In the Program.cs file of your integration-consuming project, call the AddSqlServerDbContext extension to register a DbContext
for use via the dependency injection container.
builder.AddSqlServerDbContext<YourDbContext>("sqldb");
To retrieve YourDbContext
object from a service:
public class ExampleService(YourDbContext client)
{
// Use client...
}
App host usage
The SQL Server hosting integration models the server as the SqlServerServerResource type and the database as the SqlServerDatabaseResource type. To access these types and APIs, add the 📦 Aspire.Hosting.SqlServer NuGet package in the app host project.
dotnet add package Aspire.Hosting.SqlServer
For more information, see dotnet add package or Manage package dependencies in .NET applications.
Add SQL Server resource and database resource
In your app host project, call AddSqlServer to add and return a SQL Server resource builder. Chain a call to the returned resource build to AddDatabase, to add SQL Server database resource.
var builder = DistributedApplication.CreateBuilder(args);
var sql = builder.AddSqlServer("sql")
.WithLifetime(ContainerLifetime.Persistent);
var db = sql.AddDatabase("database");
builder.AddProject<Projects.ExampleProject>()
.WithReference(db);
// After adding all resources, run the app...
Note
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:
{
"Parameters:sql-password": "<THE_GENERATED_PASSWORD>"
}
The name of the parameter is sql-password
, but really it's just formatting the resource name with a -password
suffix. For more information, see Safe storage of app secrets in development in ASP.NET Core and Add SQL Server resource with parameters.
The WithReference method configures a connection in the ExampleProject
named database
.
Tip
If you'd rather connect to an existing SQL Server, call AddConnectionString instead. For more information, see Reference existing resources.
Add SQL Server resource with data volume
To add a data volume to the SQL Server resource, call the WithDataVolume method on the SQL Server resource:
var builder = DistributedApplication.CreateBuilder(args);
var sql = builder.AddSqlServer("sql")
.WithDataVolume();
var db = sql.AddDatabase("database");
builder.AddProject<Projects.ExampleProject>()
.WithReference(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.
Warning
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:
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);
// After adding all resources, run the app...
Important
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:
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);
// After adding all resources, run the app...
For more information on providing parameters, see External parameters.
Connect to database resources
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 Azure Data Studio. 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.
To connect to the database resource from SQL Server Management Studio, follow these steps:
Open SSMS.
In the Connect to Server dialog, select the Additional Connection Parameters tab.
Paste the connection string into the Additional Connection Parameters field and select Connect.
If you're connected, you can see the database resource in the Object Explorer:
For more information, see SQL Server Management Studio: Connect to a server.
var builder = DistributedApplication.CreateBuilder(args);
var sql = builder.AddSqlServer("sql");
var sqldb = sql.AddDatabase("sqldb");
var myService = builder.AddProject<Projects.MyService>()
.WithReference(sqldb);
Configuration
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>()
:
builder.AddSqlServerDbContext<MyDbContext>("myConnection");
The connection string is retrieved from the ConnectionStrings
configuration section:
{
"ConnectionStrings": {
"myConnection": "Data Source=myserver;Initial Catalog=master"
}
}
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.
For more information, see the ConnectionString.
Use configuration providers
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:
{
"Aspire": {
"Microsoft": {
"EntityFrameworkCore": {
"SqlServer": {
"ConnectionString": "YOUR_CONNECTIONSTRING",
"DbContextPooling": true,
"DisableHealthChecks": true,
"DisableTracing": true,
"DisableMetrics": false
}
}
}
}
}
Use inline configurations
You can also pass the Action<MicrosoftEntityFrameworkCoreSqlServerSettings>
delegate to set up some or all the options inline, for example to turn off the metrics:
builder.AddSqlServerDbContext<YourDbContext>(
"sql",
static settings =>
settings.DisableMetrics = true);
Configure multiple DbContext connections
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:
{
"Aspire": {
"Microsoft": {
"EntityFrameworkCore": {
"SqlServer": {
"ConnectionString": "YOUR_CONNECTIONSTRING",
"DbContextPooling": true,
"DisableHealthChecks": true,
"DisableTracing": true,
"DisableMetrics": false,
"AnotherDbContext": {
"ConnectionString": "AnotherDbContext_CONNECTIONSTRING",
"DisableTracing": false
}
}
}
}
}
}
Then calling the AddSqlServerDbContext
method with AnotherDbContext
type parameter would load the settings from Aspire:Microsoft:EntityFrameworkCore:SqlServer:AnotherDbContext
section.
builder.AddSqlServerDbContext<AnotherDbContext>("another-sql");
Configuration options
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. |
Health checks
By default, .NET Aspire integrations enable health checks for all services. For more information, see .NET Aspire integrations overview.
By default, the .NET Aspire Sql Server Entity Framework Core integration handles the following:
- Adds the
DbContextHealthCheck
, which calls EF Core's CanConnectAsync method. The name of the health check is the name of theTContext
type. - 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:
Microsoft.EntityFrameworkCore.ChangeTracking
Microsoft.EntityFrameworkCore.Database.Command
Microsoft.EntityFrameworkCore.Database.Connection
Microsoft.EntityFrameworkCore.Database.Transaction
Microsoft.EntityFrameworkCore.Infrastructure
Microsoft.EntityFrameworkCore.Migrations
Microsoft.EntityFrameworkCore.Model
Microsoft.EntityFrameworkCore.Model.Validation
Microsoft.EntityFrameworkCore.Query
Microsoft.EntityFrameworkCore.Update
Tracing
The .NET Aspire SQL Server Entity Framework Core integration will emit the following Tracing activities using OpenTelemetry:
- "OpenTelemetry.Instrumentation.EntityFrameworkCore"
Metrics
The .NET Aspire SQL Server Entity Framework Core integration will emit the following metrics using OpenTelemetry:
Microsoft.EntityFrameworkCore:
ec_Microsoft_EntityFrameworkCore_active_db_contexts
ec_Microsoft_EntityFrameworkCore_total_queries
ec_Microsoft_EntityFrameworkCore_queries_per_second
ec_Microsoft_EntityFrameworkCore_total_save_changes
ec_Microsoft_EntityFrameworkCore_save_changes_per_second
ec_Microsoft_EntityFrameworkCore_compiled_query_cache_hit_rate
ec_Microsoft_Entity_total_execution_strategy_operation_failures
ec_Microsoft_E_execution_strategy_operation_failures_per_second
ec_Microsoft_EntityFramew_total_optimistic_concurrency_failures
ec_Microsoft_EntityF_optimistic_concurrency_failures_per_second
.NET Aspire