dotnet / dotnet/efcore

Entities with JSON owned entities can't be queried out with FromSql

Abierto
#36,282 2 comentarios 1 reacción 0 asignados Ver en GitHub
area-json area-query
Lenguaje dominante
C#
Estrellas
14.8k
Forks
3.4k
Métricas de merge de PR
Métricas de PR pendientes

Descripción

For an entity type that has JSON owned entities, the following fails:

```c#
var items = await context.Blogs.FromSql($"WITH query AS (SELECT * FROM Blogs) SELECT * FROM query").ToListAsync();
```

It seems like when applying the final projection, we compose an additional SELECT on top:

```sql
SELECT b.Id, b.Name, b.Owned
FROM WITH query AS (SELECT * FROM Blogs) SELECT * FROM query
```

... and this (correctly) causes a non-composable SQL failure. We should not need to compose this extra SELECT on top.

With the focus on complex types for mapping JSON in EF 10, owned entities are getting de-prioritized. Given that, and given that we haven't seen many reports, i'll put this on the backlog for now.

Repro

```c#
await using var context = new BlogContext();
await context.Database.EnsureDeletedAsync();
await context.Database.EnsureCreatedAsync();

var items = await context.Blogs.FromSql($"WITH query AS (SELECT * FROM Blogs) SELECT * FROM query").ToListAsync();

public class BlogContext : DbContext
{
public DbSet Blogs { get; set; }

protected override void OnConfiguring(DbContextOptionsBuilder optionsBuilder)
=> optionsBuilder
.UseSqlServer("Server=localhost;Database=test;User=SA;Password=Abcd5678;Connect Timeout=60;ConnectRetryCount=0;Encrypt=false")
.LogTo(Console.WriteLine, LogLevel.Information)
.EnableSensitiveDataLogging();

protected override void OnModelCreating(ModelBuilder modelBuilder)
{
modelBuilder.Entity().OwnsOne(b => b.Owned).ToJson();
}
}

public class Blog
{
public int Id { get; set; }
public string Name { get; set; }

public Owned Owned { get; set; }
}

public class Owned
{
public int OwnedProperty { get; set; }
}
```

Guía de contribución

Abrir la guía de contribución

Línea de trabajo

Empieza ejecutando la reproducción de BlogContext proporcionada con SQL Server e inspecciona el SQL generado para la consulta FromSql que implica la entidad owned de JSON. El trabajo estará terminado cuando esta consulta CTE no componible pueda materializarse sin la proyección SELECT externa adicional.

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

Evaluación

Stack tecnológico
csharp, sql
Área
database
Tipo de issue
Error
Dificultad
4/5
Tiempo estimado
3-5 días
Estado de actividad
Estancado
Claridad
Bastante claro
Aptitud para principiantes
35/100

Recibe los nuevos issues en tu correo

Un resumen breve de issues de GitHub para principiantes.