Linq - Group by and count over multiple fields (ou

问题:

I have an app with an inquiry answers (50 fields, with names like upa1, upa2, upd1, upd6, etc, with possible values "y", "n" and "na").

I can get and count one single field with this:

foreach(var y in items.GroupBy(g => g.upa1)
                      .Select(group => new {upa1name = group.Key, upa1count = group.Count()})) {
    <div>These are the totals for upa1:</div>
    <div>@y.upa1name : @y.upa1count</div>
}

And it will output this:

These are the totals for upa1:
y : 30
n : 11
na : 18

I can repeat this for all questions manually, but I need to count the number of "y", "n" and "na" for all questions to form this single output:

These are the totals for all questions:
y : 1342
n : 879
na : 445

I know there are already hundreds of topics about these issues, but the information is far too stretched and over way too many variations on this matter (different tables, joins, etc). Nothin on the "sum count over multiple fields".

Thanks.

Also, on a side note, each line only appears if the answer exists at least once, but I need it to show even if it is a "y : 0". Is this possible?

回答1:

With only three possible answers you can do this in three separate queries:

foreach (var ans in new[] {"y", "n", "na"}) {
    var count = items.Sum(item =>
        (item.upa1==ans?1:0) + (item.upa2==ans?1:0) + (item.upd1==ans?1:0) + ...
    );
    <div>These are the totals for @ans:</div>
    <div>@ans : @count</div>
}

This should run reasonably fast.

回答2:

Lets say you have a class like this:

public class Inquiry
{
    public string UpaName { get; set; }
    public string UpaValue { get; set; }
}

Now lets populate that list with some test data:

var list = new List<Inquiry>()
{
    new Inquiry() { UpaName = "upa1", UpaValue = "y" },
    new Inquiry() { UpaName = "upa1", UpaValue = "y" },
    new Inquiry() { UpaName = "upa1", UpaValue = "n" },
    new Inquiry() { UpaName = "upa1", UpaValue = "na" },
    new Inquiry() { UpaName = "upa2", UpaValue = "y" },
    new Inquiry() { UpaName = "upa2", UpaValue = "n" },
    new Inquiry() { UpaName = "upa2", UpaValue = "na" },
    new Inquiry() { UpaName = "upa2", UpaValue = "na" },
    new Inquiry() { UpaName = "upa1", UpaValue = "y" },
    new Inquiry() { UpaName = "upa1", UpaValue = "y" },
    new Inquiry() { UpaName = "upa2", UpaValue = "n" },
    new Inquiry() { UpaName = "upa1", UpaValue = "y" },
    new Inquiry() { UpaName = "upa1", UpaValue = "y" },
};

We can create another class that would hold our key along with sumarized values. Something like this:

[DebuggerDisplay("{UpaName,nq} y:{y,nq} n:{n,nq} na:{na,nq}")]//include Sysytem.Diagnostics
public class InquirySummary
{
    public string UpaName { get; set; }
    public int y { get; set; }
    public int n { get; set; }
    public int na { get; set; }
}

We could than instantiate a dictionary of key values where key would be your inquiry answer and the value would be an instance of our InquirySummary class.

var summary = new Dictionary<string, InquirySummary>();

We would than define an action that would take INquiry object and InquirySummary object and would summarize the values.

Action<Inquiry, InquirySummary> sumarize = new Action<Inquiry, InquirySummary>((i, sum) =>
{
    if (i.UpaValue == "y")
        sum.y += 1;
    else if (i.UpaValue == "n")
        sum.n += 1;
    else
        sum.na += 1;
});

Finally you would have all the neccessary things to call an aggregate that would run only once through your list.

list.Aggregate(summary, (b, c) => 
{
    if (summary.ContainsKey(c.UpaName)) {
        var sum = summary[c.UpaName];
        sumarize(c, sum);
    }
    else
    {
        var sum = new InquirySummary();
        summary.Add(c.UpaName, sum);
        sumarize(c, sum);
    }
    return summary;
});

This would basically give our dictionary as seed and than we would start looping through the list. First if condition would check if the key already exists in the dicitonary and than would simpy call the sumarize action on that value. Otherwise we would create a new InquirySummary object and would sumarize into him. Each time we are returning the seed which is in our case dictionary.