none
The wait operation timed out RRS feed

  • Question

  • I am getting error The wait opertaion timed out but when i get run linq query to Sql it will run in 5 sec.

    And also tried with increasing timeout to 10*60 .

    My Sql query is

    SELECT
        [Project5].[C7] AS [C1],
        [Project5].[C6] AS [C2],
        [Project5].[C5] AS [C3],
        [Project5].[C1] AS [C4],
        [Project5].[C11] AS [C5],
        [Project5].[C2] AS [C6],
        [Project5].[C3] AS [C7],
        [Project5].[C8] AS [C8],
        [Project5].[C4] AS [C9],
        [Project5].[C9] AS [C10],
        [Project5].[C10] AS [C11]
        FROM ( SELECT
            [GroupBy1].[A1] AS [C1],
            [GroupBy1].[A2] AS [C2],
            [GroupBy1].[A3] AS [C3],
            [GroupBy1].[A4] AS [C4],
            [GroupBy1].[K1] AS [C5],
            [GroupBy1].[K2] AS [C6],
            1 AS [C7],
            CASE WHEN ([Project4].[C6] IS NULL) THEN CAST(NULL AS float) ELSE ROUND( CAST( [Project4].[C2] AS float), 2) END AS [C8],
            CASE WHEN ([Project4].[C6] IS NULL) THEN CAST(NULL AS float) ELSE ROUND( CAST( [Project4].[C3] AS float), 2) END AS [C9],
            CASE WHEN ([Project4].[C6] IS NULL) THEN CAST(NULL AS float) ELSE ROUND( CAST( [Project4].[C4] AS float), 2) END AS [C10],
            [Project4].[C5] AS [C11]
            FROM   (SELECT
                [Filter1].[K1] AS [K1],
                [Filter1].[K2] AS [K2],
                [Filter1].[K3] AS [K3],
                COUNT([Filter1].[A1]) AS [A1],
                SUM([Filter1].[A2]) AS [A2],
                SUM([Filter1].[A3]) AS [A3],
                SUM([Filter1].[A4]) AS [A4]
                FROM ( SELECT
                    CASE WHEN (1 = 1) THEN [Extent3].[CostCentreDescription] ELSE [Extent4].[DepartmentDescription] END AS [K1],
                    CASE WHEN (1 = 1) THEN [Extent3].[CostCentreGUID] ELSE [Extent5].[DepartmentGUID] END AS [K2],
                    CASE WHEN (1 = 1) THEN [Extent1].[CostCentreID] ELSE [Extent1].[DepartmentID] END AS [K3],
                    1 AS [A1],
                    [Extent1].[ProRata] AS [A2],
                    [Extent1].[MonthLineRental] AS [A3],
                    [Extent1].[TotalCost] AS [A4]
                    FROM     [dbo].[InvoiceCTN] AS [Extent1]
                    LEFT OUTER JOIN [dbo].[Department] AS [Extent2] ON [Extent1].[DepartmentID] = [Extent2].[DepartmentID]
                    INNER JOIN [dbo].[CostCentre] AS [Extent3] ON [Extent1].[CostCentreID] = [Extent3].[CostCentreID]
                    LEFT OUTER JOIN [dbo].[Department] AS [Extent4] ON [Extent1].[DepartmentID] = [Extent4].[DepartmentID]
                    LEFT OUTER JOIN [dbo].[Department] AS [Extent5] ON [Extent1].[DepartmentID] = [Extent5].[DepartmentID]
                    WHERE ([Extent1].[InvoiceDateID] = 16) AND (0 = 0 OR 0 =  CAST( [Extent2].[DepartmentType] AS int))
                )  AS [Filter1]
                GROUP BY [K1], [K2], [K3] ) AS [GroupBy1]
            LEFT OUTER JOIN  (SELECT
                [Project3].[C1] AS [C1],
                [Project3].[C2] AS [C2],
                [Project3].[C3] AS [C3],
                [Project3].[C4] AS [C4],
                [Project3].[C5] AS [C5],
                1 AS [C6]
                FROM ( SELECT
                    [Project2].[C1] AS [C1],
                    [Project2].[C2] AS [C2],
                    [Project2].[C3] AS [C3],
                    [Project2].[C4] AS [C4],
                    (SELECT
                        COUNT(1) AS [A1]
                        FROM ( SELECT [Extent11].[InvoiceDateID] AS [InvoiceDateID1], [Extent12].[DepartmentType] AS [DepartmentType1], [Extent14].[CostCentreGUID] AS [CostCentreGUID], [Extent15].[DepartmentGUID] AS [DepartmentGUID1]
                            FROM     [dbo].[InvoiceCTN] AS [Extent11]
                            LEFT OUTER JOIN [dbo].[Department] AS [Extent12] ON [Extent11].[DepartmentID] = [Extent12].[DepartmentID]
                            INNER JOIN [dbo].[InvoiceCTNItemised] AS [Extent13] ON (([Extent11].[MobileNumber] = [Extent13].[MobileNumber]) OR (([Extent11].[MobileNumber] IS NULL) AND ([Extent13].[MobileNumber] IS NULL))) AND (([Extent11].[InvoiceDateID] = [Extent13].[InvoiceDateID]) OR (([Extent11].[InvoiceDateID] IS NULL) AND ([Extent13].[InvoiceDateID] IS NULL)))
                            INNER JOIN [dbo].[CostCentre] AS [Extent14] ON [Extent11].[CostCentreID] = [Extent14].[CostCentreID]
                            LEFT OUTER JOIN [dbo].[Department] AS [Extent15] ON [Extent11].[DepartmentID] = [Extent15].[DepartmentID]
                            WHERE 'SMS' = [Extent13].[CallType]
                        )  AS [Filter3]
                        WHERE ([Filter3].[InvoiceDateID1] = 16) AND (0 = 0 OR 0 =  CAST( [Filter3].[DepartmentType1] AS int)) AND (([Project2].[C1] = (CASE WHEN (1 = 1) THEN [Filter3].[CostCentreGUID] ELSE [Filter3].[DepartmentGUID1] END)) OR (([Project2].[C1] IS NULL) AND (CASE WHEN (1 = 1) THEN [Filter3].[CostCentreGUID] ELSE [Filter3].[DepartmentGUID1] END IS NULL)))) AS [C5]
                    FROM ( SELECT
                        [GroupBy2].[K1] AS [C1],
                        [GroupBy2].[A1] AS [C2],
                        [GroupBy2].[A2] AS [C3],
                        [GroupBy2].[A3] AS [C4]
                        FROM ( SELECT
                            [Project1].[K1] AS [K1],
                            SUM([Project1].[A1]) AS [A1],
                            SUM([Project1].[A2]) AS [A2],
                            SUM([Project1].[A3]) AS [A3]
                            FROM ( SELECT
                                CASE WHEN (1 = 1) THEN [Project1].[CostCentreGUID] ELSE [Project1].[DepartmentGUID] END AS [K1],
                                CASE WHEN ([Project1].[UsageCharge] IS NOT NULL) THEN [Project1].[UsageCharge] ELSE cast(0 as decimal(18)) END AS [A1],
                                (CASE WHEN ([Project1].[DataVolume] IS NOT NULL) THEN [Project1].[DataVolume] ELSE cast(0 as decimal(18)) END) / cast(1048576 as decimal(18)) AS [A2],
                                CASE WHEN ([Project1].[Minutes] IS NOT NULL) THEN [Project1].[Minutes] ELSE cast(0 as decimal(18)) END AS [A3]
                                FROM ( SELECT
                                    [Extent8].[Minutes] AS [Minutes],
                                    [Extent8].[DataVolume] AS [DataVolume],
                                    [Extent8].[UsageCharge] AS [UsageCharge],
                                    [Extent9].[CostCentreGUID] AS [CostCentreGUID],
                                    [Extent10].[DepartmentGUID] AS [DepartmentGUID]
                                    FROM     [dbo].[InvoiceCTN] AS [Extent6]
                                    LEFT OUTER JOIN [dbo].[Department] AS [Extent7] ON [Extent6].[DepartmentID] = [Extent7].[DepartmentID]
                                    LEFT OUTER JOIN [dbo].[InvoiceCTNItemised] AS [Extent8] ON (([Extent6].[MobileNumber] = [Extent8].[MobileNumber]) OR (([Extent6].[MobileNumber] IS NULL) AND ([Extent8].[MobileNumber] IS NULL))) AND (([Extent6].[InvoiceDateID] = [Extent8].[InvoiceDateID]) OR (([Extent6].[InvoiceDateID] IS NULL) AND ([Extent8].[InvoiceDateID] IS NULL)))
                                    INNER JOIN [dbo].[CostCentre] AS [Extent9] ON [Extent6].[CostCentreID] = [Extent9].[CostCentreID]
                                    LEFT OUTER JOIN [dbo].[Department] AS [Extent10] ON [Extent6].[DepartmentID] = [Extent10].[DepartmentID]
                                    WHERE ([Extent6].[InvoiceDateID] = 16) AND (0 = 0 OR 0 =  CAST( [Extent7].[DepartmentType] AS int))
                                )  AS [Project1]
                            )  AS [Project1]
                            GROUP BY [K1]
                        )  AS [GroupBy2]
                    )  AS [Project2]
                )  AS [Project3] ) AS [Project4] ON ([GroupBy1].[K2] = [Project4].[C1]) OR (([GroupBy1].[K2] IS NULL) AND ([Project4].[C1] IS NULL))
        )  AS [Project5]
        ORDER BY [Project5].[C4] DESC

    and the C# code is

     public DataTable GetCTNInvoiceLoadGroupByLinkSource(string LinkType, int DepartmentTypeID, int InvoiceDateID, int UserID, bool showAll = false)
            {
                
                bool IsLinkTypeCostCentre = LinkType == "CostCentre" ? true : false;
                
                var q1 = (from inv in db.InvoiceCTNs.AsNoTracking()

                              where inv.InvoiceDateID == InvoiceDateID && (DepartmentTypeID == 0 || inv.Department.DepartmentType == DepartmentTypeID)
                              select inv);

                if (!showAll)
                {
                    q1 = (from inv in q1
                          join ulr in db.UserLinkRels.AsNoTracking() on new { ID = (isCostCentre ? inv.CostCentreID : inv.DepartmentID) } equals new { ID = ulr.LinkID }
                             where ulr.UserID == UserID
                             select inv);
                }
              
                var q2 = (from inv in q1
                          join invCTNItem in db.InvoiceCTNItemiseds.AsNoTracking() on new { inv.MobileNumber, inv.InvoiceDateID } equals new { invCTNItem.MobileNumber, invCTNItem.InvoiceDateID } into g_join
                          from invCTNItem in g_join.DefaultIfEmpty()
                          select new{inv,invCTNItem});

                #region GroupBy
                var InvoiceCTN_GroupBy = (from inv in q1
                                          group inv by new { Description = (IsLinkTypeCostCentre ? inv.CostCentre.CostCentreDescription : inv.Department.DepartmentDescription), GUID = (IsLinkTypeCostCentre ? inv.CostCentre.CostCentreGUID : inv.Department.DepartmentGUID), ID = (IsLinkTypeCostCentre ? inv.CostCentreID : inv.DepartmentID) } into g
                                          select new
                                          {
                                              GUID = g.Key.GUID,
                                              Description = g.Key.Description,
                                              Count = g.Count(),

                                              ProRata = g.Sum(inv => inv.ProRata),
                                              AgreedLineRental = g.Sum(inv => inv.MonthLineRental),

                                              TotalCost = g.Sum(inv => inv.TotalCost)
                                          });
                var InvoiceCTNItemised_GroupBy = (from g in q2
                                                  group g.invCTNItem by new { Guid = (IsLinkTypeCostCentre ? g.inv.CostCentre.CostCentreGUID : g.inv.Department.DepartmentGUID) } into g
                                                  select new
                                                  {
                                                      GUID = g.Key.Guid,
                                                      EligibleSMS = g.Count(i => i.CallType == "SMS"),
                                                      TotalUsageCost = Math.Round((double)(g.Sum(i => (i.UsageCharge != null ? i.UsageCharge : default(decimal)))), 2),
                                                      EligibleData = Math.Round((double)(g.Sum(i => (i.DataVolume != null ? i.DataVolume : default(decimal)) / 1048576)), 2),
                                                      EligibleMinutes = Math.Round((double)(g.Sum(i => (i.Minutes != null ? i.Minutes : default(decimal)))), 2),

                                                  });
                #endregion

                //#region GroupBy
                //var InvoiceCTN_GroupBy = (from inv in q1
                //                          group inv by new { inv.CostCentre.CostCentreDescription ,inv.CostCentre.CostCentreGUID ,inv.CostCentreID } into g
                //                          select new
                //                          {
                //                              GUID = g.Key.CostCentreGUID,
                //                              Description = g.Key.CostCentreDescription,
                //                              Count = g.Count(),

                //                              ProRata = g.Sum(inv => inv.ProRata),
                //                              AgreedLineRental = g.Sum(inv => inv.MonthLineRental),

                //                              TotalCost = g.Sum(inv => inv.TotalCost)
                //                          });

                //var InvoiceCTNItemised_GroupBy = (from g in q2
                //                                  group g.invCTNItem by new { g.inv.CostCentre.CostCentreGUID } into g
                //                                  select new
                //                                  {
                //                                      GUID = g.Key.CostCentreGUID,
                //                                      EligibleSMS = g.Count(i => i.CallType == "SMS"),
                //                                      TotalUsageCost = Math.Round((double)(g.Sum(i => (i.UsageCharge != null ? i.UsageCharge : default(decimal)))), 2),
                //                                      EligibleData = Math.Round((double)(g.Sum(i => (i.DataVolume != null ? i.DataVolume : default(decimal)) / 1048576)), 2),
                //                                      EligibleMinutes = Math.Round((double)(g.Sum(i => (i.Minutes != null ? i.Minutes : default(decimal)))), 2),

                //                                  });
                //#endregion
                //if (!IsLinkTypeCostCentre)
                //{
                    
                //    InvoiceCTN_GroupBy = (from inv in q1
                //                              group inv by new {  inv.Department.DepartmentDescription,inv.Department.DepartmentGUID,  inv.DepartmentID } into g
                //                              select new
                //                              {
                //                                  GUID = g.Key.DepartmentGUID,
                //                                  Description = g.Key.DepartmentDescription,
                //                                  Count = g.Count(),
                //                                  ProRata = g.Sum(inv => inv.ProRata),
                //                                  AgreedLineRental = g.Sum(inv => inv.MonthLineRental),
                //                                    TotalCost = g.Sum(inv => inv.TotalCost)
                //                              });
                //    InvoiceCTNItemised_GroupBy = (from g in q2
                //                                      group g.invCTNItem by new {  g.inv.Department.DepartmentGUID} into g
                //                                      select new
                //                                      {
                //                                          GUID = g.Key.DepartmentGUID,
                //                                          EligibleSMS = g.Count(i => i.CallType == "SMS"),
                //                                          TotalUsageCost = Math.Round((double)(g.Sum(i => (i.UsageCharge != null ? i.UsageCharge : default(decimal)))), 2),
                //                                          EligibleData = Math.Round((double)(g.Sum(i => (i.DataVolume != null ? i.DataVolume : default(decimal)) / 1048576)), 2),
                //                                          EligibleMinutes = Math.Round((double)(g.Sum(i => (i.Minutes != null ? i.Minutes : default(decimal)))), 2),

                //                                      });
                    
                //}
              
                var q_join = (from q_1 in InvoiceCTN_GroupBy
                              join q_2 in InvoiceCTNItemised_GroupBy
                              on q_1.GUID equals q_2.GUID into g
                              from q_2 in g.DefaultIfEmpty()
                              select new
                              {
                                  GUID = q_1.GUID,
                                  Description = q_1.Description,
                                  Count = q_1.Count,
                                  q_2.EligibleSMS,
                                  q_1.ProRata,
                                  q_1.AgreedLineRental,
                                  q_2.TotalUsageCost,
                                  q_1.TotalCost,
                                  q_2.EligibleData,
                                  q_2.EligibleMinutes
                              }).OrderByDescending(g => g.TotalCost);
              
                    return q_join.ToList().ToDataTable();
                
            }

    Friday, April 24, 2015 9:53 AM

Answers