CODEWITHSUNDEEP
c#linq
NoviceToDotNet
NoviceToDotNet

Reputation: 10815

Why does this LINQ statement throw a timeout error?

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.

Upvotes: 1

Views: 2686

Answers (5)

NoviceToDotNet
NoviceToDotNet

Reputation: 10815

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.

Upvotes: 0

Reza Shirazian
Reza Shirazian

Reputation: 2353

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;

Upvotes: 1

CodeCaster
CodeCaster

Reputation: 151690

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.

Upvotes: 2

ken2k
ken2k

Reputation: 49013

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
{
    // ...
}

Upvotes: 2

Bas
Bas

Reputation: 27105

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).

Upvotes: 2

Related Questions