Why does this LINQ statement throw a timeout error?

Question

I am getting this error when I execute this line, through break points I detected this error.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

int? dressSerialNo;
var lstDress = (
    from yy in currContext.OrderDressings
    where yy.OrderID == this.OrderID
        && yy.OrderItemID == this.orderItemID
        && yy.ProductID == this.ProductID
    select yy
    ).ToList();
if (lstDress.Count > 0)
{
    dressSerialNo = (
        from yy in lstDress
        where yy.OrderID == this.OrderID
            && yy.OrderItemID == this.orderItemID
            && yy.ProductID == this.ProductID
        select (int?)yy.SrNo
        ).Max();
    dressSerialNo += dressSerialNo + 1;
}
else dressSerialNo = 1;

solution:- In my project I was mainlining transaction in two for some module old method with ado.net and for newly developed modules I was using the entity framework so it was creating problem in transaction. So it went aberrant.

Always use `seq.Any()` instead of `seq.Count > 0`. Even if it is a list here it is more readable to use `Any` and also more efficient if you remove the `ToList` and use `seq.Count() > 0` instead. You want o know if it contains any elements, you don't want to know the total count. — Tim Schmelter, Nov 29 '13 at 13:55
Fire up the SQL profiler, capture the query, run it in SSMS and analyze the query plan. There's really nothing we can say about this, apart from the obvious "try this" guessing game. — CodeCaster, Nov 29 '13 at 13:56
@SriramSakthivel: As long as you use .`Any()` with `.ToList()` should be fine. Otherwise, I guess it will be expensive — huMpty duMpty, Nov 29 '13 at 13:58
@SriramSakthivel: He doesn't need a list and he'll remove the `ToList`. I am sure that you would change the `lstDress.Count > 0` to `lstDress.Count() > 0` then. So why not using the more readable and correct `Any` in the first place even if it's a collection? — Tim Schmelter, Nov 29 '13 at 14:02
@TimSchmelter If it is a sequence I agree with you you need to call `Count() > 0` which is stupid, but for List or Array **no** I disagree with you, `Any` is gonna create an `Enumerator` and try to call `MoveNext`, then dispose it, it goes as a garbage which is subject to "Garbage collection"!. Why not use pre-computed O(1) property `Count` or `Length` for arrays? — Sriram Sakthivel, Nov 29 '13 at 14:13
This question appears to be off-topic because it is about performance and timeout issues only diagnoseable in the whole context — rene, Dec 09 '13 at 13:15

score 2 · Answer 1 · edited May 23 '17 at 12:28

2

You are using Linq-To-Entities. There is an issue with the connection to your database server. Common causes for this are:

The query is taking longer than the timeout specified in the context.
There are network related issues causing a delay.

You can optionally change the command timeout (see this question about how to do this).

edited May 23 '17 at 12:28

Community

1
1

answered Nov 29 '13 at 13:58

Bas

26,772
8
53
86

score 2 · Answer 2 · answered Nov 29 '13 at 14:00

You don't want to materialize your all your entities using .ToList().

You could write a single query that returns only what you're interested in:

// Get the entity that contains the max value, using Ordering
var myMaxIfAny = currContext.OrderDressings
    .Where(yy => yy.OrderID == this.OrderID && yy.OrderItemID == this.orderItemID && yy.ProductID == this.ProductID)
    .OrderByDescending(z => z.SrNo)
    .FirstOrDefault();

if (myMaxIfAny != null)
{
    // Then you got a value, retrieve the Max using myMaxIfAny.SrNo
    // ...
}
else
{
    // ...
}

score 2 · Answer 3 · answered Nov 29 '13 at 14:06

I've formatted and commented your code:

int? dressSerialNo;

// Get all OrderDressings with matching OrderID, orderItemID and ProductID as a List<OrderDressing>
var lstDress = (from yy in currContext.OrderDressings 
                where yy.OrderID == this.OrderID 
                   && yy.OrderItemID == this.orderItemID 
                   && yy.ProductID == this.ProductID 
                select yy)
                .ToList();

                // If any were found,               
                if (lstDress.Count > 0)
                {
                    // Execute the Where again (what else will the list contain?) and select all yy.SrNo
                    dressSerialNo = (from yy in lstDress 
                                     where yy.OrderID == this.OrderID 
                                        && yy.OrderItemID == this.orderItemID 
                                        && yy.ProductID == this.ProductID 
                                    select (int?)yy.SrNo)
                                    .Max();     // And take the Max() of that

                    // Add dressSerialNo + 1 to dressSerialNo.
                    dressSerialNo += dressSerialNo + 1;
                }

                else dressSerialNo = 1;

Which seems it can be corrected and reduced to:

int? serialNumber = (from yy in currContext.OrderDressings 
                     where yy.OrderID == this.OrderID 
                        && yy.OrderItemID == this.orderItemID 
                        && yy.ProductID == this.ProductID 
                     select yy.SrNo)
                     .DefaultIfEmpty() // Might not be necessary
                     .Max();

if (!serialNumber.HasValue)
{
    serialNumber = 1;
}
else
{
    serialNumber++;
}

Please note this can cause concurrency issues if two people execute this at the same time.

You get the same error if you run the second code block? Then see my [comment about the SQL Profiler](http://stackoverflow.com/questions/20287368/why-does-this-linq-statement-throw-a-timeout-error/20287570?noredirect=1#comment30266762_20287368). — CodeCaster, Nov 29 '13 at 14:14

score 1 · Answer 4 · answered Nov 30 '13 at 01:21

The query against the database is taking too long. There are many reasons as to why this could be happening.

Try running the sql statement generated from the linq directly to the database to see if it takes as long.

Check and see if any of your columns have massive data. (like a string column filled with large volume of data)

Meanwhile try adding this to end of your connection string

Connection Timeout=30000;

score 0 · Accepted Answer · answered Nov 30 '13 at 11:46

0

I have found the issue it was creating time out problem, in my application transaction was maintained in 2 style, one with old ado.net style, and another with EF style, so it created a chaos. I am to make it uniform all to change in entity-framework.

answered Nov 30 '13 at 11:46

NoviceToDotNet

10,387
36
112
166

Why does this LINQ statement throw a timeout error?

5 Answers5