不分组的 LinqToSql 查询求和

Question

(from pd in context.Report
where pd.ReportDate.Month == 2012
&& pd.ReportDate.Year == 11 && pd.UserID == 11014
group pd by  pd.UserID  into g
select new 
{                               
    Cost = g.Sum(pd => pd.Cost),
    RevenueUSD = g.Sum(pd => pd.Revenue),
});

此查询是

 -- Region Parameters
    DECLARE @p0 Int = 2012
    DECLARE @p1 Int = 11
    DECLARE @p2 Int = 11014
    -- EndRegion
    SELECT SUM([t0].[Cost]) AS [Cost], SUM([t0].[Revenue]) AS [RevenueUSD]
    FROM [IntegratedPublisherData] AS [t0]
    WHERE (DATEPART(Month, [t0].[ReportDate]) = @p0) AND (DATEPART(Year, [t0].[ReportDate]) = @p1) AND ([t0].[UserID] = @p2)
    GROUP BY [t0].[UserID]

但我要

 -- Region Parameters
    DECLARE @p0 Int = 2012
    DECLARE @p1 Int = 11
    DECLARE @p2 Int = 11014
    -- EndRegion
    SELECT SUM([t0].[Cost]) AS [Cost], SUM([t0].[Revenue]) AS [RevenueUSD]
    FROM [IntegratedPublisherData] AS [t0]
    WHERE (DATEPART(Month, [t0].[ReportDate]) = @p0) AND (DATEPART(Year, [t0].[ReportDate]) = @p1) AND ([t0].[UserID] = @p2)

此查询将返回 1 行，因为 UserID 被选为一个值，并且按 UserID 分组。

如何在不分组的情况下使用 Sum 编写此 Linq 查询？

Answer 1

如果查询返回了多行，这也将涵盖您的问题。也适用于单行。

var query = 
    (from pd in context.Report
     where pd.ReportDate.Month == 11
     && pd.ReportDate.Year == 2012 
     && pd.UserID == 11014
     select pd).ToList() //use .ToList() to avoid doubled execution
var result = 
    new 
    {
        Cost = query.Sum(pd => pd.Cost), 
        RevenueUSD = query.Sum(pd => pd.Revenue) 
    };

Answer 2

删除分组，然后从表中读取值。当有一条记录时，您不需要sum：

from pd in context.Report
where pd.ReportDate.Month == 2012 && pd.ReportDate.Year == 11 && pd.UserID == 11014
select new {                               
   Cost = pd.Cost,
   RevenueUSD = pd.Revenue,
 }