Aspire SQL Server Entity Framework Core integration

Konten ini belum tersedia dalam bahasa Anda.

SQL Server is a relational database management system developed by Microsoft. The 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.

Hosting integration

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 AppHost project.

aspire add sqlserver

Aspire CLI interaktif; pilih hasil pencarian yang sesuai saat diminta:

Select an integration to add:

> sqlserver (Aspire.Hosting.SqlServer)
> Other results listed as selectable options...

#:package Aspire.Hosting.SqlServer@*

<PackageReference Include="Aspire.Hosting.SqlServer" Version="*" />

Add SQL Server resource and database resource

In your AppHost 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.

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql")
    .WithLifetime(ContainerLifetime.Persistent);

var db = sql.AddDatabase("database");

builder.AddProject<Projects.ExampleProject>("exampleproject")
    .WithReference(db)
    .WaitFor(db);

// After adding all resources, run the app...

builder.Build().Run();

When Aspire adds a container image to the AppHost, 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.

When adding a database resource to the app model, the database is created if it doesn’t already exist. The creation of the database relies on the AppHost eventing APIs, specifically ResourceReadyEvent. In other words, when the sql resource is ready, the event is raised and the database resource is created.

The SQL Server resource includes default credentials with a username of sa and a random password generated using the CreateDefaultPasswordParameter method.

When the AppHost runs, the password is stored in the AppHost’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.

Add SQL Server resource with database scripts

By default, when you add a SqlServerDatabaseResource, it relies on the following SQL script to create the database:

IF
(
    NOT EXISTS
    (
        SELECT 1
        FROM sys.databases
        WHERE name = @DatabaseName
    )
)
CREATE DATABASE [<QUOTED_DATABASE_NAME>];

To alter the default script, chain a call to the WithCreationScript method on the database resource builder:

var builder = DistributedApplication.CreateBuilder(args);

var sql = builder.AddSqlServer("sql")
                 .WithLifetime(ContainerLifetime.Persistent);

var databaseName = "app-db";
var creationScript = $$"""
    IF DB_ID('{{databaseName}}') IS NULL
        CREATE DATABASE [{{databaseName}}];
    GO

    -- Use the database
    USE [{{databaseName}}];
    GO

    -- Create the todos table
    CREATE TABLE todos (
        id INT PRIMARY KEY IDENTITY(1,1),        -- Unique ID for each todo
        title VARCHAR(255) NOT NULL,             -- Short description of the task
        description TEXT,                        -- Optional detailed description
        is_completed BIT DEFAULT 0,              -- Completion status
        due_date DATE,                           -- Optional due date
        created_at DATETIME DEFAULT GETDATE()    -- Creation timestamp
    );
    GO

    """;

var db = sql.AddDatabase(databaseName)
            .WithCreationScript(creationScript);

builder.AddProject<Projects.AspireApp_ExampleProject>("exampleproject")
       .WithReference(db)
       .WaitFor(db);

// After adding all resources, run the app...

builder.Build().Run();

The preceding example creates a database named app_db with a single todos table. The SQL script is executed when the database resource is created. The script is passed as a string to the WithCreationScript method, which is then executed in the context of the SQL Server resource.

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.AspireApp_ExampleProject>("exampleproject")
    .WithReference(db)
    .WaitFor(db);

// After adding all resources, run the app...

builder.Build().Run();

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.

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.AspireApp_ExampleProject>("exampleproject")
    .WithReference(db)
    .WaitFor(db);

// After adding all resources, run the app...

builder.Build().Run();

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.AspireApp_ExampleProject>("exampleproject")
    .WithReference(db)
    .WaitFor(db);

// After adding all resources, run the app...

builder.Build().Run();

For more information on providing parameters, see External parameters.

Connect to database resources

When the Aspire AppHost 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 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.

SQL Server Management Studio
MSSQL for Visual Studio Code

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.

Hosting integration health checks

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.

The hosting integration relies on the 📦 AspNetCore.HealthChecks.SqlServer NuGet package.

Client integration

To get started with the 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.

dotnet add package Aspire.Microsoft.EntityFrameworkCore.SqlServer

#:package Aspire.Microsoft.EntityFrameworkCore.SqlServer@*

<PackageReference Include="Aspire.Microsoft.EntityFrameworkCore.SqlServer" Version="*" />

Add SQL Server database context

In the Program.cs file of your client-consuming project, call the AddSqlServerDbContext extension method on any IHostApplicationBuilder to register a Microsoft.EntityFrameworkCore.DbContext for use via the dependency injection container. The method takes a connection name parameter.

builder.AddSqlServerDbContext<ExampleDbContext>(connectionName: "database");

To retrieve ExampleDbContext object from a service:

public class ExampleService(ExampleDbContext context)
{
    // Use context...
}

For more information on dependency injection, see .NET dependency injection.

Enrich a SQL Server database context

You may prefer to use the standard Entity Framework method to obtain a database context and add it to the dependency injection container:

builder.Services.AddDbContext<ExampleDbContext>(options =>
    options.UseSqlServer(builder.Configuration.GetConnectionString("database")
        ?? throw new InvalidOperationException("Connection string 'database' not found.")));

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 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 Aspire-style retries, health checks, logging, and telemetry features by calling the EnrichSqlServerDbContext method:

builder.EnrichSqlServerDbContext<ExampleDbContext>(
    configureSettings: settings =>
    {
        settings.DisableRetry = false;
        settings.CommandTimeout = 30; // seconds
    });

The settings parameter is an instance of the MicrosoftEntityFrameworkCoreSqlServerSettings class.

Configuration

The 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<ExampleDbContext>("sql");

The connection string is retrieved from the ConnectionStrings configuration section:

{
  "ConnectionStrings": {
    "sql": "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 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.

By default, the 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 the TContext type.
Integrates with the /health HTTP endpoint, which specifies all registered health checks must pass for app to be considered ready to accept traffic

Logging

The 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 Aspire SQL Server Entity Framework Core integration will emit the following Tracing activities using OpenTelemetry:

“OpenTelemetry.Instrumentation.EntityFrameworkCore”

Metrics

The 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

Tanya & Jawab Kolaborasi Komunitas Diskusi Tonton