问题描述:

Just getting my head around Linq and having lots of fun! Can any one aid me with a query for this:

I have a list of data:

Key Value

Aaa 12

AaA 10

AAa 5

BBB 2

Bbb 1

1. I want to group by Key.ToUpper()

2. For every group I need the Max(Value) & Sum(Value)

3. For every group I want to select the entries

There the Value != Max(value)

the final result should be like this:

Key Max Total

AaA 12 27

AAa 12 27

Bbb 2 3

Thanks!

Update, actually I also need the Key from the Maximum entry:

Key Max Total Correct

AaA 12 27 Aaa

AAa 12 27 Aaa

Bbb 2 3 BBB

网友答案:

:)

var results =
  from kvp in source
  group kvp by kvp.Key.ToUpper() into g
  select new
  {
    Group = g,
    Max = g.Max(kvp => kvp.Value),
    Total = g.Sum(kvp => kvp.Value)
  } into ag
  from x in ag.Group  //SelectMany
  where x.Value != ag.Max
    //for the update to the question - note: possibly ambiguous
  let correct = ag.Group.Where(y => y.Value == ag.Max).First().Key
  select new
  {
    Key = x.Key,
    Max = ag.Max,
    Total = ag.Total,
    Correct = correct 
  };

I kinda like the question because of all the little parts (some are rarely used) that are required to make the answer.


Max = g.Max(kvp => kvp.Value),
Total = g.Sum(kvp => kvp.Value)

Performing multiple aggregations on a group is straightforward, yet challenging if you don't know how.


select a into b

This clause takes everything that happened before and starts a new query with the target. Without it, I'd have to start a new query like this:

var A = ... select a

var B = from b in A

It's important to note that the select into clause removes kvp and g from scope.


  from b in source
  from a in b.A  //SelectMany

This "unpacking" of the child collection turns my query about b's into a query about a's. Unlike the default Enumerable.SelectMany overload, it leaves the parent (b) in scope.


where x.Value != ag.Max

Comparing a child's property with a parent's property? Delightful. It's important to remember to break out where anytime you want to filter, even if you just grouped (there is no HAVING).

相关阅读:
Top