title: Entity Framework, efficient NavigationProperty.OfType query
body: <p>I'm having trouble constructing an efficient query in EF4 using type per table (TPT) inheritance.</p>
  
  <p>I have an entity called <strong>Episode</strong>,  and each episode can have multiple events. There several different type of events all deriving from a base entity called <strong>Event</strong>.  I want to filter on all episodes that don't contain a certain type of event.  <strong>Episode</strong> has a navigation property that is a collection of all its events (i.e. a collection of the base <strong>Event</strong> type)</p>
  
  <p>I've tried:</p>
  
  <pre><code>from episode in context.EpisodeSet
  where episode.Events.OfType&lt;DerivedEvent&gt;().Count() == 0
  select episode
  </code></pre>
  
  <p>and</p>
  
  <pre><code>from episode in context.EpisodeSet
  where episode.Events.Where(p =&gt; p is DerivedEvent).Count() == 0
  select episode
  </code></pre>
  
  <p>Both of these produce a typical long SQL expansion that queries every <strong>Event</strong> type table.  </p>
  
  <p>Shouldn't there be a way to express this query in LINQ that just involves a join between the <strong>Episode</strong> and the <strong>DerivedEvent</strong> table in the resulting SQL?</p>
  
  <p><strong>Edit:</strong> 
  In response to ProfessorX here is the generated SQL (basically just a typical massive union across all event tables)</p>
  
  <pre><code>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%')
  )
  </code></pre>