Skip to content

sql-server-entity-framework-integration.md

Latest commit

 

History

History
240 lines (174 loc) · 11.2 KB

sql-server-entity-framework-integration.md

File metadata and controls

240 lines (174 loc) · 11.2 KB
Raw
title .NET Aspire SQL Server Entity Framework Core integration
description Learn how to use the .NET Aspire SQL Server Entity Framework integration, which includes both hosting and client integrations.
ms.date 12/02/2024
uid database/sql-server-ef-core-integration

.NET Aspire SQL Server Entity Framework Core integration

[!INCLUDE includes-hosting-and-client]

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.

Hosting integration

[!INCLUDE sql-app-host]

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

.NET CLI

dotnet add package Aspire.Microsoft.EntityFrameworkCore.SqlServer

PackageReference

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

For more information, see dotnet add package or Manage package dependencies in .NET applications.

Add SQL Server database context

In the :::no-loc text="Program.cs"::: file of your client-consuming project, call the xref:Microsoft.Extensions.Hosting.AspireSqlServerEFCoreSqlClientExtensions.AddSqlServerDbContext%2A extension method on any xref:Microsoft.Extensions.Hosting.IHostApplicationBuilder to register a xref:Microsoft.EntityFrameworkCore.DbContext for use via the dependency injection container. The method takes a connection name parameter.

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

Tip

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:

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

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

Add SQL Server database context with enrichment

To enrich the DbContext with additional services, such as automatic retries, health checks, logging and telemetry, call the xref:Microsoft.Extensions.Hosting.AspireSqlServerEFCoreSqlClientExtensions.EnrichSqlServerDbContext* method:

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

The settings parameter is an instance of the xref:Aspire.Microsoft.EntityFrameworkCore.SqlServer.MicrosoftEntityFrameworkCoreSqlServerSettings class.

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<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 .NET Aspire SQL Server Entity Framework Core integration supports xref:Microsoft.Extensions.Configuration?displayProperty=fullName. It loads the xref:Aspire.Microsoft.EntityFrameworkCore.SqlServer.MicrosoftEntityFrameworkCoreSqlServerSettings from configuration files such as :::no-loc text="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 :::no-loc text="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.

[!INCLUDE client-integration-health-checks]

By default, the .NET Aspire Sql Server Entity Framework Core integration handles the following:

  • Adds the DbContextHealthCheck, which calls EF Core's xref:Microsoft.EntityFrameworkCore.Storage.IDatabaseCreator.CanConnectAsync%2A 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

[!INCLUDE integration-observability-and-telemetry]

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

See also