Linq GroupBy查询中空组的默认值(Default values for empty groups in Linq GroupBy query)

Linq GroupBy查询中空组的默认值(Default values for empty groups in Linq GroupBy query)

我有一组我想汇总的值的数据集。 对于每个组，我想创建一个足够大的数组来包含最大组的值。 当一个组包含小于这个最大数时，我想为空的键值插入一个默认值零。

数据集

Col1 Col2 Value -------------------- A X 10 A Z 15 B X 9 B Y 12 B Z 6

期望的结果

X, [10, 9] Y, [0, 12] Z, [15, 6]

请注意，数据集中Col1中的值“A”在Col2中没有“Y”值。 值“A”是外系列中的第一组，因此它是第一个缺失的元素。

以下查询创建结果数据集，但不插入Y组的默认零值。

result = data.GroupBy(item => item.Col2) .Select(group => new { name = group.Key, data = group.Select(item => item.Value) .ToArray() })

实际结果

X, [10, 9] Y, [12] Z, [15, 6]

我需要做什么来插入零作为缺少的组值？

I have a data set of values that I want to summarise in groups. For each group, I want to create an array big enough to contain the values of the largest group. When a group contains less than this maximum number, I want to insert a default value of zero for the empty key values.

Dataset

Col1 Col2 Value -------------------- A X 10 A Z 15 B X 9 B Y 12 B Z 6

Desired result

X, [10, 9] Y, [0, 12] Z, [15, 6]

Note that value "A" in Col1 in the dataset has no value for "Y" in Col2. Value "A" is first group in the outer series, therefore it is the first element that is missing.

The following query creates the result dataset, but does not insert the default zero values for the Y group.

result = data.GroupBy(item => item.Col2) .Select(group => new { name = group.Key, data = group.Select(item => item.Value) .ToArray() })

Actual result

X, [10, 9] Y, [12] Z, [15, 6]

What do I need to do to insert a zero as the missing group value?

最满意答案

这是我的理解。

假设我们有这个

class Data { public string Col1, Col2; public decimal Value; } Data[] source = { new Data { Col1="A", Col2 = "X", Value = 10 }, new Data { Col1="A", Col2 = "Z", Value = 15 }, new Data { Col1="B", Col2 = "X", Value = 9 }, new Data { Col1="B", Col2 = "Y", Value = 12 }, new Data { Col1="B", Col2 = "Z", Value = 6 }, };

首先，我们需要确定“固定”部分

var columns = source.Select(e => e.Col1).Distinct().OrderBy(c => c).ToList();

然后，我们可以使用正常的分组进行处理，但在组内部，我们将用组元素将这些columns 连接起来 ，这样可以使我们实现所需的行为

var result = source.GroupBy(e => e.Col2, (key, elements) => new { Key = key, Elements = (from c in columns join e in elements on c equals e.Col1 into g from e in g.DefaultIfEmpty() select e != null ? e.Value : 0).ToList() }) .OrderBy(e => e.Key) .ToList();

Here is how I understand it.

Let say we have this

class Data { public string Col1, Col2; public decimal Value; } Data[] source = { new Data { Col1="A", Col2 = "X", Value = 10 }, new Data { Col1="A", Col2 = "Z", Value = 15 }, new Data { Col1="B", Col2 = "X", Value = 9 }, new Data { Col1="B", Col2 = "Y", Value = 12 }, new Data { Col1="B", Col2 = "Z", Value = 6 }, };

First we need to determine the "fixed" part

var columns = source.Select(e => e.Col1).Distinct().OrderBy(c => c).ToList();

Then we can process with the normal grouping, but inside the group we will left join the columns with group elements which will allow us to achieve the desired behavior

var result = source.GroupBy(e => e.Col2, (key, elements) => new { Key = key, Elements = (from c in columns join e in elements on c equals e.Col1 into g from e in g.DefaultIfEmpty() select e != null ? e.Value : 0).ToList() }) .OrderBy(e => e.Key) .ToList();