"Execution Timeout" on converting LINQ query results using ToList()

Question

As the title states, I'm getting a "Wait operation timed out" message (inner exception message: "Timeout expired") on a module I'm maintaining. Everytime the app tries to convert the query results using ToList(), it times out regardless of the number of results.

Reason this needs to be converted to list: Results needed to be exported to Excel for download.

Below is the code:

public Tuple<IEnumerable<ProductPriceSearchResultDto>, int> GetProductPriceSearchResults(ProductPriceFilterDto filter, int? pageNo = null)
{
    //// Predicate builder
    var predicate = GetProductPriceSearchFilter(filter);

    //// This runs for approx. 1 minute before throwing a "Wait operation timed out" message...
    var query = this.GetProductPriceSearchQuery()
                    .Where(predicate)
                    .Distinct()
                    .OrderBy(x => x.DosageFormName)
                    .ToList();

    return Tuple.Create<IEnumerable<ProductPriceSearchResultDto>, int>(query, 0);
}

My query:

var query = (from price in this.context.ProductPrice.AsExpandable()
             join product in this.context.vwDistributorProducts.AsExpandable() 
                 on price.DosageFormCode equals product.DosageFormCode
             join customer in this.context.vwCustomerBranch.AsExpandable()
                 on price.CustCd equals customer.CustomerCode
             where price.CountryId == CurrentUserService.Identity.CountryId && !product.IsInactive
             select new { price.PriceKey, price.EffectivityDateFrom, price.ContractPrice, price.ListPrice,
                          product.DosageFormName, product.MpgCode, product.DosageFormCode,
                          customer.CustomerName }).GroupBy(x => x.DosageFormCode)
             .Select(x => x.OrderByDescending(y => y.EffectivityDateFrom).FirstOrDefault())
             .Select(
                 x =>
                 new ProductPriceSearchResultDto
                 {
                     PriceKey = x.PriceKey,
                     DosageFormCode = x.DosageFormCode,
                     DosageFormName = x.DosageFormName,
                     EffectiveFrom = x.EffectivityDateFrom,
                     Price = x.ListPrice,
                     MpgCode = x.MpgCode,
                     ContractPrice = x.ContractPrice,
                     CustomerName = x.CustomerName
                 }); 

return query;

Notes:

ProductPrice is a table and has a non-clustered index pointing at columns CountryId and DosageFormCode.
vwDistributorProducts and vwCustomerBranch are views copied from the client's database.

I'm already at my wit's end. How do I get rid of this error? Is there something in the code that I need to change?

Edit: As much as possible, I don't want to resort to setting a command timeout because 1.) app's doing okay without it by far...except for this function and 2.) this is already a huge application and I don't want to possibly put the other modules' performances at risk.

Any help would be greatly appreciated. Thank you.

Possible duplicate of [Set Command Timeout in EF 6](https://stackoverflow.com/questions/36977974/set-command-timeout-in-ef-6) — mjwills, Jun 20 '18 at 10:01
@DeeJ. are you confusing LINQ to Objects with LINQ to SQL? L2S is a different *ORM*. As for the error itself, it means the *query* timed out, not the conversion of the results. Most likely you are loading an entire table in memory in one of those joins or calls to `.AsExpandable()` (what's that?). — Panagiotis Kanavos, Jun 20 '18 at 10:22
@DeeJ. you *shouldn't* need any joins with EF or any ORM. You should specify appropriate relations and navigation properties so that you *don't* have to JOIN and the ORM can generate a correct query that doesn't end up loading the entire database in memory. — Panagiotis Kanavos, Jun 20 '18 at 10:23
Hi @PanagiotisKanavos, thank you for the info. Does this mean I should change how the query is written? — Dee J., Jun 20 '18 at 10:27
@Deej. There's no [AsExpandable()](https://stackoverflow.com/questions/30667858/asexpandable-in-linq-to-entity) function in LINQ. That call almost certainly ends up loading every product, price, branch etc in memory for the in-memory join, hence the timeouts — Panagiotis Kanavos, Jun 20 '18 at 10:27
@DeeJ. you should change your *entities* so that they have proper relations to each other, so you won't need any joins. If this is a reporting query, in which case there are no entities, it's probably better to create an actual view and map to it. — Panagiotis Kanavos, Jun 20 '18 at 10:30
@DeeJ. in any case removing `.AsExpandable()` will allow Entity Framework to construct a SQL query and avoid the timeouts. If it complains, it will be because the query can't be converted to SQL. In this case, fix the query, don't try to cover it up by using LINQ-to-Objects. — Panagiotis Kanavos, Jun 20 '18 at 10:32
`As much as possible, I don't want to resort to setting a command timeout` You have no choice. The query is timing out. You need to either extend the timeout, change the query or change the database so it is faster (e.g add indexes). — mjwills, Jun 20 '18 at 10:49

score 1 · Answer 1 · answered Jun 20 '18 at 10:19

1

I'd try and log the sql this translates into. The actual sql may then be used to get the query plan, which may lead you closer to the root cause.

answered Jun 20 '18 at 10:19

PLaine

135
1
1
10

"Execution Timeout" on converting LINQ query results using ToList()

1 Answers1