Entity Framework，高效的NavigationProperty.OfType查询

Question

我在使用EF4时遇到了麻烦，使用类型对应每个表（TPT）继承构建高效查询困难。

我有一个名为Episode的实体，每个剧集都可以有多个事件。有几种不同类型的事件都是从称为Event的基础实体派生而来的。我想过滤掉不包含某种类型事件的所有剧集。Episode有一个导航属性，它是其所有事件的集合（即基本Event类型的集合）。

我尝试过：

from episode in context.EpisodeSet
where episode.Events.OfType<DerivedEvent>().Count() == 0
select episode

并且

from episode in context.EpisodeSet
where episode.Events.Where(p => p is DerivedEvent).Count() == 0
select episode

这两种方法都会生成一个典型的长SQL扩展，查询每个事件类型表。

难道没有一种用LINQ表达这个查询的方式，只涉及到结果SQL中的剧集和派生事件表之间的连接吗？

编辑： 响应ProfessorX，在此处生成了SQL（基本上只是跨所有事件表的典型大联合）

SELECT 
[Extent1].[Id] AS [Id], 
[Extent1].[TypeId] AS [TypeId], 
[Extent1].[PatientId] AS [PatientId], 
[Extent1].[CentreId] AS [CentreId], 
[Extent1].[CreatedOn] AS [CreatedOn], 
[Extent1].[UpdatedOn] AS [UpdatedOn], 
[Extent1].[CreatedBy] AS [CreatedBy], 
[Extent1].[UpdatedBy] AS [UpdatedBy]
FROM [dbo].[Episode] AS [Extent1]
WHERE  EXISTS (SELECT 
    1 AS [C1]
    FROM   [dbo].[Event] AS [Extent2]
    LEFT OUTER JOIN  (SELECT 
        [Extent3].[Id] AS [Id], 
        cast(1 as bit) AS [C1]
        FROM [dbo].[InvasiveDischargableEvent] AS [Extent3] ) AS [Project1] ON [Extent2].[Id] = [Project1].[Id]
    LEFT OUTER JOIN  (SELECT 
        [UnionAll4].[C1] AS [C1], 
        [UnionAll4].[C2] AS [C2], 
        [UnionAll4].[C3] AS [C3], 
        [UnionAll4].[C4] AS [C4], 
        [UnionAll4].[C5] AS [C5], 
        [UnionAll4].[C6] AS [C6], 
        [UnionAll4].[C7] AS [C7]
        FROM  (SELECT 
            [UnionAll3].[C1] AS [C1], 
            [UnionAll3].[C2] AS [C2], 
            [UnionAll3].[C3] AS [C3], 
            [UnionAll3].[C4] AS [C4], 
            [UnionAll3].[C5] AS [C5], 
            [UnionAll3].[C6] AS [C6], 
            [UnionAll3].[C7] AS [C7]
            FROM  (SELECT 
                [UnionAll2].[C1] AS [C1], 
                [UnionAll2].[C2] AS [C2], 
                [UnionAll2].[C3] AS [C3], 
                [UnionAll2].[C4] AS [C4], 
                [UnionAll2].[C5] AS [C5], 
                [UnionAll2].[C6] AS [C6], 
                [UnionAll2].[C7] AS [C7]
                FROM  (SELECT 
                    [UnionAll1].[Id] AS [C1], 
                    [UnionAll1].[C1] AS [C2], 
                    [UnionAll1].[C2] AS [C3], 
                    [UnionAll1].[C3] AS [C4], 
                    [UnionAll1].[C4] AS [C5], 
                    [UnionAll1].[C5] AS [C6], 
                    [UnionAll1].[C6] AS [C7]
                    FROM  (SELECT 
                        [Extent4].[Id] AS [Id], 
                        cast(0 as bit) AS [C1], 
                        cast(1 as bit) AS [C2], 
                        cast(0 as bit) AS [C3], 
                        cast(0 as bit) AS [C4], 
                        cast(0 as bit) AS [C5], 
                        cast(0 as bit) AS [C6]
                        FROM [dbo].[InvasivePSQ10Event] AS [Extent4]
                    UNION ALL
                        SELECT 
                        [Extent5].[Id] AS [Id], 
                        cast(0 as bit) AS [C1], 
                        cast(0 as bit) AS [C2], 
                        cast(0 as bit) AS [C3], 
                        cast(0 as bit) AS [C4], 
                        cast(0 as bit) AS [C5], 
                        cast(1 as bit) AS [C6]
                        FROM [dbo].[InvasivePostTreatmentEvent] AS [Extent5]) AS [UnionAll1]
                UNION ALL
                    SELECT 
                    [Extent6].[Id] AS [Id], 
                    cast(0 as bit) AS [C1], 
                    cast(0 as bit) AS [C2], 
                    cast(1 as bit) AS [C3], 
                    cast(0 as bit) AS [C4], 
                    cast(0 as bit) AS [C5], 
                    cast(0 as bit) AS [C6]
                    FROM [dbo].[InvasiveTreatmentEvent] AS [Extent6]) AS [UnionAll2]
            UNION ALL
                SELECT 
                [Extent7].[Id] AS [Id], 
                cast(0 as bit) AS [C1], 
                cast(0 as bit) AS [C2], 
                cast(0 as bit) AS [C3], 
                cast(0 as bit) AS [C4], 
                cast(1 as bit) AS [C5], 
                cast(0 as bit) AS [C6]
                FROM [dbo].[InvasiveConsultationEvent] AS [Extent7]) AS [UnionAll3]
        UNION ALL
            SELECT 
            [Extent8].[Id] AS [Id], 
            cast(1 as bit) AS [C1], 
            cast(0 as bit) AS [C2], 
            cast(0 as bit) AS [C3], 
            cast(0 as bit) AS [C4], 
            cast(0 as bit) AS [C5], 
            cast(0 as bit) AS [C6]
            FROM [dbo].[InvasiveMOXFQEvent] AS [Extent8]) AS [UnionAll4]
    UNION ALL
        SELECT 
        [Extent9].[Id] AS [Id], 
        cast(0 as bit) AS [C1], 
        cast(0 as bit) AS [C2], 
        cast(0 as bit) AS [C3], 
        cast(1 as bit) AS [C4], 
        cast(0 as bit) AS [C5], 
        cast(0 as bit) AS [C6]
        FROM [dbo].[InvasiveReferralEvent] AS [Extent9]) AS [UnionAll5] ON [Extent2].[Id] = [UnionAll5].[C1]
    WHERE ([Extent1].[Id] = [Extent2].[EpisodeId]) AND (CASE WHEN (( NOT (([UnionAll5].[C2] = 1) AND ([UnionAll5].[C2] IS NOT NULL))) AND ( NOT (([UnionAll5].[C3] = 1) AND ([UnionAll5].[C3] IS NOT NULL))) AND ( NOT (([UnionAll5].[C4] = 1) AND ([UnionAll5].[C4] IS NOT NULL))) AND ( NOT (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL))) AND ( NOT (([UnionAll5].[C5] = 1) AND ([UnionAll5].[C5] IS NOT NULL)))) THEN '2X' WHEN (([UnionAll5].[C5] = 1) AND ([UnionAll5].[C5] IS NOT NULL)) THEN '2X0X' WHEN (([Project1].[C1] = 1) AND ([Project1].[C1] IS NOT NULL) AND ( NOT (([UnionAll5].[C6] = 1) AND ([UnionAll5].[C6] IS NOT NULL))) AND ( NOT (([UnionAll5].[C7] = 1) AND ([UnionAll5].[C7] IS NOT NULL)))) THEN '2X1X' WHEN (([UnionAll5].[C4] = 1) AND ([UnionAll5].[C4] IS NOT NULL)) THEN '2X2X' WHEN (([UnionAll5].[C2] = 1) AND ([UnionAll5].[C2] IS NOT NULL)) THEN '2X3X' WHEN (([UnionAll5].[C6] = 1) AND ([UnionAll5].[C6] IS NOT NULL)) THEN '2X1X0X' WHEN (([UnionAll5].[C7] = 1) AND ([UnionAll5].[C7] IS NOT NULL)) THEN '2X1X1X' ELSE '2X4X' END LIKE '2X4X%')
)

