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

本文关键字：查询 类型 继承 | 更新日期: 2023-09-27 18:16:05

我有一个典型的TPT场景(不要问我为什么使用TPT，这可能是一个愚蠢的选择)，我试图在BASE类型上运行一个简单的Count查询。考虑到计数是基于基类型和基类上的属性，我不明白为什么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)

任何想法如何强制EF生成更简单的查询时，派生类的属性不需要或当返回集是基类的一组(这可能取决于abstract关键字对基类以及，但EF应该能够实现一个抽象类的代理)?

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

如果使用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();
相关文章：