Exeption when querying json, that happens only on Azure SQL with compatibility level 170

[KarolStosik]

Bug description

When compatibility level on DbContext is set to 170, query on json column (native json or nvarchar(max)) works on local SQL server 17.0.4025 but doesn't work on Azure SQL 12.0.2000.8. Both databases have Compatibility level 170.

When .UseAzureSql(ConnectionString) is used

var partNumbers = await queryContext.Cars
    .Where(c => c.Vin == "1FA6P8TH8J5123456" && c.DealerId == "DEALER-001")
    .SelectMany(c => c.CarConfiguration.OptionPackages!)
    .Where(op => op.PackageId == "PKG-SPORT")
    .SelectMany(op => op.PartNumbers)
    .ToListAsync();

Triggers exception:
AS JSON option can be specified only for column of nvarchar(max) type in WITH clause

Workaround is to use .UseAzureSql(ConnectionString, sql => sql.UseCompatibilityLevel(160))

SQL Server documentation mentions that there may be some compatibility issues link, this one may be related.

Currently, the OPENJSON() function doesn't accept the json data type in some platforms. Currently, it's an implicit conversion. Explicitly convert to nvarchar(max) first.

In SQL Server 2025 (17.x), the OPENJSON() function does support json. For more information, see Key JSON capabilities in SQL Server 2025.

This should be addressed by EF Core, or at least mentioned in documentation.

Your code

using Microsoft.EntityFrameworkCore;


const string ConnectionString =
    // Local repro (shows the bad SQL shape but does not fail):
    "Server=(localdb)\\MSSQLLocalDB;Database=EfCoreJsonRepro;Trusted_Connection=True;TrustServerCertificate=True";
// Azure SQL repro (fails at runtime with Msg 13618) -- swap in your own server/db/credentials:
// "Server=tcp:<yourserver>.database.windows.net,1433;Database=<db>;User ID=<user>;Password=<password>;Encrypt=True;TrustServerCertificate=False";


var options = new DbContextOptionsBuilder<CarContext>()
    .UseSqlServer(ConnectionString, sql => sql.UseCompatibilityLevel(170))
    // .UseAzureSql(ConnectionString, sql => sql.UseCompatibilityLevel(170)) // <- use against Azure SQL to see Msg 13618
    .Options;

await using (var seedContext = new CarContext(options))
{
    await seedContext.Database.EnsureDeletedAsync();
    await seedContext.Database.EnsureCreatedAsync();

    seedContext.Cars.Add(new Car
    {
        Vin = "1FA6P8TH8J5123456",
        DealerId = "DEALER-001",
        CarConfiguration = new CarConfiguration
        {
            CurrentTrim = "GT-Line",
            OptionPackages =
            [
                new OptionPackage { PackageId = "PKG-SPORT", PartNumbers = ["SP-100", "SP-101"] },
                new OptionPackage { PackageId = "PKG-TOW", PartNumbers = ["TW-200"] }
            ]
        }
    });
    await seedContext.SaveChangesAsync();
}

await using (var queryContext = new CarContext(options))
{
    // The query below triggers server-side "AS JSON" push-down for the nested PartNumbers collection.
    var partNumbers = await queryContext.Cars
        .Where(c => c.Vin == "1FA6P8TH8J5123456" && c.DealerId == "DEALER-001")
        .SelectMany(c => c.CarConfiguration.OptionPackages!)
        .Where(op => op.PackageId == "PKG-SPORT")
        .SelectMany(op => op.PartNumbers)
        .ToListAsync();

    Console.WriteLine($"Part numbers: {string.Join(", ", partNumbers)}");
}


// ============================================================================
// Model
// ============================================================================

public class Car
{
    public int CarId { get; set; }
    public string Vin { get; set; } = null!;
    public string DealerId { get; set; } = null!;

    // Stored as a native `json` column.
    public CarConfiguration CarConfiguration { get; set; } = null!;

    public byte[] RowVersion { get; set; } = null!;
}

public class CarConfiguration
{
    public string? CurrentTrim { get; set; }

    public List<OptionPackage>? OptionPackages { get; set; }
}

public class OptionPackage
{
    public required string PackageId { get; set; }

    public required ICollection<string> PartNumbers { get; set; }
}

// ============================================================================
// DbContext
// ============================================================================

