dotnet / dotnet/efcore

Prune unneeded tables in TPT queries

Open
#31,304 1 comment 1 reaction 0 assignees View on GitHub
area-perf area-query customer-reported
Dominant language
C#
Stars
14.8k
Forks
3.4k
PR merge metrics
PR metrics pending

Description

A question regarding the count query generated when a TPT entity count is performed.

Assume the structure as per the docs, but with some other FK on the derived entity:
```cs
public class Blog
{
public int BlogId { get; set; }
public string Url { get; set; }
}

public class RssBlog : Blog
{
public string RssUrl { get; set; }
public int SiteId { get; set; } // FK to another type, we will filter on this
}

```

When I perform a simple filtered count on RssBlog:

```cs
var siteRssBlogCount = await myContext.RssBlogs
.AsNoTracking()
.Where(x => x.SiteId == someSiteId)
.CountAsync();
```

it generates a query that looks like this:

```sql
SELECT count ( * ) :: int
FROM Blogs b
INNER JOIN RssBlogs r ON b.Id = r.Id
WHERE r.SiteId =
```

### The question:

Is there any reason it couldn't bypass the base class table `Blogs` and just do a straight count on `RssBlogs`? The FK on the derived table ensures that a base table row must exist for each derived table row.
eg:

```sql
SELECT count ( * ) :: int
FROM RssBlogs r
WHERE r.SiteId =
```

We have real-world cases where this join to the base type table causes query performance to be significantly slower. See explain output summaries:

```
-- For EF generated count query that joins to base table:
-- QUERY PLAN
-- Planning Time: 0.480 ms
-- Execution Time: 241.689 ms

-- For a count query that only uses the derived type table:
-- QUERY PLAN
-- Planning Time: 0.083 ms
-- Execution Time: 14.113 ms

```

I understand it's probably not straightforward to _just_ implement a change like this, but I was curious if there were any cases that I wasn't aware of that would prevent this from working, or if there was a nice way to tell EF to **not** use the base table in this case. Apologies if this belongs in Npgsql instead of here.

### Provider and version information

EF Core version: 7.0.9
Database provider: Npgsql
Target framework: .NET 7.0

Contributor guide

Open the contributing guide

Assessment

This issue has not been assessed yet.

Get new issues in your inbox

A short digest of beginner-friendly GitHub issues.