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

Abierto
  • ¿Algo no está bien?
#39,625 0 comentarios 0 reacciones 0 asignados Ver en GitHub

Los mantenedores suelen responder en 2 días

Nadie ha tomado este issue todavía.

Evaluación

Dificultad
4/5
Tiempo estimado
3-5 días
Aptitud para principiantes
48/100
Tipo de issue
Error
Claridad
Bien especificado
Estado de actividad
Activo
Stack tecnológico
csharp, sql
Área
backend, databases

Línea de trabajo

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.

Escrito por el modelo de indexación a partir del texto del issue.

Descripción

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

Lenguaje dominante
C#
Estrellas
14.8k
Forks
3.4k
Merge medio
3 d 18 h
PR fusionados (30 d)
159

Preparar el entorno

Abrir en Codespaces

Inicia el contenedor de desarrollo del proyecto en tu navegador, con tu propia cuenta de GitHub.

Primeros pasos

  1. Lee el issue completo y luego la guía de contribución del proyecto.
  2. Comenta en el issue que vas a ocuparte — evita que dos personas hagan lo mismo.
  3. Haz un fork del repositorio y trabaja en una rama.
  4. Abre un pull request que haga referencia al número del issue.

Más de dotnet/efcore

Todos los issues de dotnet/efcore

Issues similares

Más issues de C#

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.