Entity Framework, requête NavigationProperty.OfType efficace

Je ne parviens pas à créer une requête efficace dans EF4 en utilisant l’inheritance type par table (TPT).

J’ai une entité appelée Episode , et chaque épisode peut avoir plusieurs événements. Il existe plusieurs types d’événements différents, tous dérivés d’une entité de base appelée Event . Je souhaite filtrer tous les épisodes ne contenant pas un certain type d’événement. Episode a une propriété de navigation qui est une collection de tous ses événements (c’est-à-dire une collection du type d’ événement de base)

J’ai essayé:

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

et

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

Ces deux produits produisent une longue extension SQL typique qui interroge chaque table de type d’ événement .

Ne devrait-il pas y avoir un moyen d’exprimer cette requête dans LINQ qui implique simplement une jointure entre l’ épisode et la table DerivedEvent dans le code SQL résultant?

Edit: En réponse à ProfessorX, voici le code SQL généré (essentiellement une union massive typique de toutes les tables d’événements)

 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%') ) 

Après avoir passé beaucoup de temps à me gratter la tête, j’ai réussi à faire fonctionner ceci

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

Par conséquent, plutôt que d’essayer d’appliquer un filtre OfType à la propriété de navigation, j’ai dû l’appliquer à ObjectSet et d’effectuer une jointure externe. Il semble que OfType et le filtrage de type ‘as’ ne fonctionnent pas avec les propriétés de navigation.

Cela produit les épisodes qui n’ont pas d’événement correspondant dans la table DerivedEvent, et avec le type de code SQL que vous écririez à la main.

Le LINQ suit la manière dont vous écrivez naturellement la requête avec SQL. Il est tout simplement trop facile de se laisser séduire par toutes ces propriétés de navigation qui conduisent à un joli LINQ mais à un SQL affreux.

.Tout () est meilleur que .Count () De ma prospective, votre requête peut être optimisée pour:

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