I have a L2S query with several joins that should returns 11 records in about 3 seconds. However, it times out after 30 seconds unless I specify a Take parameter (i used Take(20) even though it only returns 11 records) in which case it returns in the expected time frame of 3 seconds with the 11 records.
The query looks like this:
(from q in TransmittalDetails where q.TransmittalHeader.TransmittalEntityID == 196
&& q.TransmittalHeader.DateRangeBeginTimeID == 20100101
&& q.TransmittalHeader.ScenarioID == 2
&& q.LineItem.AccountType.AccountCategory.AccountGroup.
AccountSummary.AccountSummaryID == 6
select new {
q.LineItem.AccountType.AccountCategory.AccountGroup.AccountGroupID,
q.LineItem.AccountType.AccountCategory.AccountGroup.AccountGroup1
}).Distinct()
This generates some SQL that looks like this:
DECLARE @p0 Int = 196
DECLARE @p1 Int = 20100101
DECLARE @p2 Int = 2
DECLARE @p3 Int = 6
SELECT DISTINCT [t5].[AccountGroupID], [t5].[AccountGroup] AS [AccountGroup1]
FROM [dbo].[TransmittalDetail] AS [t0]
INNER JOIN [dbo].[TransmittalHeader] AS [t1] ON [t1].[TransmittalHeaderID] =
[t0].[TransmittalHeaderID]
INNER JOIN [dbo].[LineItem] AS [t2] ON [t2].[LineItemID] = [t0].[LineItemID]
LEFT OUTER JOIN [dbo].[AccountType] AS [t3] ON [t3].[AccountTypeID] =
[t2].[AccountTypeID]
LEFT OUTER JOIN [dbo].[AccountCategory] AS [t4] ON [t4].[AccountCategoryID] =
[t3].[AccountCategoryID]
LEFT OUTER JOIN [dbo].[AccountGroup] AS [t5] ON [t5].[AccountGroupID] =
[t4].[AccountGroupID]
LEFT OUTER JOIN [dbo].[AccountSummary] AS [t6] ON [t6].[AccountSummaryID] =
[t5].[AccountSummaryID]
WHERE ([t1].[TransmittalEntityID] = @p0) AND ([t1].[DateRangeBeginTimeID] = @p1)
AND ([t1].[ScenarioID] = @p2) AND ([t6].[AccountSummaryID] = @p3)
Now, the truly bizarre part is that if I execute that SQL in Management Studio, it returns 11 rows in 3 seconds, yet the linq query that generates it will timeout after 30 seconds of activity.
Specifying the Take parameter just doesn't make much sense. Am I hitting some bug?
Note: The code times out without the Take() parameter whether i execute it from my app, or Linqpad. Likewise, in both app and linqpad it works fine with a Take() parameter. Also, without the distinct it only returns 19 rows.