- James Gaunt

---

据我所知，这个问题无法解决。目前TPT查询效率非常低下。 - Ladislav Mrnka

最后我终于找到了一种方法使它工作了，并在下面回答了自己的问题。我同意使用TPT存在一些陷阱，但是到目前为止，通过仔细使用OfType，我已经成功地解决了所有问题。但我不确定好处是否超过了额外的注意。 - James Gaunt

Answer 1

经过许多思考，我终于成功了：

var episodes = (from episode in context.EpisodeSet
                join e in context.EventSet.OfType<DerivedEvent>() on episode.Id equals e.EpisodeId into outer
                from o in outer.DefaultIfEmpty()
                where o == null
                select episode)

因此，我不得不将OfType筛选器应用于ObjectSet并进行外连接，而不是尝试将其应用于导航属性。似乎OfType和“as”类型过滤不适用于导航属性。

这会生成在DerivedEvent表中没有相应事件的情况下的剧集，以及手动编写的SQL。LINQ遵循您自然编写SQL查询的方式。只是太容易陷入所有这些导航属性的诱惑，这些导航属性会导致看起来漂亮的LINQ但可怕的SQL。

- James Gaunt

Answer 2

.任何时候使用.Any()都比使用.Count()更好 从我的角度来看，您的查询可以进行优化：

context.EpisodeSet
.Where(e => e.Events.Any(p => p is DerivedEvent))
.Select(e => e);

- Ahmed Magdy

---

谢谢你的提示。我已经修复了，但不幸的是它并没有解决跨越所有事件类型表连接的SQL问题。 - James Gaunt

我已经添加了使用Any查询的SQL结果。不确定它有多有用，因为它与TPT继承一起得到的SQL通常难以阅读。但是，您可以看到它正在跨多个事件表进行查询，而不仅仅是在单个派生事件表上进行连接（在此情况下为PSQ10Event表）。 - James Gaunt