CODEWITHSUNDEEP
.netlinq-to-sqltask-parallel-library
OG Dude
OG Dude

Reputation: 936

Breaking out of Parallel.ForEach over LINQ query

I am looping over a LINQ to SQL mapping using Parallel.ForEach. I break out of the loop once a certain number of elements have been treated. Processing stops, but the loop then hangs for several seconds before I get an error:

System.Data.SqlClient.SqlErrorCollection: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

How do I get the loop to exit normally? Forcing a local collection by appending say a ToList() to the query is no option. I also tried wrapping everything in a using block to no avail. Note, that the

Here is the code:

var query = SomeDataContext.SomeTableMapping; // All ok, if I append Take(maxRecords)
int maxRecords = 1000;
Parallel.ForEach(query, (queryResult, pLoopState, idx) =>
{
    // Do whatever here on queryResult.
    if (idx > maxRecords)
    {
         Console.WriteLine("Reached maximum number of records: {0}", maxRecords);
         pLoopState.Break();
    }
});

Thanks,

/David

Upvotes: 4

Views: 2014

Answers (3)

Panagiotis Kanavos
Panagiotis Kanavos

Reputation: 131219

You should probably check the SQL statements you are executing rather than the loop. The loop will process ALL rows returned by the statement in parallel. Breaking out of the loop will not stop the statement's execution, it will simply stop the processing of the results.

LINQ executes the query when you try to enumerate its results, not when you try to access one of them. In your example, this is the moment you pass the query variable to Parallel.ForEach and it gets converted to an IEnumerable. Parallel.ForEach then takes each result row and tries to process it in parallel.

I suspect you have a large table that you query without any WHERE criteria. As a result, your connection times out after once the default execution timeout elapses (around 60 seconds I think). If you want to retrieve a specific number of rows you should use e TOP statement in SQL or the Take() method in LINQ, e.g. by calling

Parallel.ForEach(query.Take(10),

instead of simply passing the query.

If you want to limit the rows that are returned by a SQL or LINQ statement you should do that in the statement itself instead of trying to limit the results once they are returned. Retrieving unnecessary rows results will slow down your DB server significantly and lead to possible deadlocks.

Upvotes: 0

Gayot Fow
Gayot Fow

Reputation: 8792

Even though the state Break is one of the two ways to stop a parallel loop, breaking out of the delegate does not have any effect on the loop execution because the body of the delegate gets disconnected from the loop structure on each execution. That explains why you observe it hanging on for a while (And I would agree that on first glimpse it seems counter-intuitive that the Break is different from the semantics we are accustomed to in looping constructs). The loop is still running!

The solution will depend upon what you are trying to accomplish in the loop, but you may want to experiment with...

if (pLoopState.ShouldExitCurrentIteration)
{
     return;
}

instead of the Break. That should get you past the part where it appears to hang.

Upvotes: 0

Pawan Mishra
Pawan Mishra

Reputation: 7268

You can use CancellationTokenSource class instance for cancelling parallel loop. For more : How to: Cancel a Parallel.For or ForEach Loop

Important thing to note here is that, when the cancellation token in called inside the parallel loop, the execution of already running iteration will not be stopped. Its just that any new iterations will not be started.

There are other means of breaking/stopping parallel loop using the Break() and Stop() method of ParallelLoopState class. When initializing the Parallel.For/ForEach loop, you can pass an instance of ParallelLoopState class and use that instance to invoke Break/Stop method.

Break method behaves slightly different than Stop. In case of Stop, the framework requests to stop iteration as soon as possible. With Break, the framework request the loop to stop execution of iterations beyond the current iteration as soon as possible. If you are looking for a particular key/text and you want to breakout once you have found, then you should use Stop() method.

Upvotes: 5

Related Questions