I can't repro this in anything but our own DB but this in LINQPad:
var occ = from oc in OccurrenceView.Where(ov => ov.StaffMemberID == 10082)
where oc.OccurrenceNo == "O1001-09"
select oc;
occ.Count().Dump();
returns straight away while replacing the Count with Any times out:
occ.Any().Dump();
Simplifying the generated SQL to this:
SELECT TOP(1 /* @p4 */) CASE
WHEN EXISTS
(SELECT [LPLA_1].[Occurrence_No]
FROM (SELECT [LPLA_1].[Occurrence_No]
FROM [AQD].[oc_Occ_ViewSecured] [LPLA_1]
WHERE ((([LPLA_1].[Staff_Member_ID] = 10082 /* @p1 */))
AND ([LPLA_1].[Occurrence_No] = 'O1001-09' /* @p2 */))) [LPA_L2]) THEN 1
ELSE 0
END AS [LPFA_1]
FROM [AQD].[oc_Occ_ViewSecured] [LPLA_1]
and it still times out in SSMS but if I change it to:
SELECT TOP(1 /* @p4 */) CASE
WHEN EXISTS
(SELECT COUNT(0)
FROM (SELECT [LPLA_1].[Occurrence_No]
FROM [AQD].[oc_Occ_ViewSecured] [LPLA_1]
WHERE ((([LPLA_1].[Staff_Member_ID] = 10082 /* @p1 */))
AND ([LPLA_1].[Occurrence_No] = 'O1001-09' /* @p2 */))) [LPA_L2]) THEN 1
ELSE 0
END AS [LPFA_1]
FROM [AQD].[oc_Occ_ViewSecured] [LPLA_1]
or
SELECT TOP(1 /* @p4 */) CASE
WHEN EXISTS
(SELECT [Occurrence_No]
FROM (SELECT [LPLA_1].[Occurrence_No]
FROM [AQD].[oc_Occ_ViewSecured] [LPLA_1]
WHERE ((([LPLA_1].[Staff_Member_ID] = 10082 /* @p1 */))
AND ([LPLA_1].[Occurrence_No] = 'O1001-09' /* @p2 */))) [LPA_L2]) THEN 1
ELSE 0
END AS [LPFA_1]
it returns straight away.
Also not actually a LINQ problem QuerySpec also times out
var qa = qf.Create().Select(qf.OccurrenceView.Where(OccurrenceViewFields.StaffMemberID == 10082)
.AndWhere(OccurrenceViewFields.OccurrenceNo == "O1001-09").Any());
var exists = dap.FetchScalar<bool>(qa);
This is in version 5.4.3 but same problem in any version.
We've had to work around this for years and I'm only now getting a chance to report it.