dotnet / dotnet/efcore

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

Offen
#36,282 2 Kommentare 1 Reaktion 0 zugewiesene Personen Auf GitHub ansehen
area-json area-query
Vorherrschende Sprache
C#
Sterne
14.8k
Forks
3.4k
PR-Merge-Kennzahlen
PR-Kennzahlen ausstehend

Beschreibung

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; }
}
```

Beitragsleitfaden

Beitragsleitfaden öffnen

Rechercherichtung

Start by running the supplied BlogContext reproduction with SQL Server and inspect the generated SQL for the FromSql query involving the JSON owned entity. The work is done when this non-composable CTE query can be materialized without the additional outer SELECT projection.

Vom Indexierungsmodell aus dem Issue-Text verfasst.

Bewertung

Tech-Stack
csharp, sql
Bereich
database
Issue-Typ
Bug
Schwierigkeit
4/5
Geschätzter Aufwand
3-5 Tage
Aktivitätsstatus
Veraltet
Klarheit
Größtenteils klar
Anfängerfreundlichkeit
35/100

Neue Issues direkt in Ihr Postfach

Eine kurze Übersicht über anfängerfreundliche GitHub-Issues.