等待操作超时 [英] The wait operation timed out
问题描述
我收到错误等待操作超时但是当我运行linq查询到Sql它将在5秒内运行。
并且还尝试增加超时到10 * 60。
我的Sql查询是
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（选择
[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）THAST 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）那么[Extent3]。[CostCentreDescription] ELSE [Ext ent4]。[部门描述]结束为[K1]，
情况何时（1 = 1）然后[Extent3]。[CostCentreGUID] ELSE [Extent5]。[DepartmentGUID] END AS [K2]，
CASE WHEN（1 = 1）那么[Extent1]。[CostCentreID] ELSE [Extent1]。[DepartmentID] END AS [K3]，
1 AS [A1]，
[Extent1]。[ProRata] AS [A2]，
[Extent1]。[MonthLineRental] AS [A3]，
[Extent1 ]。[TotalCost] AS [A4]
来自[dbo]。[InvoiceCTN] AS [Extent1]
LEFT OUTER JOIN [dbo]。[部门] AS [Extent2] ] ON [Extent1]。[DepartmentID] = [Extent2]。[DepartmentID]
INNER JOIN [dbo]。[CostCentre] AS [Extent3] ON [Extent1]。[CostCentreID] = [Extent3] 。[CostCentreID]
LEFT OUTER JOIN [dbo]。[部门] AS [Extent4] ON [Extent1]。[DepartmentID] = [Extent4]。[DepartmentID]
LEFT OUTER JOIN [dbo]。 [部门] 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（选择
[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（SELEC T [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]。[部门] 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]。[部门] 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）然后[Filter3]。[CostCentreGUID] ELSE [Filter3]。[DepartmentGUID1] END为空））））AS [C5]
FROM（SELECT
[ GroupBy2]。[K1] AS [C1]，
[GroupBy2]。[A1] AS [C2]，
[GroupBy2]。[A2] AS [C3] ，
[GroupBy2]。[A3] AS [C4]
FROM（选择
[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）那么[Project1]。[CostCentreGUID] ELSE [Project1]。[DepartmentGUID] END AS [K1] ，
CASE WHEN（[Project1]。[UsageCharge] IS NOT NULL）那么[Project1]。[UsageCharge] ELSE强制转换（0为十进制（18））END AS [A1]，
（CASE WHEN（[Project1]。[DataVolume] IS NOT NULL）然后[Project1]。[DataVolume] ELSE强制转换（0为十进制（18））END）/强制转换（1048576为十进制（18）） AS [A2]，
CASE WHEN（[Project1]。[Minutes] IS NOT NULL）那么[Project1]。[Minutes] ELSE cast（0为十进制（18））END AS [A3]
FROM（选择
[Extent8]。[分钟] AS [分钟]，
[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]。[部门] 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]）或（（[Extent6]。[InvoiceDateID] IS NULL）AND（[Extent8]。[InvoiceDateID] IS NULL）））
INNER JOIN [dbo]。[CostCentre] AS [范围9] ON [Extent6]。[CostCentreID] = [Extent9]。[CostCentreID]
LEFT OUTER JOIN [dbo]。[部门] 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
和C＃代码是
public DataTable GetCTNInvoiceLoadGroupByLinkSource（string LinkType，int DepartmentTypeID，int InvoiceD ateID，int UserID，bool showAll = false）
{
bool IsLinkTypeCostCentre = LinkType ==CostCentre？ true：false;
var q1 =（来自db.InvoiceCTNs.AsNoTracking（）中的inv
其中inv.InvoiceDateID == InvoiceDateID &&（DepartmentTypeID == 0 || inv.Department.DepartmentType == DepartmentTypeID）
select inv）;
if（！showAll）
{
q1 =（来自q1中的inv
在db.UserLinkRels.AsNoTracking（）中加入ulr new {ID =（isCostCentre？inv.CostCentreID：inv.DepartmentID）}等于新的{ID = ulr.LinkID}
其中ulr.UserID == UserID
select inv ）;
}
var q2 =（来自q1中的inv
加入db.InvoiceCTNItemiseds中的invCTNItem。新{inv.MobileNumber，inv.InvoiceDateID}上的AsNoTracking（）等于new {invCTNItem.M obileNumber，invCTNItem.InvoiceDateID}到g_join
来自g_join.DefaultIfEmpty（）中的invCTNItem
选择新的{inv，invCTNItem}）;
< br $>
#region GroupBy
var InvoiceCTN_GroupBy =（来自q1中的inv
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）}进入g
选择新的
{
GUID = g.Key.GUID，
描述= 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 InvoiceC TNItemised_GroupBy =（来自g in q2
group g.invCTNItem by new {Guid =（IsLinkTypeCostCentre？ g.inv.CostCentre.CostCentreGUID：g.inv.Department.DepartmentGUID）}进入g
选择新的
{
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 =（来自q1中的inv
//将新的{inv.CostCentre.CostCentreDescription，inv.CostCentre.CostCentreGUID，inv.CostCentreID}分组到g
//选择新的
// {
// GUID = g.Key.CostCentreGUID，
//说明= 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
//将新的{g.inv.CostCentre.CostCentreGUID}组g.invCTNItem分组到g
//选择新的
// {
// GUID = g.Key.CostCentreGUID，
// EligibleSMS = g.Count（i => i.CallType ==SMS），< br $>
// TotalUsageCost = Math.Round（（double）（g.Sum（i =>（i.UsageCharge！= null？i.UsageCharge：defa ult（十进制）））），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：默认（十进制）））），2），
//}）;
// #endregion
// if（！IsLinkTypeCostCentre）
// {
// InvoiceCTN_GroupBy =（来自q1中的inv
//新的{inv.Department.DepartmentDescription，inv.Department.DepartmentGUID，inv.DepartmentID}组inv到g
//选择新的
// {
// GUID = g.Key.DepartmentGUID，
//说明= g.Key。 Departmen tDescription，
// Count = g.Count（），
// ProRata = g.Sum（inv => inv.ProRata），
// AgreedLineRental = g.Sum（inv => inv.MonthLineRental），
// TotalCost = g.Sum（inv => inv.TotalCost）
//}）;
// InvoiceCTNItemised_GroupBy =（来自g in q2
// group g.invCTNItem by new {g.inv.Department.DepartmentGUID}进入g
//选择新的
// {
// GUID = g.Key。 DepartmentGUID，
// EligibleSMS = g.Count（i => i.CallType ==SMS），
// TotalUsageCost = Math.Round（（double ）（g.Sum（i =>（i.UsageCharge！= null？i.Usag eCharge：默认（十进制）））），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：默认（十进制）））），2），
//}）;
//}
var q_join =（来自InvoiceCTN_GroupBy的q_1
加入q_2在InvoiceCTNItemised_GroupBy
on q_1.GUID等于q_2.GUID到g
来自q_2 in g.DefaultIfEmpty（）
选择新的
{
GUID = q_1.GUID，
描述= 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）;
返回q_join.ToList（）。ToDataTable（）;
}
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();
}
推荐答案
请始终对这种查询使用存储过程。此查询需要更多的编译时间。存储过程是预编译代码...
Please always use Stored Procedure for that kind of queries .. This query is taking more compile time. and Stored procedure is pre compile code...
这篇关于等待操作超时的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