public class CarContext(DbContextOptions<CarContext> options) : DbContext(options)
{
    public DbSet<Car> Cars => Set<Car>();

    protected override void OnModelCreating(ModelBuilder modelBuilder)
    {
        modelBuilder.Entity<Car>(builder =>
        {
            builder.ToTable("Cars");
            builder.HasKey(e => e.CarId);

            builder.Property(e => e.Vin).IsUnicode(false).HasMaxLength(32);
            builder.Property(e => e.DealerId).IsUnicode(false).HasMaxLength(32);
            builder.Property(e => e.RowVersion).IsRowVersion();

            builder.HasIndex(e => new { e.Vin, e.DealerId }).IsUnique();

            builder.ComplexProperty(e => e.CarConfiguration, pp =>
            {
                pp.ToJson("CarConfiguration");
                pp.HasColumnType("json");
                pp.IsRequired();

                pp.Property(p => p.CurrentTrim)
                    .HasJsonPropertyName("currentTrim");

                pp.ComplexCollection(p => p.OptionPackages, op =>
                {
                    op.HasJsonPropertyName("optionPackages");
                    op.Property(o => o.PackageId).HasJsonPropertyName("packageId");
                    op.PrimitiveCollection(o => o.PartNumbers)
                        .ElementType(e => e.IsUnicode(false).HasMaxLength(32))
                        .HasJsonPropertyName("partNumbers");
                });
            });
        });
    }
}

Stack traces

Microsoft.Data.SqlClient.SqlException (0x80131904): AS JSON option can be specified only for column of nvarchar(max) type in WITH clause.
at System.Threading.Tasks.ContinuationResultTaskFromResultTask2.InnerInvoke() at System.Threading.Tasks.Task.<>c.<.cctor>b__288_0(Object obj) at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) --- End of stack trace from previous location --- at System.Threading.ExecutionContext.RunInternal(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.Tasks.Task.ExecuteWithThreadLocal(Task& currentTaskSlot, Thread threadPoolThread) --- End of stack trace from previous location --- at System.Runtime.ExceptionServices.InternalCalls.<RhpSfiNext>g____PInvoke|1_0(StackFrameIterator* __pThis_native, UInt32* __uExCollideClauseIdx_native, Boolean* __fUnwoundReversePInvoke_native, Boolean* __fIsExceptionIntercepted_native) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at System.Runtime.ExceptionServices.InternalCalls.<RhpSfiNext>g____PInvoke|1_0(StackFrameIterator* __pThis_native, UInt32* __uExCollideClauseIdx_native, Boolean* __fUnwoundReversePInvoke_native, Boolean* __fIsExceptionIntercepted_native) at Microsoft.EntityFrameworkCore.Storage.RelationalCommand.ExecuteReaderAsync(RelationalCommandParameterObject parameterObject, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.InitializeReaderAsync(AsyncEnumerator enumerator, CancellationToken cancellationToken)
at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.<>c__DisplayClass30_02.<<ExecuteAsync>b__0>d.MoveNext() --- End of stack trace from previous location --- at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func4 operation, Func4 verifySucceeded, TState state, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteImplementationAsync[TState,TResult](Func4 operation, Func4 verifySucceeded, TState state, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Storage.ExecutionStrategy.ExecuteAsync[TState,TResult](TState state, Func4 operation, Func4 verifySucceeded, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.Query.Internal.SingleQueryingEnumerable1.AsyncEnumerator.MoveNextAsync()
at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken) at Microsoft.EntityFrameworkCore.EntityFrameworkQueryableExtensions.ToListAsync[TSource](IQueryable1 source, CancellationToken cancellationToken)

Verbose output

SELECT [p].[value]
FROM [Cars] AS [c]
CROSS APPLY OPENJSON([c].[CarConfiguration], '$.optionPackages') WITH (
    [packageId] nvarchar(max) '$.packageId',
    [partNumbers] json '$.partNumbers' AS JSON
) AS [o]
CROSS APPLY OPENJSON([o].[partNumbers]) WITH ([value] varchar(32) '$') AS [p]
WHERE [c].[Vin] = '1FA6P8TH8J5123456' AND [c].[DealerId] = 'DEALER-001' AND [o].[packageId] = N'PKG-SPORT'

EF Core version

10.0.9

Database provider

No response

Target framework

.NET 10

Operating system

Azure

IDE

No response