Entity Framework object LINQ query Timeout issues

Question

I've attempted to modify my connection string to include an extended timeout and I've confirmed that on the sql server side the view that feeds my EF Object executes within seconds and returns a total of 3000 or less records.

BUT when I attempt to run it via code I am now running into Timeout issues and I was seeking some advice to fix this issue. I get "Execution Timeout Expired. The timeout period elapsed prior to completion of the operation or the server is not responding." Most solutions I find on the specific error recommend connection string modifications OR something along this.context.CommandTimeout... which I cannot figure out how to use in this situation.

I've included the Method I use to acquire the desired data. If there is a more efficient way please let me know.

The input arguments are:

• int? inputSKU = null
• int? inputStoreNum = null
• DateTime? inputStartDate = null

The intent is to return the full list.

And it hangs at, because it skips all the conditional bits: var qUniqueOffers = query.GroupBy(q => q.Plan_Number).ToList();

Thank you.

---

private List<PromotionItem> QueryPromotion(int? inputSKU, int? inputStoreNum, DateTime? inputStartDate)
{
    log.Info("Client requested QueryPromotion");
    List<PromotionItem> resultQuery = new List<PromotionItem>();

    try
    {
        using (DWH_Entities db = new DWH_Entities())
        {
            var query = db.vw_Web_Promotion.AsQueryable();

            // filter promotion results that don't match SKU#
            if (inputSKU != null)
                query = query.Where(q => q.Sku_Number == inputSKU);
            // filter promotion results that don't match Store Num
            if (inputStoreNum != null)
                query = query.Where(q => q.Store_Number == inputStoreNum);
            // filter promotion results that don't match Promotion Start Date
            if (inputStartDate != null)
                query = query.Where(q => q.Start_Date >= inputStartDate);
            // Group promotions By Plan Number ('Promotion ID')
            var qUniqueOffers = query
                                .GroupBy(q => q.Plan_Number)
                                .ToList();
            // Select first from each group to get unique details
            var qOffers = qUniqueOffers
                        .Select(g => g.OrderBy(gi => gi.Plan_Number).First())
                        .ToList();

            foreach (var qo in qOffers)
            {
                resultQuery.Add(new PromotionItem
                {
                    PromotionNumber = qo.Plan_Number.Trim(),
                    PromotionDescription = qo.Plan_Description.Trim(),
                    StartDate = qo.Start_Date,
                    EndDate = qo.End_Date
                });
            }
        }
    }
    catch (Exception e)
    {
        log.Error("[" + e.TargetSite + "] | " + e.Message);
        throw e;
    }

    return resultQuery;
}

Answer 1

If you are using latest EF version do the following to increase timeout:

using (DWH_Entities db = new DWH_Entities())
{
    db.Database.CommandTimeout = 300;
    ...

If you want records in the minimum time, try following:

var temp = query.ToList();
var qUniqueOffers = temp.GroupBy(q => q.Plan_Number)
                                .ToList();

Answer 2

// Group promotions By Plan Number ('Promotion ID')
var qUniqueOffers = query
                    .GroupBy(q => q.Plan_Number)
                    .ToList();
// Select first from each group to get unique details
var qOffers = qUniqueOffers
            .Select(g => g.OrderBy(gi => gi.Plan_Number).First())
            .ToList();

The way you have written the above LINQ means you are pulling a lot of data over the wire (the first ToList) and then getting a subset of the data (using First and the second ToList). Consider changing it to:

// Group promotions By Plan Number ('Promotion ID')
var qUniqueOffers = query
                    .GroupBy(q => q.Plan_Number)
// Select first from each group to get unique details
var qOffers = qUniqueOffers
            .Select(g => g.OrderBy(gi => gi.Plan_Number).First())
            .ToList();

This should result in much less data being sent from the database - which will hopefully make it faster.

As https://stackoverflow.com/a/13827077/34092 states:

ToList() always forces everything ahead of it to evaluate immediately, as opposed to deferred execution.