SQL Server provider: JSON_VALUE should emit RETURNING nvarchar(4000) instead of RETURNING nvarchar(max) to avoid Azure SQL engine crash

[knopa]

Bug Description

When querying properties from native json columns, EF Core 10 generates
JSON_VALUE(..., '$.Property' RETURNING nvarchar(max)). This causes a fatal engine crash
on both Azure SQL and SQL Server 2025 when the query joins a wide table and selects
any column from it alongside JSON_VALUE RETURNING nvarchar(max).

Replacing RETURNING nvarchar(max) with RETURNING nvarchar(4000) fixes the crash entirely.

Since JSON_VALUE is already documented to return a maximum of nvarchar(4000) by default,
emitting RETURNING nvarchar(max) provides no practical benefit but triggers an engine bug
related to unbounded nvarchar(max) memory allocation when combined with wide table projections.

Error

Via application / remote connection:

Msg 40197, Level 20, State 12, Line 10
The service has encountered an error processing your request. Please try again. Error code 22002.
Msg 0, Level 20, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.

Via direct SSMS connection (TCP):

Msg 10054, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server.
(provider: TCP Provider, error: 0 - An existing connection was forcibly closed by the remote host.)

Via direct SSMS connection (Shared Memory):

Msg 233, Level 20, State 0, Line 0
A transport-level error has occurred when receiving results from the server.
(provider: Shared Memory Provider, error: 0 - No process is on the other end of the pipe.)

Minimal Failing Query

SELECT
    [e].[Id],
    [w].[Id],                    -- any column from the wide joined table
    JSON_VALUE([e].[Data1], '$.Name'  RETURNING nvarchar(max)) AS [Name],
    JSON_VALUE([e].[Data1], '$.Email' RETURNING nvarchar(max)) AS [Email]
FROM [Entities] AS [e]
INNER JOIN [WideTable] AS [w] ON [e].[WideTableId] = [w].[Id]
WHERE [e].[ForeignKeyId] = 1

Where:

• [e].[Data1] is a native json typed column (not nvarchar(max))
• [WideTable] is a table with many columns (20+), regardless of which column is selected from it

Minimal Working Query

-- Option 1: replace nvarchar(max) with nvarchar(4000)
SELECT
    [e].[Id],
    [w].[Id],
    JSON_VALUE([e].[Data1], '$.Name'  RETURNING nvarchar(4000)) AS [Name],
    JSON_VALUE([e].[Data1], '$.Email' RETURNING nvarchar(4000)) AS [Email]
FROM [Entities] AS [e]
INNER JOIN [WideTable] AS [w] ON [e].[WideTableId] = [w].[Id]
WHERE [e].[ForeignKeyId] = 1

-- Option 2: remove the column selected from the wide table
SELECT
    [e].[Id],
    JSON_VALUE([e].[Data1], '$.Name'  RETURNING nvarchar(max)) AS [Name],
    JSON_VALUE([e].[Data1], '$.Email' RETURNING nvarchar(max)) AS [Email]
FROM [Entities] AS [e]
INNER JOIN [WideTable] AS [w] ON [e].[WideTableId] = [w].[Id]
WHERE [e].[ForeignKeyId] = 1

Exact Reproduction Conditions

Through extensive binary search testing, the crash requires all of the following:

Condition	Required to crash
JSON_VALUE RETURNING nvarchar(max)	✅ Yes — nvarchar(4000) does not crash
INNER JOIN on a wide table (20+ columns)	✅ Yes — narrow tables or no JOIN do not crash
Any column selected from the wide joined table	✅ Yes — JOIN without selecting from it does not crash

Removing any one of these conditions prevents the crash.

Progression of Tests

Test	Conditions	Result
JSON_VALUE RETURNING nvarchar(max), no JOIN	minimal	✅ OK
INNER JOIN wide table, no JSON_VALUE	no JSON	✅ OK
INNER JOIN wide table + JSON_VALUE but no column selected from wide table	partial	✅ OK
INNER JOIN wide table + JSON_VALUE RETURNING nvarchar(4000) + column from wide table	bounded	✅ OK
INNER JOIN wide table + JSON_VALUE RETURNING nvarchar(max) + column from wide table	full	❌ Crash

Schema

-- Table with native json columns
CREATE TABLE [Entities] (
    [Id]           int   NOT NULL,
    [WideTableId]  int   NOT NULL,   -- FK to wide table
    [ForeignKeyId] int   NOT NULL,
    [Data1]        json  NULL,       -- native json type
    [Data2]        json  NULL,       -- native json type
)

-- Wide table (20+ columns of various types)
CREATE TABLE [WideTable] (
    [Id]  int  NOT NULL,
    -- ... 20+ columns
)

Proposed Fix

