CODEWITHSUNDEEP
c#linqlinq-to-sql
frenchie
frenchie

Reputation: 51947

Linq query generates timeouts

I have a query that looks like this: 

var TheDataToDelete = (from x in MyDC.SomeTable
                       where x.....
                       select x).ToList();

if (TheDataToDelete.Count > 0)
{
  MyDC.SomeTable.DeleteAllOnSubmit(TheDataToDelete);
  MyDC.SubmitChanges();
}

There are about 10K rows to delete and in my error logs I sometimes (once a week) see this error:

Inner Exception Type: System.ComponentModel.Win32Exception
Inner Exception: The wait operation timed out
Inner Source: 
Exception Type: System.Data.SqlClient.SqlException
Exception: Timeout expired.  The timeout period elapsed prior to completion of the operation or the server is not responding.

What's wrong with my code and what do I need to change to fix it?

Upvotes: 0

Views: 1428

Answers (1)

Kolky
Kolky

Reputation: 2977

As @Shyju suggested, use Any(). But also remove ToList() as it will already execute the query. Not adding it will make the query have lazy-execution. And because of then only doing Any() the query will be more efficient.

var TheDataToDelete = (from x in MyDC.SomeTable
                       where x.....
                       select x);

if (TheDataToDelete.AnY())
{
    MyDC.SomeTable.DeleteAllOnSubmit(TheDataToDelete);
    MyDC.SubmitChanges();
}

Which will produce the SQL query;

SELECT
    (CASE
        WHEN EXISTS(
            SELECT x 
            FROM MyDC.SomeTable
            WHERE x
            ) THEN 1
        ELSE 0
     END) AS [value]

Which is fast and efficient.

Upvotes: 6

Related Questions