实体框架，高效NavigationProperty.OfType查询 [英] Entity Framework, efficient NavigationProperty.OfType query
本文介绍了实体框架，高效NavigationProperty.OfType查询的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我无法使用每个表类型（TPT）继承构建在EF4一个高效的查询。
我有一个名为插曲和每一集可以具有多个事件。有几种不同类型的事件从基实体的所有衍生名为事件。我想在不包含特定类型的事件的所有剧集过滤。 插曲有一个导航属性，是其所有活动的集合
（即基本的活动类型的集合）
我试过：
从context.EpisodeSet
情节，episode.Events.OfType< DerivedEvent> （）.Count之间的（）== 0
选择插曲
和
从context.EpisodeSet
情节，episode.Events.Where（p =指p是DerivedEvent）.Count之间的（）== 0
选择插曲
这两个产生一个典型的长SQL扩展，查询每个<强>事件类型的表。
不应该有表达的LINQ查询这只是涉及到的集之间加入方式和 DerivedEvent 表中生成的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]
从[DBO]。[插曲] AS [Extent1]
WHERE EXISTS（SELECT
1 AS [C1]
FROM [DBO ]。[事件] AS [Extent2]
LEFT OUTER JOIN（SELECT
[Extent3]。[ID] AS [ID]，
投（1为位）AS [C1]
从[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]。[6] AS [C6]，
[UnionAll4]。[C7 ] AS [C7]
FROM（SELECT
[UnionAll3]。[C1] AS [C1]，
[UnionAll3]。[C2] AS [C2]，
[UnionAll3 ]。[C3]的AS〔C3〕，
[UnionAll3]。[4] AS〔C4〕，
[UnionAll3]。[C5] AS [C5]，
[UnionAll3]。 [6] AS [6]，
[UnionAll3]。[C7] AS [C7]
FROM（SELECT
[UnionAll2]。[C1] AS [C1]，
[UnionAll2]。〔C2〕的AS〔C2〕，
[UnionAll2]。[C3]的AS〔C3〕，
[UnionAll2]。[4] AS〔C4〕，
[UnionAll2 ]。[C5] AS [C5]，
[UnionAll2]。[6] AS [6]，
[UnionAll2]。[C7] AS [C7]
FROM（SELECT
[UnionAll1]。[ID] AS [C1]，
[UnionAll1]。[C1] AS [C2]，
[UnionAll1]。[C2] AS [C3]，
[UnionAll1]。[C3]的AS〔C4〕，
[UnionAll1]。[4] AS [C5]，
[UnionAll1]。[C5] AS [C6]，
[UnionAll1 ]。[6] AS [C7]
FROM（SELECT
[Extent4]。[ID] AS [ID]，
投（0为位）AS [C1]，
铸钢（1为位）AS [C2]，
投（0为位）AS [C3]，
投（0为位）AS [4]，
投（0作为位）AS [C5]，
投（0为位）AS [6]
从[DBO]。[InvasivePSQ10Event] AS [Extent4]
UNION ALL
选择
[Extent5]。[ID] AS [ID]，
投（0为位）AS [C1]，
投（0为位）AS [C2]，
投（ 0作为位）AS [C3]，
投（0为位）AS [4]，
投（0为位）AS [C5]，
投（1为位）AS [6]
从[DBO]。[InvasivePostTreatmentEvent] AS [Extent5]）AS [UnionAll1]
UNION ALL
选择
[Extent6]。[ID] AS [ID] ，
投（0为位）AS [C1]，
投（0为位）AS [C2]，
投（1为位）AS [C3]，
投（0为位）AS [4]，
投（0为位）AS [C5]，
投（0为位）AS [6]
从[DBO]。[ InvasiveTreatmentEvent] AS [Extent6]）AS [UnionAll2]
UNION ALL
选择
[Extent7]。[ID] AS [ID]，
投（0为位）AS [ C1]，
投（0为位）AS [C2]，
投（0为位）AS [C3]，
投（0为位）AS [4]，
投（1为位）AS [C5]，
投（0为位）AS [6]
从[DBO]。[InvasiveConsultationEvent] AS [Extent7]）AS [UnionAll3]
UNION ALL
选择
[Extent8]。[ID] AS [ID]，
投（1为位）AS [C1]，
投（0为位） AS [C2]，
投（0为位）AS [C3]，
投（0为位）AS [4]，
投（0为位）AS [C5]
投（0为位）AS [6]
从[DBO]。[InvasiveMOXFQEvent] AS [Extent8]）AS [UnionAll4]
UNION ALL
选择
[Extent9]。[ID] AS [ID]，
投（0为位）AS [C1]，
投（0为位）AS [C2]，
投（0作为位）AS [C3]，
投（1为位）AS [4]，
投（0为位）AS [C5]，
投（0为位）AS [C6 ]
从[DBO]。[InvasiveReferralEvent] AS [Extent9]）AS [UnionAll5] ON [Extent2]。[ID] = [UnionAll5] [C1]
WHERE（[Extent1]。[ID ] = [Extent2]。[EpisodeId]）AND（CASE WHEN（（NOT（（UnionAll5]。〔C2〕= 1）和（[UnionAll5]。〔C2〕IS NOT NULL）））AND（NOT（（ UnionAll5]。[C3] = 1）与（[UnionAll5]。[C3] IS NOT NULL）））AND（NOT（（UnionAll5]。[4] = 1）与（[UnionAll5]。[C4]的IS NOT NULL）））AND（NOT（（[PROJECT1]。[C1] = 1）和（[PROJECT1]。[C1] IS NOT NULL）））AND（NOT（（[UnionAll5]。[C5] = 1） （[UnionAll5]。[C5] IS NOT NULL））））THEN2XWHEN（（[UnionAll5]。[C5] = 1）和（[UnionAll5]。[C5] IS NOT NULL））THEN'2X0X WHEN（（[PROJECT1]。[C1] = 1）和（[PROJECT1]。[C1] IS NOT NULL）AND（NOT（（[UnionAll5]。[6] = 1）和（[UnionAll5]。[6] IS NOT NULL）））AND（NOT（（[UnionAll5]。[C7] = 1）和（[UnionAll5]。[C7] IS NOT NULL））））THEN'2X1XWHEN（（[UnionAll5]。[C4 ] = 1）和（[UnionAll5]。[4] IS NOT NULL））THEN'2X2XWHEN（（[UnionAll5]。[C2] = 1）和（[UnionAll5]。[C2] IS NOT NULL））THEN 2X3X当（（[UnionAll5]。[6] = 1）和（[UnionAll5]。[6] IS NOT NULL））THEN'2X1X0XWHEN（（[UnionAll5]。[C7] = 1）和（[ UnionAll5]。[C7] IS NOT NULL））THEN'2X1X1XELSE2X4X'END LIKE'2X4X％'）
）
解决方案
很多挠头我已经成功地得到这个工作后：
VAR发作=（从context.EpisodeSet
插曲加入E在context.EventSet.OfType< DerivedEvent>在episode.Id（）等于e.EpisodeId到来自外太空
o在outer.DefaultIfEmpty（）
其中O == NULL
选择插曲）
因此，而不是试图将OfType过滤器应用到导航属性我已经把它应用到对象集，做一个外部联接。似乎OfType和'为'类型过滤不针对导航属性工作
这产生不具有在DerivedEvent表的相应事件的发作，和同样的SQL，你将手工编写。
在LINQ遵循你写自然与SQL查询的方式。这只是太容易得到所有这些导航属性，导致好看的LINQ但可怕的寻找SQL诱惑。
I'm having trouble constructing an efficient query in EF4 using type per table (TPT) inheritance.
I have an entity called Episode, and each episode can have multiple events. There several different type of events all deriving from a base entity called Event. I want to filter on all episodes that don't contain a certain type of event. Episode has a navigation property that is a collection of all its events (i.e. a collection of the base Event type)
I've tried:
from episode in context.EpisodeSet
where episode.Events.OfType<DerivedEvent>().Count() == 0
select episode
and
from episode in context.EpisodeSet
where episode.Events.Where(p => p is DerivedEvent).Count() == 0
select episode
Both of these produce a typical long SQL expansion that queries every Event type table.
Shouldn't there be a way to express this query in LINQ that just involves a join between the Episode and the DerivedEvent table in the resulting SQL?
Edit: In response to ProfessorX here is the generated SQL (basically just a typical massive union across all event tables)
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%')
)
解决方案
After much head scratching I've managed to get this to work:
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)
So rather than try to apply an OfType filter to the navigation property I've had to apply it to the ObjectSet and do an outer join. Seems that OfType and 'as' type filtering don't work against Navigation Properties.
This produces the episodes that don't have a corresponding event in the DerivedEvent table, and with the kind of SQL that you would write by hand.
The LINQ follows the way you'd naturally write the query with SQL. It's just too easy to get seduced by all these navigation properties that lead to nice looking LINQ but awful looking SQL.
这篇关于实体框架，高效NavigationProperty.OfType查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
