SQL Server 170: Contains over a Guid/DateTime primitive collection translates to JSON_CONTAINS with a uniqueidentifier/datetime2 argument (error 8116)

Aperta
  • Qualcosa non va?
#39,625 0 commenti 0 reazioni 0 assegnatari Vedi su GitHub

I maintainer di solito rispondono entro 2 giorni

Nessuno ha ancora preso questa issue.

Valutazione

Difficoltà
4/5
Tempo stimato
3-5 giorni
Idoneità per principianti
48/100
Tipo di issue
Bug
Chiarezza
Specificata chiaramente
Stato di attività
Attiva
Stack tecnologico
csharp, sql
Ambito
backend, databases

Direzione di ricerca

Start by reading the SQL Server provider's translation for primitive collection Contains and its compatibility-level 170 JSON_CONTAINS tests; the issue does not name specific files. Compare the generated search argument types for Guid and DateTime with the working int case, and check existing OPENJSON translation tests as a fallback reference. Done means Contains works for both column-mapped and ToJson collections with Guid and DateTime at level 170, while existing int behavior remains covered.

Scritto dal modello di indicizzazione a partire dal testo della issue.

Descrizione

Bug description

At SQL Server compatibility level 170, Contains over a primitive collection of Guid or DateTime is translated to JSON_CONTAINS(..., @param) with the parameter typed uniqueidentifier / datetime2. SQL Server rejects that:

Microsoft.Data.SqlClient.SqlException (0x80131904): Argument data type uniqueidentifier is invalid for argument 2 of json_contains function.
Microsoft.Data.SqlClient.SqlException (0x80131904): Argument data type datetime2 is invalid for argument 2 of json_contains function.

JSON_CONTAINS only accepts JSON-native scalar types as the search value, but EF passes the CLR type's store type. int lists work; Guid and DateTime lists fail.

The failure covers both collection shapes:

  • a primitive collection mapped to its own column: WHERE JSON_CONTAINS([b].[Ids], @id) = 1;
  • a list inside an owned type mapped with ToJson(): WHERE JSON_CONTAINS(JSON_QUERY([b].[Details], '$.Ids'), @id) = 1.

It happens with both UseSqlServer and UseAzureSql, on json columns, so it is not the column-type issue. Any(x => x == v) is normalized to the same JSON_CONTAINS.

Below level 170 the same queries use OPENJSON and work. The OPENJSON translation also seems to be the right fallback at 170 for search values that are not JSON-native: string-convert the value, or skip JSON_CONTAINS for those types.

Workaround: .Where(x => x == v).Any(), which still translates to EXISTS (SELECT 1 FROM OPENJSON(...) ... WHERE [value] = @v).

Your code
// dotnet run repro.cs -- "<connection string to an empty database on SQL Server 2025>"
#:property PublishAot=false
#:package [email protected]
using Microsoft.EntityFrameworkCore;

var options = new DbContextOptionsBuilder<BlogContext>()
    .UseSqlServer(args[0], o => o.UseCompatibilityLevel(170)) // UseAzureSql(...) fails the same way
    .Options;
await using var context = new BlogContext(options);
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

var id = Guid.NewGuid();
var when = new DateTime(2026, 1, 2);
context.Blogs.Add(new Blog { Ids = [id], Details = new Details { Numbers = [1, 2], Ids = [id], Dates = [when] } });
await context.SaveChangesAsync();

await context.Blogs.CountAsync(b => b.Details.Numbers.Contains(2)); // OK:   JSON_CONTAINS(JSON_QUERY([b].[Details], '$.Numbers'), 2) = 1
await context.Blogs.CountAsync(b => b.Ids.Contains(id));            // FAIL: JSON_CONTAINS([b].[Ids], @id) = 1
await context.Blogs.CountAsync(b => b.Details.Ids.Contains(id));    // FAIL: JSON_CONTAINS(JSON_QUERY([b].[Details], '$.Ids'), @id) = 1
await context.Blogs.CountAsync(b => b.Details.Dates.Contains(when)); // FAIL: JSON_CONTAINS(JSON_QUERY([b].[Details], '$.Dates'), @when) = 1
await context.Blogs.CountAsync(b => b.Details.Ids.Where(x => x == id).Any()); // OK (workaround): EXISTS over OPENJSON

public class Blog
{
    public int Id { get; set; }
    public List<Guid> Ids { get; set; } = [];
    public Details Details { get; set; } = new();
}

public class Details
{
    public List<int> Numbers { get; set; } = [];
    public List<Guid> Ids { get; set; } = [];
    public List<DateTime> Dates { get; set; } = [];
}

public class BlogContext(DbContextOptions<BlogContext> options) : DbContext(options)
{
    public DbSet<Blog> Blogs => Set<Blog>();
    protected override void OnModelCreating(ModelBuilder modelBuilder)
        => modelBuilder.Entity<Blog>().OwnsOne(b => b.Details, d => d.ToJson());
}
Stack traces
Microsoft.Data.SqlClient.SqlException (0x80131904): Argument data type uniqueidentifier is invalid for argument 2 of json_contains function.
Error Number:8116,State:1,Class:16
EF Core version

11.0.0

Database provider

Microsoft.EntityFrameworkCore.SqlServer 11.0.0. Reproduced on SQL Server 2025 (17.0.4025.3); the same error on Azure SQL Database.

Target framework

.NET 11.0

Operating system

Windows 11

IDE

No response

Lingua principale
C#
Stelle
14.8k
Fork
3.4k
Merge medio
3g 18h
PR unite (30g)
159

Preparare l'ambiente

Apri in Codespaces

Avvia il container di sviluppo del progetto nel browser, con il tuo account GitHub.

Come iniziare

  1. Leggi tutta la issue e poi la guida ai contributi del progetto.
  2. Commenta sulla issue per dire che te ne occupi tu — evita che due persone facciano lo stesso lavoro.
  3. Fai un fork del repository e lavora su un branch.
  4. Apri una pull request che faccia riferimento al numero della issue.

Altre issue di dotnet/efcore

Tutte le issue di dotnet/efcore

Issue simili

Altre issue su C#

Ricevi le nuove issue nella tua casella

Un breve riepilogo di issue GitHub adatte ai principianti.