获取一个 LINQ 查询中两列之和

假设我有一张桌子叫 项(IDint，完成整数，总计整数)

我可以通过两个查询来完成:

int total = m.Items.Sum(p=>p.Total)
int done = m.Items.Sum(p=>p.Done)

但是我想用一个类似这样的查询来完成:

var x = from p in m.Items select new { Sum(p.Total), Sum(p.Done)};

当然有一种方法可以从 LINQ 语法调用聚合函数... ？

83135 次浏览
小开

使用一个助手元组类，可以是你自己的，也可以是.NET 4中的标准元组类，你可以这样做:

var init = Tuple.Create(0, 0);


var res = m.Items.Aggregate(init, (t,v) => Tuple.Create(t.Item1 + v.Total, t.Item2 + v.Done));

res.Item1是 Total列和 Done列的 res.Item2的总和。

小开
最佳答案

这样就行了:

LINQ 查询语法:

from p in m.Items
group p by 1 into g
select new
{
SumTotal = g.Sum(x => x.Total),
SumDone = g.Sum(x => x.Done)
};

LINQ 方法语法:

m.Items
.GroupBy(r => 1)
.Select(g => new
{
SumTotal = g.Sum(x => x.Total),
SumDone = g.Sum(x => x.Done)
});

注意，这个解决方案(两种语法)返回一个包含1个项的列表，因此您可能希望在最后添加:

 .FirstOrDefault();
小开

怎么样

   m.Items.Select(item => new { Total = item.Total, Done = item.Done })
.Aggregate((t1, t2) => new { Total = t1.Total + t2.Total, Done = t1.Done + t2.Done });
小开

对表格进行求和，按常数分组:

from p in m.Items
group p by 1 into g
select new {
SumTotal = g.Sum(x => x.Total),
SumDone = g.Sum(x => x.Done)
}
小开

在我的代码的其余部分中，找出从哪里提取总和或其他聚合使我困惑，直到我记起我构造的变量是一个 Iqueryable。假设我们的数据库中有一个由 Orders 组成的表，我们希望为 ABC 公司生成一个摘要:

var myResult = from g in dbcontext.Ordertable
group p by (p.CUSTNAME == "ABC") into q  // i.e., all of ABC company at once
select new
{
tempPrice = q.Sum( x => (x.PRICE ?? 0m) ),  // (?? makes sure we don't get back a nullable)
tempQty = q.Sum( x => (x.QTY ?? 0m) )
};

现在有趣的部分-temPrice 和 temQty 没有在任何地方声明但它们一定是 myResult 的一部分，不是吗？访问它们的方式如下:

Console.Writeline(string.Format("You ordered {0} for a total price of {1:C}",
myResult.Single().tempQty,
myResult.Single().tempPrice ));

还可以使用许多其他 Queryable 方法。

小开 
//Calculate the total in list field values
//Use the header file:


Using System.Linq;
int i = Total.Sum(G => G.First);


//By using LINQ to calculate the total in a list field,


var T = (from t in Total group t by Total into g select g.Sum(t => t.First)).ToList();


//Here Total is a List and First is the one of the integer field in list(Total)
小开

使用 分组Linq 时，会创建一个新的项集合，这样就有了两个项集合。

以下是解决这两个问题的办法:

  1. 在一次迭代中总计任意数量的成员
  2. 避免重复你的收藏品

密码:

public static class LinqExtensions
{
/// <summary>
/// Computes the sum of the sequence of System.Double values that are obtained
/// by invoking one or more transform functions on each element of the input sequence.
/// </summary>
/// <param name="source">A sequence of values that are used to calculate a sum.</param>
/// <param name="selectors">The transform functions to apply to each element.</param>
public static double[] SumMany<TSource>(this IEnumerable<TSource> source, params Func<TSource, double>[] selectors)
{
if (selectors.Length == 0)
{
return null;
}
else
{
double[] result = new double[selectors.Length];


foreach (var item in source)
{
for (int i = 0; i < selectors.Length; i++)
{
result[i] += selectors[i](item);
}
}


return result;
}
}


/// <summary>
/// Computes the sum of the sequence of System.Decimal values that are obtained
/// by invoking one or more transform functions on each element of the input sequence.
/// </summary>
/// <param name="source">A sequence of values that are used to calculate a sum.</param>
/// <param name="selectors">The transform functions to apply to each element.</param>
public static double?[] SumMany<TSource>(this IEnumerable<TSource> source, params Func<TSource, double?>[] selectors)
{
if (selectors.Length == 0)
{
return null;
}
else
{
double?[] result = new double?[selectors.Length];


for (int i = 0; i < selectors.Length; i++)
{
result[i] = 0;
}


foreach (var item in source)
{
for (int i = 0; i < selectors.Length; i++)
{
double? value = selectors[i](item);


if (value != null)
{
result[i] += value;
}
}
}


return result;
}
}
}

总结的方法是这样的:

double[] result = m.Items.SumMany(p => p.Total, q => q.Done);

下面是一个普遍的例子:

struct MyStruct
{
public double x;
public double y;
}


MyStruct[] ms = new MyStruct[2];


ms[0] = new MyStruct() { x = 3, y = 5 };
ms[1] = new MyStruct() { x = 4, y = 6 };


// sum both x and y members in one iteration without duplicating the array "ms" by GROUPing it
double[] result = ms.SumMany(a => a.x, b => b.y);

如你所见

result[0] = 7
result[1] = 11
小开

这个问题已经得到了回答，但是其他的答案仍然会在集合上进行多次迭代(多次调用 Sum)或者创建大量的中间对象/元组(Tuple) ，这可能没问题，但是如果不行，那么你可以创建一个(或多个)扩展方法，这种方法用老式的方法完成，但是很适合 LINQ 表达式。

这样的扩展方法应该是这样的:

public static Tuple<int, int> Sum<T>(this IEnumerable<T> collection, Func<T, int> selector1, Func<T, int> selector2)
{
int a = 0;
int b = 0;


foreach(var i in collection)
{
a += selector1(i);
b += selector2(i);
}


return Tuple.Create(a, b);
}

你可以这样使用它:

public class Stuff
{
public int X;
public int Y;
}


//...


var stuffs = new List<Stuff>()
{
new Stuff { X = 1, Y = 10 },
new Stuff { X = 1, Y = 10 }
};


var sums = stuffs.Sum(s => s.X, s => s.Y);
小开

使用 C # 7.0中引入的对元组的语言支持，您可以使用以下 LINQ 表达式解决这个问题:

var itemSums = m.Items.Aggregate((Total: 0, Done: 0), (sums, item) => (sums.Total + item.Total, sums.Done + item.Done));

完整代码示例:

var m = new
{
Items = new[]
{
new { Total = 10, Done = 1 },
new { Total = 10, Done = 1 },
new { Total = 10, Done = 1 },
new { Total = 10, Done = 1 },
new { Total = 10, Done = 1 },
},
};


var itemSums = m.Items.Aggregate((Total: 0, Done: 0), (sums, item) => (sums.Total + item.Total, sums.Done + item.Done));


Console.WriteLine($"Sum of Total: {itemSums.Total}, Sum of Done: {itemSums.Done}");

提交答案