It was crashing. I think I have sorted that now. I have also stripped out the predicate and used it on the whole link. It looks a bit clearer that way:
public IQueryable<TopicTextEntity> CreateDataSource()
{
var languageCode = HttpContext.Current.Request.QueryString["LanguageCode"] ?? "en";
var topicTextEntities = ((from a in metaData.TopicText
where a.ReadyforPublication == false && a.LanguageCode == languageCode
select a).IncludeFields(a => a.Topic)
.WithPath(d => d.Prefetch<TopicBaseEntity>(e => e.TopicBase)
.SubPath(f => f.Prefetch<TopicRegionEntity>(g => g.TopicRegion)
.SubPath(h => h.Prefetch<RegionBaseEntity>(i => i.Region)
.SubPath( j => j.Prefetch<CountriesByConsultantEntity>( k => k.CountriesByConsultant)
.SubPath( l => l.Prefetch <ActiveDirectoryLogonEntity>( m => m.ConsultantsLogon)))))))
.Where( n => n.TopicBase.TopicRegion.Any( o => o.Region.CountriesByConsultant.Where(p => p.ConsultantsLogon.Logon == userInfo.Username). Count() > 0));
return topicTextEntities;
}
I needed to understand what .Any and .All do:-)
The SQL is very fast (5x faster than my old joined tables script!) so well done to the team for knowing how to get high performing - if a little obscure - script:
I am still not sure my .Count() > 0 is a good thing though even though it seems to work. I have used it as the .Where needs a boolean to be returned and I couldn't think of a simpler way to do that. So any suggestions on this point would be most welcome.
I don't understand the SQL well enough to see if there are any pitfalls:
DECLARE @ReadyforPublication1 BIT = 0
DECLARE @LanguageCode2 varchar(6) = 'en'
DECLARE @Logon4 nvarchar(50) = 'fred'
DECLARE @LPFA_13 INT = 0
SELECT TOP 1
COUNT(*) AS [LPAV_]
FROM ( [dbo].[v2_Topic_Base] [LPA_L1]
INNER JOIN [dbo].[v2_Topic_Text] [LPA_L2] ON [LPA_L1].[IDTopic] = [LPA_L2].[IDTopic]
)
WHERE ( ( ( ( ( ( ( ( [LPA_L2].[ReadyforPublication] = @ReadyforPublication1 )
AND ( [LPA_L2].[LanguageCode] = @LanguageCode2 ))))
)
AND EXISTS ( SELECT [LPLA_3].[IDTopic]
FROM [dbo].[v2_TopicRegion] [LPLA_3]
WHERE ( [LPA_L1].[IDTopic] = [LPLA_3].[IDTopic]
AND ( ( SELECT COUNT(*) AS [LPAV_]
FROM ( ( [dbo].[REGION] [LPA_L3]
INNER JOIN [dbo].[CountriesByConsultant] [LPA_L4] ON [LPA_L3].[CountryCode] = [LPA_L4].[CountryCode]
AND [LPA_L3].[RegionCode] = [LPA_L4].[RegionCode]
)
INNER JOIN [dbo].[ConsultantsLogon] [LPA_L5] ON [LPA_L5].[IDCounter] = [LPA_L4].[IDContact]
)
WHERE ( ( [LPA_L3].[CountryCode] = [LPLA_3].[CountryCode]
AND [LPA_L3].[RegionCode] = [LPLA_3].[RegionCode]
)
AND ( [LPA_L5].[Logon] = @Logon4 )
)
) > @LPFA_13 )
) )))
)
