我收到错误等待操作超时但是当我运行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}）; #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）， // 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...
更多推荐
等待操作超时
发布评论