CODEWITHSUNDEEP
c#.netsql-server-2008linq-to-sql
Eitan
Eitan

Reputation: 1504

TimeoutException not being thrown Linq to SQL DataContext.CommandTimeout C#

I'm trying to throw a TimeoutException whenever it takes longer than 4 minutes to execute a stored procedure call in Linq to SQL.

Here is the code in my controller:

public IEnumerable<Quality> Get(DateTime param1, DateTime param2, string param3)
        {     

            var dc = new VideoDataContext(WebApplication.MonitorServer);

            dc.CommandTimeout = 240;

            List<Quality> cqs = dc.Vid_GetQualityForVideo(param1, param2, 
            param3).ToList();


            return cqs;
        }

For some reason the code executes for longer (way longer) than 4 minutes but doesn't throw a timeout exception. In the end I get a Memory exception for really big queries.

I can't optimize the query or even see it because I don't have access to the stored procedures. I can only detect a timeout in the application. Does anyone know why I'm not getting a TimeoutException.

In other places in my code I have very similar code and the timeout is thrown after 4 minutes on another server holding a different database. It's just not happening here.

I can write to the Data team to change something in their but I'd like to be specific as to what the problem is.

Thanks!

Edit: To clarify I saw before when I retrieved data it would take forever and noticed that it was the massive amount of data being passed to the client. I have an MVC Action Filter that detects the number of rows and if it's more than a certain threshold (10000 right now) it only passes an error message to the client and not the entire dataset. The queries I'm running return way more than the threshold but it's still taking longer than 4 minutes to complete.

Upvotes: 1

Views: 1068

Answers (2)

LINQ2Vodka
LINQ2Vodka

Reputation: 3036

Probably request worked well and your large data being received via network. In this case no TimeOut exception should occur.

Upvotes: 0

Adam Kewley
Adam Kewley

Reputation: 1234

If your query is large the .ToList() may cause performance issues. The IEnumerable<T> you get from the query is lazily evaluated. However, calling a .ToList() will iterate through the entire contents of the result, putting it into the resulting List (cqs).

If you truly wish the return of your function to be IEnumerable<Quality> then you may be able to just return the query IEnumerable:

public IEnumerable<Quality> Get(DateTime param1, DateTime param2, string param3)
    {     

        var dc = new VideoDataContext(WebApplication.MonitorServer);

        dc.CommandTimeout = 240;

        return dc.Vid_GetQualityForVideo(param1, param2, param3);
    }

Edit:

To clarify, if you are getting a memory flow, not a timeout flow, I would suspect that evaluating the IEnumerable into cqs is filling memory as quickly as the IEnumerable iterates. I would use LINQ queries (Filter, etc.) to truncate the resulting IEnumerable.

Also (reminded in comments), I'll put my money on the reason you are not getting a timeout is because dc.Vid_GetQualityForVideo(param1, param2, param3) evaluates successfully. The following ToList() called upon it returning is causing the overflow. Doing some basic breakpoints checks on it would confirm this.

Upvotes: 1

Related Questions