Entities with JSON owned entities can't be queried out with FromSql
- Langage dominant
- C#
- Étoiles
- 14.8k
- Forks
- 3.4k
- Métriques de merge des PR
- Métriques de PR en attente
Description
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; }
}
```
Guide de contribution
Ouvrir le guide de contribution
Piste de recherche
Commencez par exécuter la reproduction de BlogContext fournie avec SQL Server et examinez le SQL généré pour la requête FromSql impliquant l’entité owned JSON. Le travail est terminé lorsque cette requête CTE non composable peut être matérialisée sans la projection SELECT externe supplémentaire.
Rédigé par le modèle d'indexation à partir du texte de l'issue.
Évaluation
- Stack technique
- csharp, sql
- Domaine
- database
- Type d'issue
- Bug
- Difficulté
- 4/5
- Temps estimé
- 3-5 jours
- Activité
- À l'abandon
- Clarté
- Plutôt claire
- Accessibilité débutants
- 35/100