EF Core should emit RETURNING nvarchar(4000) instead of RETURNING nvarchar(max)
for JSON_VALUE calls, since:

1. JSON_VALUE already caps its return value at nvarchar(4000) by default —
   RETURNING nvarchar(max) provides no additional data capacity
2. RETURNING nvarchar(max) triggers an engine bug on both Azure SQL and SQL Server 2025
   causing crashes when joining wide tables and selecting columns from them
3. RETURNING nvarchar(4000) is functionally equivalent and does not trigger the crash

Workaround

Use a DbCommandInterceptor with a precise regex that targets only JSON_VALUE calls
to avoid accidentally replacing nvarchar(max) in other SQL contexts:

public class FixJsonValueInterceptor : DbCommandInterceptor
{
    // Targets only RETURNING nvarchar(max) inside JSON_VALUE calls
    private static readonly Regex JsonValueReturning = new(
        @"JSON_VALUE\(([^)]+)\s+RETURNING\s+nvarchar\(max\)\)",
        RegexOptions.Compiled
    );

    private static string Fix(string sql) =>
        JsonValueReturning.Replace(sql, "JSON_VALUE($1 RETURNING nvarchar(4000))");

    public override InterceptionResult<DbDataReader> ReaderExecuting(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result)
    {
        command.CommandText = Fix(command.CommandText);
        return result;
    }

    public override ValueTask<InterceptionResult<DbDataReader>> ReaderExecutingAsync(
        DbCommand command, CommandEventData eventData,
        InterceptionResult<DbDataReader> result,
        CancellationToken cancellationToken = default)
    {
        command.CommandText = Fix(command.CommandText);
        return ValueTask.FromResult(result);
    }
}

Register it:

options.UseSqlServer(connectionString)
       .AddInterceptors(new FixJsonValueInterceptor());

Environment

• EF Core version: 10.x
• Database provider: Microsoft.EntityFrameworkCore.SqlServer
• Target framework: .NET 10
• SQL Server 2025: 17.0.1105.2 (RTM-GDR, KB5077468) — ❌ crashes
• Azure SQL: 12.0.2000.8 (RTM) — ❌ crashes
• Column type: Native json (not nvarchar(max))

[roji]

AI Triage

The below is an AI-generated analysis and may contain inaccuracies.

Classification

Type: Bug (SQL Server engine bug triggered by EF Core's generated SQL)
Area labels: area-query, area-sqlserver, area-json (already applied)

Analysis

The issue report is well-documented and the SQL generation pattern is confirmed. EF Core 10+ generates JSON_VALUE(... RETURNING nvarchar(max)) for string properties within native json columns (when using compatibility level 170). The user reports that this triggers a fatal engine crash on SQL Server 2025 (RTM-GDR) and Azure SQL when combined with a JOIN on a wide table (20+ columns) and selecting a column from that wide table.

Key context on RETURNING nvarchar(max)

The RETURNING nvarchar(max) clause was intentionally added in EF Core 10 via #35729 to take advantage of SQL Server 2025's new RETURNING clause support. A primary motivation was to overcome the 4000-character limit of JSON_VALUE without RETURNING (#29477). Without the RETURNING clause, JSON_VALUE is capped at nvarchar(4000) and silently truncates/nulls longer values.

The user's assertion that RETURNING nvarchar(max) provides no additional data capacity is incorrect — it is specifically what allows JSON string values longer than 4000 characters to be read. Simply switching to RETURNING nvarchar(4000) as the user proposes would regress that functionality.

Where the SQL is generated

The RETURNING clause is generated in SqlServerQuerySqlGenerator.VisitJsonScalar when the source JSON column is mapped as native json type. The return type (nvarchar(max)) comes from the scalar property's type mapping (TypeMapping.StoreType), which defaults to nvarchar(max) for unconstrained string properties in SqlServerTypeMappingSource.

Is this a regression?

Not a traditional EF Core regression. The RETURNING clause is entirely new in EF Core 10 — previous versions used CAST(JSON_VALUE(...) AS nvarchar(max)) which does not trigger this SQL Server engine bug. However, upgrading from EF Core 9 to 10 against SQL Server 2025/Azure SQL could expose this SQL Server engine bug.

Reproducing the crash

The repro below confirms that EF Core generates the problematic SQL pattern (JSON_VALUE(... RETURNING nvarchar(max)) with a wide-table JOIN). However, the actual engine crash could not be reproduced on the available SQL Server 2025 instance — it may require specific SQL Server build versions or Azure SQL configurations as reported by the user.

Minimal repro

using System;
using System.Linq;
using Microsoft.EntityFrameworkCore;
using Microsoft.Extensions.Logging;

await using var context = new TestContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

// Generates: JSON_VALUE([e].[Data], '$.Name' RETURNING nvarchar(max))
// which crashes SQL Server 2025 / Azure SQL when joined with a wide table
var query = context.Entities
    .Join(context.WideTables, e => e.WideTableId, w => w.Id, (e, w) => new
    {
        e.Id,
        WideTableId = w.Id,
        e.Data.Name,
        e.Data.Email
    })
    .Where(x => x.Id == 1);

Console.WriteLine(query.ToQueryString());
await foreach (var item in query.AsAsyncEnumerable())
{
    Console.WriteLine($"{item.Id} {item.Name} {item.Email}");
}

public class Entity
{
    public int Id { get; set; }
    public int WideTableId { get; set; }
    public JsonData Data { get; set; } = null!;
    public WideTable WideTable { get; set; } = null!;
}

public class JsonData
{
    public string Name { get; set; } = "";
    public string Email { get; set; } = "";
}

public class WideTable
{
    public int Id { get; set; }
    public string Col01 { get; set; } = "";
    public string Col02 { get; set; } = "";
    public string Col03 { get; set; } = "";
    public string Col04 { get; set; } = "";
    public string Col05 { get; set; } = "";
    public string Col06 { get; set; } = "";
    public string Col07 { get; set; } = "";
    public string Col08 { get; set; } = "";
    public string Col09 { get; set; } = "";
    public string Col10 { get; set; } = "";
    public string Col11 { get; set; } = "";
    public string Col12 { get; set; } = "";
    public string Col13 { get; set; } = "";
    public string Col14 { get; set; } = "";
    public string Col15 { get; set; } = "";
    public string Col16 { get; set; } = "";
    public string Col17 { get; set; } = "";
    public string Col18 { get; set; } = "";
    public string Col19 { get; set; } = "";
    public string Col20 { get; set; } = "";
}

public class TestContext : DbContext
{
    public DbSet<Entity> Entities { get; set; }
    public DbSet<WideTable> WideTables { get; set; }

    protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
        => optionsBuilder
            .UseSqlServer(
                Environment.GetEnvironmentVariable("Test__SqlServer__DefaultConnection"),
                o => o.UseCompatibilityLevel(170))
            .LogTo(Console.WriteLine, LogLevel.Information)
            .EnableSensitiveDataLogging();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Entity>(b =>
        {
            b.OwnsOne(e => e.Data, nb => nb.ToJson());
            b.HasOne(e => e.WideTable).WithMany().HasForeignKey(e => e.WideTableId);
        });
    }
}

