# Ef Core Azure SQL

> Use when EF Core targets Azure SQL Database: EnableRetryOnFailure, 'does not support user-initiated transactions', duplicate rows after a retry, UseAzureSql or UseSqlServer, AsSplitQuery, JSON columns.

- Skill: `microsoft/ef-core-azure-sql` (Agent Skill, multi-file: 2 files)
- Install (CLI): `npx get-skill add microsoft/ef-core-azure-sql`
- Raw SKILL.md: https://api.skillmd.com/api/skills/microsoft/ef-core-azure-sql/raw
- Safety review: pending
- Works with: Claude Code, Claude.ai, OpenAI Codex
- Category: DevOps & Infra
- Author: Microsoft (https://skillmd.com/u/microsoft)
- Updated: 2026-11-18
- Page: https://skillmd.com/skills/microsoft/ef-core-azure-sql

---


# EF Core against Azure SQL Database

This is the Azure SQL Database delta on top of EF Core, not an EF Core tutorial. Modelling, LINQ,
change tracking and `Include` work the same everywhere and are not repeated here.

Command output, compiler results and runtime behaviour below are for
`Microsoft.EntityFrameworkCore.SqlServer` 9 unless a row says otherwise. EF Core 10 is the
supported long term release and EF Core 9 is still serviced.
Open [references/retry-transactions-and-type-mapping.md](references/retry-transactions-and-type-mapping.md) when
something below disagrees with what you are seeing, or to run the checks yourself.

## Scope

- The Azure SQL Database delta on EF Core, not general EF Core.
- Pooling is `connect-from-dotnet`, retry doctrine `connect-to-azure-sql`, identity
  `entra-id-auth`, and running migrations from a pipeline `github-actions-for-sql`.

## The correction

Retry is not a setting on this database. Every current recommendation turns it on:
`EnableRetryOnFailure` in older code, `UseAzureSql` in newer code. Both produce
`SqlServerRetryingExecutionStrategy`, and **turning it on silently redefines what a transaction
is.** The visible half is an exception, and it is not the problem. The problem is the code written
next, because the exception dictates its own remedy and the remedy is incomplete:

```csharp
// Retry on, so this unit is replayed on a transient fault. A fault arriving after
// Commit runs the delegate again, inserts the order a SECOND time, and Execute returns
// success. Nothing is thrown. Nothing is logged. The caller is told it worked.
var strategy = context.Database.CreateExecutionStrategy();
strategy.Execute(() =>
{
    using var tx = context.Database.BeginTransaction();
    context.Orders.Add(order);
    context.SaveChanges();
    tx.Commit();
});
```

**A retriable unit is a unit that will be run more than once.** Retry does not make a transaction
resilient. It makes it repeatable, and a repeatable write that is not idempotent is a duplicate
write waiting for a network fault it was configured to expect. Both call shapes carry this
identically, so switching call does not fix it and staying on the old call does not cause it.

## What actually refuses, and what does not

The commonly repeated claim, that `Database.BeginTransaction` throws, is wrong.

| Call, with the retrying strategy configured | Result |
|---|---|
| `Database.BeginTransaction()` on its own | **Succeeds.** No exception |
| A LINQ query while that transaction is open | Throws `InvalidOperationException` at once |
| `SaveChanges` while that transaction is open | Throws `InvalidOperationException` at once |
| `Database.ExecuteSqlRaw` while that transaction is open | **Succeeds.** Not guarded at all |
| `SaveChanges` with no explicit transaction | Succeeds. EF's own transaction is a unit it created |

The guard fires on EF's own pipeline, not on raw SQL, and on the first execution of the path rather
than under load:

> `InvalidOperationException: The configured execution strategy 'SqlServerRetryingExecutionStrategy'
> does not support user-initiated transactions. Use the execution strategy returned by
> 'DbContext.Database.CreateExecutionStrategy()' to execute all the operations in the transaction as
> a retriable unit.`

**This exception is cheap**: deterministic, milliseconds, on the first run of the path, and it names
its own fix. Treat it as a compiler error that arrives late. **The fix it names is not the whole
fix**, and nothing after it fails loudly. That is the next section, and it is why this skill exists.

## The silent duplicate write

Same delegate, same configured strategy, one variable: where the transient fault lands.

| Fault arrives | Delegate runs | Rows committed | What the caller sees |
|---|---|---|---|
| Before `Commit` | 2 | **1** | Success. Correct, the first attempt rolled back |
| After `Commit` | 2 | **2** | Success. **No exception, no warning, no log line** |

The window is the commit acknowledgement. The commit reached the server, the acknowledgement did
not reach the client, and the strategy cannot tell that apart from a commit that never happened, so
it replays a unit that already succeeded. Learn calls this the idempotency issue and says it can
lead to data corruption. The blast radius is a silent double write on a transactional path, in
production only, on exactly the transient faults this database is configured to expect.

### Making the unit safe to run twice

In order of preference.

1. **Give the write a natural idempotency key and a unique index**, so a replay collides with
   `Msg 2601` instead of duplicating. This is the only defence that survives a process crash
   between the two attempts.
2. **Hand the transaction to `ExecuteInTransaction` with a `verifySucceeded` predicate.** The
   strategy owns the transaction and, when the commit fails with an unknown outcome, asks the
   predicate whether the work already landed before retrying:

```csharp
var strategy = db.Database.CreateExecutionStrategy();
db.Orders.Add(order);

await strategy.ExecuteInTransactionAsync(
    db,
    operation: (context, cancellationToken) =>
        context.SaveChangesAsync(acceptAllChangesOnSuccess: false, cancellationToken),
    verifySucceeded: (context, cancellationToken) =>
        context.Orders.AsNoTracking().AnyAsync(o => o.Key == order.Key, cancellationToken),
    cancellationToken: ct);

db.ChangeTracker.AcceptAllChanges();
```

`acceptAllChangesOnSuccess: false` is load bearing, not decoration: it leaves the entity `Added` so
the same unit can be replayed if the commit is what failed, and `AcceptAllChanges` afterwards is
what finishes the save. The verifying context needs its own execution strategy, because the
connection that just failed is likely to fail again during the check.

3. **Only then** the bare `CreateExecutionStrategy().ExecuteAsync` wrapper, and only for units that
   are already safe to run twice. The delegate must not capture entities tracked by an outer
   context, must not rely on store generated keys, and must not send an email, enqueue a message or
   charge a card inside it.

Some units cannot be made idempotent, and for those the choice is to accept the duplicate risk or
to narrow the retry set, never to disable retry so the exception goes away.

## What retry costs when it does not help

`UseAzureSql` defaults to 6 retries with a 30 second cap, so 7 attempts. The delays between them
grow to roughly 0, 1, 3, 7.5, 15 and 30 seconds, so a permanently failing operation gives up only
after **close to a minute**. A request thread is held for that whole minute, and behind a
30 second gateway timeout the caller has already given up. Size the retry budget against the
deadline of the thing calling you, and put a `CancellationToken` through every async call.

## UseAzureSql, and the near miss

From **EF Core 9** there is a call named for this database, and Learn's own Azure SQL Database tab
uses it. It does **not** delete `EnableRetryOnFailure`; it changes the default from off to on:

```csharp
options.UseAzureSql(connectionString, o => o.UseCompatibilityLevel(170));
```

| | `UseSqlServer` | `UseAzureSql` |
|---|---|---|
| Engine type | `SqlServer` | `AzureSql` |
| Execution strategy | `SqlServerExecutionStrategy`, no retry | `SqlServerRetryingExecutionStrategy`, 6 retries, 30 second cap |
| Default compatibility level, EF Core 9 | 150 | 150 |
| Default compatibility level, EF Core 10 | 150 | **170** |
| Native `json` column type, EF Core 10 | only at level 170 or above | on by default |

- **It is a compile time API.** On EF Core 8 the call does not exist and the build says
  `error CS1061: 'DbContextOptionsBuilder' does not contain a definition for 'UseAzureSql'`.
  Upgrade the provider package rather than inventing an extension method.
- **When `UseSqlServer` is called by code you cannot change**, EF Core 9 has a first party answer
  that is not obsolete, and it compiles clean: `options.ConfigureSqlEngine(c =>
  c.EnableRetryOnFailureByDefault());`. `UseSqlServer(cs, o => o.UseAzureSqlDefaults(true))` also
  installs the retrying strategy, but it warns `CS0618: 'UseAzureSqlDefaults(bool)' is obsolete` and
  leaves the engine type as `SqlServer`, so the type mapping does not follow.
- **Retry numbers are defaults, not overrides.** Supplying them alongside `UseAzureSql` replaces the
  6 and the 30 seconds. Do that for a reason from your own workload, never by copying a blog post:

```csharp
options.UseAzureSql(
    connectionString,
    o => o.EnableRetryOnFailure(maxRetryCount: 3, maxRetryDelay: TimeSpan.FromSeconds(10), errorNumbersToAdd: null));
```

Whether to retry at all is not a question on this database. **The split is clean:
`connect-to-azure-sql` owns retry at the driver and connection level, this skill owns the EF Core
execution strategy above it and the transaction semantics that only exist up here.**

## Compatibility level, and the migration nobody expects

On EF Core 10, `UseAzureSql` defaults the compatibility level to **170**, and at 170 the provider
maps JSON to the native `json` column type instead of `nvarchar(max)`. Learn states it as a
breaking change: upgrading an application that already uses `UseAzureSql` generates a migration that
alters every existing `nvarchar(max)` JSON column to `json`: data movement on a live table,
produced by a change that reads as configuration.

Generate the migration and read it before it reaches a database:

```bash
dotnet ef migrations add UpgradeToEfCore10
dotnet ef migrations script --idempotent --output migrate.sql
grep -nE 'ALTER COLUMN|json' migrate.sql
```

Then choose, rather than inherit:

- **State the level.** Learn names a level below 170 as the opt out, and its own example is
  `o.UseCompatibilityLevel(160)`, which keeps `nvarchar(max)`.
- **Pin one column** with `HasColumnType("nvarchar(max)")` where only part of the model must wait.
- **Take the retype deliberately**, in a window sized for the table, because the `ALTER` rewrites it.

The level does **not** gate `vector(n)`, which EF Core 10 surfaces as `SqlVector<float>` and
`EF.Functions.VectorDistance`, so do not raise it expecting to unlock vectors. Storing embeddings is
`vector-search-azure-sql`.

## Type mapping, honestly scoped

Most EF Core type mapping advice applies to any engine this provider talks to and belongs to
`design-azure-sql-schema`. Two things are different here.

**`DateTime` with `HasDefaultValueSql("GETDATE()")`.** Azure SQL Database offers no time zone
choice: `CURRENT_TIMEZONE()` returns UTC and `GETDATE()` equals `SYSUTCDATETIME()`, in the local
Azure SQL Database container and in the cloud alike. A model carried over from an engine running on local time changes meaning
without changing shape. Prefer `DateTimeOffset`, or `SYSUTCDATETIME()`, and be explicit either way.

**An indexed `string` with no `MaxLength` is narrowed to `nvarchar(450)`**, and neither the index
nor the data fails silently. From the script generated above, for one indexed string property:

```text
[Name] nvarchar(450) NOT NULL,
CREATE INDEX [IX_Orders_Name] ON [Orders] ([Name]);
```

The `CREATE INDEX` succeeds and the engine then refuses anything longer, with
`Msg 2628, String or binary data would be truncated in table '<table>', column '<column>'`. Adding
the index later to a column already holding longer values fails the same way on the `ALTER COLUMN`,
so the migration stops rather than trimming data. Set `HasMaxLength` deliberately instead of
discovering the 450 at deployment.

## Single and split queries over a wide area network

`Include` of two sibling collections produces a cross product. That is engine independent. **What
changes here is the arithmetic of the fix**, because each extra query is a real network round trip.

- **Prefer split** when the cartesian product is large: moving duplicated megabytes costs more.
- **Prefer single** for small collections and chatty request paths.
- **Measure.** The break even point moves with row width and with distance.
- Split queries are not one consistent read unless wrapped in a snapshot or serializable
  transaction, which puts them back under everything above.

Slow query triage is `slow-query-investigation`.

## Migrations against a cloud database

Doctrine and deployment sequencing are `schema-migrations-safely`; the declarative alternative is
`sql-database-projects`. Only the Azure SQL Database parts are here:

```bash
dotnet ef database update \
  --connection "Server=tcp:your-server.database.windows.net,1433;Database=your-database;Authentication=Active Directory Default;Encrypt=Mandatory;"
```

- **It runs from wherever it is invoked**, so that machine needs a server firewall rule for its own
  address and a principal that can change the schema. Neither is the application's runtime identity,
  and `--connection` is how the two are kept apart: it overrides the string in `AddDbContext` or
  `OnConfiguring` without editing the application.
- **The database itself is not created by a migration.** That is `provision-azure-sql-db`.
- **Do not migrate from application startup here.** EF Core 9 added a database wide lock, so the
  concurrency objection you may recall is out of date. The objection that survives is
  permissions: startup migration means the runtime identity holds schema rights permanently.

## Connecting with an identity rather than a password

Nothing in the `DbContext` changes. `UseAzureSql` takes the same connection string a bare driver
would, so passwordless access stays a connection string concern:

```text
Server=tcp:your-server.database.windows.net,1433;Database=your-database;Authentication=Active Directory Default;Encrypt=Mandatory;
```

Getting an identity to a working connection is `entra-id-auth`. Packages, authentication modes and
pooling are `connect-from-dotnet`.

## Check it worked

Three checks, in the order they catch things.

**The context is on the Azure SQL Database path at all.** Nothing in the connection string says so,
and this is the only command that prints it:

```bash
dotnet ef dbcontext info
```

Expect `Options: EngineType=AzureSql`. Under `UseSqlServer`, with or without a hand written
`EnableRetryOnFailure`, the same line reads `Options: EngineType=SqlServer`, which is how a
`UseAzureSqlDefaults` configuration is caught: it retries, and it still prints `SqlServer`.

**The model and the migrations agree**, before a deployment discovers they do not:

```bash
dotnet ef migrations has-pending-model-changes
```

Expect exit code 0 and `No changes have been made to the model since the last migration.` Exit code
1 and `Changes have been made to the model since the last migration.` is the failure, and it is the
one that becomes an unexplained `ALTER` in somebody else's release.

**A replayed unit collides instead of duplicating.** An idempotency key is only a defence if the
index over it is unique:

```sql
SELECT i.name, i.is_unique
FROM sys.indexes AS i
WHERE i.object_id = OBJECT_ID('dbo.Orders') AND i.is_unique = 1;
```

Expect one row, the index over the key column. With `is_unique` 1 a replayed insert fails with
`Msg 2601, Cannot insert duplicate key row in object 'dbo.Orders' with unique index`; with a
non-unique index the replay writes a second row and the whole defence is decorative.

## Do not

- Do not treat the execution strategy exception as the end of the work. It is the visible half, and
  the silent half is a duplicate write.
- Do not put a hand written `BeginTransaction` and `Commit` inside a retried delegate without an
  idempotency key or a `verifySucceeded` predicate. It compiles, it passes review, and it doubles a
  row on the fault it was configured to expect.
- Do not drop `acceptAllChangesOnSuccess: false` from the `ExecuteInTransaction` form. Without it
  the entity is marked `Unchanged` and the replay saves nothing.
- Do not assume `Database.BeginTransaction` throws. It succeeds, and the next query or `SaveChanges`
  throws. Raw SQL inside that transaction is not guarded at all.
- Do not disable retry to make a transaction error disappear, and do not leave the retry budget
  larger than the caller's timeout.
- Do not claim `UseAzureSql` removes `EnableRetryOnFailure`. It sets its default.
- Do not reach for `UseAzureSqlDefaults(true)`. It is obsolete, and it leaves the engine type
  unchanged; `ConfigureSqlEngine(c => c.EnableRetryOnFailureByDefault())` is the supported form.
- Do not upgrade to EF Core 10 and switch to `UseAzureSql` in the same change without reading the
  next migration.
- Do not add `AsSplitQuery` everywhere by reflex. Over a network it can cost more than it saves.
- Do not apply migrations from application startup against a cloud database.
- Do not leave an indexed string property without `HasMaxLength`, and do not leave two sibling
  collection `Include` calls without `AsSplitQuery` or `AsSingleQuery`.
- Do not give the application's runtime identity schema permission, and do not put a connection
  string, password or server hostname in source.
- Do not use this skill for a different object relational mapper, or for raw
  `Microsoft.Data.SqlClient` work.

## References

- [Checks behind the tables above, with the setup to run them](references/retry-transactions-and-type-mapping.md)
- [Connection resiliency](https://learn.microsoft.com/ef/core/miscellaneous/connection-resiliency):
  execution strategies, the transaction rule, and the four documented answers to a commit that fails
  with an unknown outcome. Read it before writing anything transactional.
- [Microsoft SQL Server database provider](https://learn.microsoft.com/ef/core/providers/sql-server/):
  the first party statement of `UseAzureSql`, `UseCompatibilityLevel` and `ConfigureSqlEngine`.
- [Breaking changes in EF Core 10](https://learn.microsoft.com/ef/core/what-is-new/ef-core-10.0/breaking-changes):
  read before an EF Core 10 upgrade, for the JSON retype and the compatibility level that gates it.
- [Single vs split queries](https://learn.microsoft.com/ef/core/querying/single-split-queries):
  cartesian explosion and every characteristic of split queries.
- Underneath this skill: `connect-to-azure-sql` for retry and pool sizing at the connection level,
  `connect-from-dotnet` for the driver and packages, `entra-id-auth` for a passwordless identity.
- Beside it: `design-azure-sql-schema` for string sizing and collation, and
  `schema-migrations-safely` for how migrations reach a database.

