I am getting error The wait operation 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();
}
Submit an article or tip