Prune unneeded tables in TPT queries
- 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
Assessment
This issue has not been assessed yet.