LINQ query to select object and count

Question

I'm trying to do a query to generate packing slips, and need general order info, as well as how many items the order contains.

Here's my basic query:

var orders = (from cert in _orderRepository.GetAll()
                           where !cert.PrintedPackSlip
                           orderby cert.CardNumber
                           //group cert by cert.CardNumber
                           //into certGrp
                           select cert);

What I need to do is group by CardNumber and do a count of how many orders have that card number.

I would ideally it to come out like a Dictionary if at all possible

Are we talking about L2EF or L2SQL? If it's EF, see my question about a similar matter: http://stackoverflow.com/questions/10723439/linq-to-entities-get-results-row-count-in-one-query — walther, Jul 05 '12 at 19:06

score 1 · Answer 1 · edited Jul 05 '12 at 19:13

1

May be something like this:

var orders = from cert in _orderRepository.GetAll()
                       where !cert.PrintedPackSlip
                       orderby cert.CardNumber
                       group cert by cert.CardNumber into certGrp
                       select new {CardNumber = certGrp.Key, Count = certGrp.Count());

foreach(var item in orders)
    Console.WriteLine("item.CardNumber: " + item.CardNumber + " item.Count: " + item.Count.ToString());

edited Jul 05 '12 at 19:13

Jeff Mercado

129,526
32
251
272

answered Jul 05 '12 at 19:10

Alex F

3,180
2
28
40

`cert` would be out of scope after the grouping, you'd have to use the key. I've made the correction. – Jeff Mercado Jul 05 '12 at 19:13
This is essentially what I had before, but I would *ideally* it to come out like a Dictionary if at all possible. (will add to question for clarification. – Matt Millican Jul 05 '12 at 19:18
@mmillican for a dictionary, just wrap this LINQ query in parentheses and append `.ToDictionary(x => x.CardNumer, x => x.Count)` to it. – Arithmomaniac Jul 05 '12 at 19:31

Nick Jones · Accepted Answer · 2012-07-05T20:08:32.690

1

If I've correctly understood, for each order you want to list the total number of orders for that card number, in which case the following should work:

        var orders = (from cert in _orderRepository.GetAll()
                      join cert2 in _orderRepository.GetAll()
                      on cert.CardNumber equals cert2.CardNumber
                      where !cert.PrintedPackSlip 
                      where !cert2.PrintedPackSlip
                      group cert by cert
                      into certGrp
                      select certGrp).ToDictionary(o => o.Key,o => o.Count())

edited Jul 05 '12 at 20:08

answered Jul 05 '12 at 19:39

Nick Jones

6,413
2
18
18

It looks like it isn't possible to get the actual Order out of this, but only the key (or CardNumber)? – Matt Millican Jul 05 '12 at 19:42
Try changing it to .ToDictionary(o => o.First(),o => o.Count()) though that assumes that the first order in a group is representative of all the orders (without seeing the rest of the data which makes up an order I can't say if that is true) – Nick Jones Jul 05 '12 at 19:46
That would not be true. There could be multiple Orders that have the same card number, and we want to group those together to get the Count. Sorry if this is confusing. Thanks for the help though! – Matt Millican Jul 05 '12 at 19:50
OK, I think I know understand what you are trying to do - for each order you want to list the number of orders with the same card number - I've modified by answer to do this – Nick Jones Jul 05 '12 at 20:09

LINQ query to select object and count

2 Answers2