Generated SQL (confirmed):

SELECT [e].[Id], [w].[Id] AS [WideTableId],
    JSON_VALUE([e].[Data], '$.Name' RETURNING nvarchar(max)) AS [Name],
    JSON_VALUE([e].[Data], '$.Email' RETURNING nvarchar(max)) AS [Email]
FROM [Entities] AS [e]
INNER JOIN [WideTables] AS [w] ON [e].[WideTableId] = [w].[Id]
WHERE [e].[Id] = 1

Potential duplicates

No duplicates found. Related issues:

• #35729 — the feature that added RETURNING clause support (completed in EF Core 10)
• #29477 — the original request to support JSON strings > 4000 chars (closed as duplicate of Support the new RETURNING clause in JSON_VALUE() in the upcoming SQL Server 2025 #35729)
• #36474 — related RETURNING clause cleanup work (open, milestone 11.0)

[roji]

This causes a fatal engine crash on both Azure SQL and SQL Server 2025 when the query joins a wide table and selects
any column from it alongside JSON_VALUE RETURNING nvarchar(max).

This would be a serious bug indeed in SQL Server, but has nothing to do with EF; in other words, if a certain SQL generated by EF causes SQL Server to crash, then that needs to be fixed on the SQL Server side, rather than looking into changing the SQL produced by EF.

Now, I'm more than happy to make sure the proper people at SQL Server are aware of this, but in order to do that I need to see an actually minimal repro; I've tried to do that, but above you've provided a general textual description, and haven't provided a simple, minimal repro as is needed (see also the attempted AI-generated one in the previous comment which does not cause SQL Server 2025 to crash for me).

So if possible, can you please submit a simple T-SQL script which, when executed against SQL Server 2025, causes it to crash? This would include the creation of the table, the seeding and the query? Otherwise, if it's much easier for you, a .NET console program using EF (or not) which causes the crash would also be OK.

Since JSON_VALUE is already documented to return a maximum of nvarchar(4000) by default, emitting RETURNING nvarchar(max) provides no practical benefit

That's not accurate. JSON_VALUE indeed returns nvarchar(4000) by default, but this is precisely what we want to avoid by specifying RETURNING nvarchar(max) - if the JSON property in question contains more than 4000 characters, the default JSON_VALUE behavior wouldn't allow fetching it (see #29477). In other words, RETURNING nvarchar(max) was done very much by design and serves a purpose.