22

I have a table NCR containing data of the format:

ID | Date     | Item   | Type | Qty
1  | 01/01/13 | Apple  | A    | 1 
2  | 01/01/13 | Apple  | B    | 1 
3  | 01/01/13 | Orange | C    | 1 
4  | 01/01/13 | Orange | A    | 2 
6  | 01/01/13 | Orange | C    | 1

I would like to produce a linq query that gives me a summary of the types and sums for a given date like so:

Item   | A | B | C
Apple  | 1 | 1 | 0 
Orange | 2 | 0 | 2

So far I have this:

var q = data.GroupBy(l => l.Item)
             .Select(g => new {
                                    Item = g.Key,
                                    Total = g.Sum(c => c.Qty),
                                    A = g.Sum(c => c.Type == "A"),
                                    B = g.Sum(c => c.Type == "B"),
                                    C = g.Sum(c => c.Type == "C")
});

However I can't seem to give a criteria to the g.Sum lambda statement. If I use Count (which is the wrong data) I can give the critera, but why is Sum missing this? What is my alternative to creating a summary table of the data available?

Soner Gönül
  • 97,193
  • 102
  • 206
  • 364
Gordon Copestake
  • 1,616
  • 4
  • 21
  • 37

1 Answers1

39

The delegate provided to Sum isn't a predicate; it's a selector.

Are you trying to sum the Qty property? If so, I suspect you want:

A = g.Where(c => c.Type == "A").Sum(c => c.Qty),
B = g.Where(c => c.Type == "B").Sum(c => c.Qty),
C = g.Where(c => c.Type == "C").Sum(c => c.Qty)

(Or you could group by type as well, of course.)

Jon Skeet
  • 1,421,763
  • 867
  • 9,128
  • 9,194
  • How do I handle nulls with this method? If for example there are no B's I get an "materialized value is null" error – Gordon Copestake Jan 28 '13 at 11:56
  • @GordonCopestake: You shouldn't - `Sum` should return 0. Are you sure you're not getting a value with a `Qty` property of 0? – Jon Skeet Jan 28 '13 at 11:57
  • 2
    @GordonCopestake if qty is nullable field than you can might do like this Sum(c => c.Qty?0) or Sum(c => (c.Qty == null ? 0 : c.Qty)) – Pranay Rana Jan 28 '13 at 11:57