Linq Group by id

Question

var query = from c in context.Auto
            join r in context.Label on c.idAutoChar equals r.idAutoChar
            join g in context.Rent on c.idAuto equals g.idAuto
            where c.idAutoChar == r.idAutoChar && c.idAuto == g.idAuto
            group new {c, r, g} by new {c.idAuto, r.Name, c.RegNumber, g.Summ} into v
            select new
            {
                v.Key.idAuto,
                v.Key.Name,
                v.Key.RegNumber,
                Sum = (from b in v select v.Key.Summ).Sum()
            };

I need to group Cars by idAuto with a column of SUM(Summ) but it didn't work correctly. Could you please help.

Andrew · Answer 1 · 2014-04-24T10:19:56.897

1

var query = from c in context.Auto
                 join r in context.Label on c.idAutoChar equals r.idAutoChar
                 join g in context.Rent on c.idAuto equals g.idAuto
                 //where c.idAutoChar == r.idAutoChar && c.idAuto == g.idAuto //???
                 group new {c, r, g} by new {c.idAuto, r.Name, c.RegNumber} into v
                         select new
                         {
                             v.Key.idAuto,
                             v.Key.Name,
                             v.Key.RegNumber,
                             Sum = v.Sum(item => item.Key.Summ)
                         };

use Sum = v.Sum(item => item.Key.Summ) and don't group by Summ, otherwise it will produce a group item for each different Summ value.

BTW, why do you need your where condition - it replicates on conditions of your joins

edited Apr 24 '14 at 10:19

answered Apr 24 '14 at 10:05

Andrew

3,648
1
15
29

yeah, pardon me, that's a typo – Andrew Apr 24 '14 at 10:13
@BartoszKP Yes, I solved it by Sum = (from b in v select b.g.Summ).Sum() – user3568230 Apr 24 '14 at 10:17
there should be `v.Sum` extension method available. BTW, why do you need your `where` condition - it replicates `on` conditions of your joins. – Andrew Apr 24 '14 at 10:19
@user3568230 Don't write like that, seriously. Look at the updated version. Much cleaner. – BartoszKP Apr 24 '14 at 10:21
Exactly, shorter and faster. And remove the `where` condition, it's only slowing things down. – Andrew Apr 24 '14 at 10:24

score 0 · Answer 2 · answered Apr 24 '14 at 10:04

Assuming that g.Summ is a numeric value you want to sum on, and not something you actually want to group on then it shouldn't be part of the key.

I've no way of testing this, but something like this might be the answer:

c in context.Auto
                     join r in context.Label on c.idAutoChar equals r.idAutoChar
                     join g in context.Rent on c.idAuto equals g.idAuto
                     where c.idAutoChar == r.idAutoChar && c.idAuto == g.idAuto
                     group new {c, r, g} by new {c.idAuto, r.Name, c.RegNumber} into v
                     select new
                     {
                         v.Key.idAuto,
                         v.Key.Name,
                         v.Key.RegNumber,
                         Sum = (from b in v
                                           select b.Summ).Sum()
                     };

Note we don't include c.Summ in the group by, and the Sum is then over b.Summ. I also think that g (Rent?) isn't actually required in this linq statement.

`Sum` accepts key selector, so it would be a bit more readable to use instead of the `(from b in v select b.Summ)` part. — BartoszKP, Apr 24 '14 at 10:12
Thank you. You helped me. But just it should be like this: Sum = (from b in v select b.g.Summ).Sum() — user3568230, Apr 24 '14 at 10:13

Linq Group by id

2 Answers2