计数每类型表继承中的查询生成

Question

我有一个典型的TPT场景(不要问我为什么要做TPT，一开始这可能是一个愚蠢的选择)，我正尝试在基类型上运行一个简单的计数查询。考虑到计数是基于基类型和基类上的属性的，我不明白为什么EF通过将所有派生类型连接在一起来创建COUNT(*)查询。

这些类如下所示：

public abstract class StudyCase {
    public DateTime? DateSubmitted { get; protected set; }
    public bool Deleted { get; set; }
}

public class StudyCaseStandard : StudyCase { 
// ... other stuff
}

public class StudyCaseReview : StudyCase { 
// ... other stuff
}

我的查询是这样的

SubmittedCasesCount = _context.Set<StudyCase>().Where(sc => !sc.Deleted).Count(sc => sc.DateSubmitted.HasValue);

这是EF生成的查询(上面没有显示，但在查询中可以看到第三个类)：

SELECT 
    [GroupBy1].[A1] AS [C1]
    FROM ( SELECT 
        COUNT(1) AS [A1]
        FROM   (SELECT 
            [Extent1].[Id] AS [Id]
            FROM [dbo].[StudyCases_Exacerbation] AS [Extent1]
        UNION ALL
            SELECT 
            [Extent2].[Id] AS [Id]
            FROM [dbo].[StudyCases_Standard] AS [Extent2]
        UNION ALL
            SELECT 
            [Extent3].[Id] AS [Id]
            FROM [dbo].[StudyCases_Review] AS [Extent3]) AS [UnionAll2]
        INNER JOIN [dbo].[StudyCases] AS [Extent4] ON [UnionAll2].[Id] = [Extent4].[Id]
        WHERE ([Extent4].[Deleted] <> 1) AND ([Extent4].[DateSubmitted] IS NOT NULL)
    )  AS [GroupBy1]
go

正如你所看到的，它正在做所有的UNION和JOIN，而像这样做一个简单的计数会更有效率(也是唯一必要的)：

SELECT COUNT(1) AS [A1]
FROM [dbo].[StudyCases]
WHERE ([Deleted] <> 1) AND ([DateSubmitted] IS NOT NULL)

当不需要派生类的属性时，或者当返回集只是基类的集合时(这也可能取决于基类上的abstract关键字，但EF应该能够实现抽象类的代理)，你知道如何强制EF生成更简单的查询吗？

Answer 1

如果使用select语句仅从基类中提取属性，则会生成什么查询，如下所示：

SubmittedCasesCount = 
    _context
        .Set<StudyCase>()
        .Select(sc => new { Deleted = sc.Deleted, DateSubmitted = sc.DateSubmitted })
        .Where(anon => !anon.Deleted)
        .Count(anon => anon.DateSubmitted.HasValue);

编辑:不幸的是，上面产生了相同的查询，我所知道的唯一其他解决方案是执行一个原始的SQL查询，类似于：

int count = context.Set<StudyCase>
                   .FromSql("SELECT Deleted, DateSubmitted FROM dbo.StudyCases")
                   .Where(sc => !sc.Deleted).Count(sc => sc.DateSubmitted.HasValue)
                   .Count();