Group Multiple Tables in LINQ

Question

I have a very simple SQL query:

SELECT r.SpaceID, Count (*), SpaceCode 
FROM Rider r JOIN Spaces s 
ON r.SpaceID = s.SpaceID
GROUP BY r.SpaceID, s.SpaceCode

Please note that my group by clause is on multiple tables, I want to do the same in LINQ, I know how to group single table, but about multiple tables I have no idea.

Just see here: http://stackoverflow.com/questions/3435485/linq-group-by-multiple-tables — aF., Jan 04 '12 at 09:48
Thanks, i've already seen that, but Concat does not seems like a good option, moreover the structure for both of my table is too different, so this is not gonna work for me, — Manvinder, Jan 04 '12 at 09:50

score 79 · Accepted Answer · answered Jan 04 '12 at 09:54

79

For grouping multiple tables you can do as:

group new { r,s } by new { r.SpaceID, s.SpaceCode }

answered Jan 04 '12 at 09:54

Pankaj Tiwari

1,378
11
13

How can I sum others columns from `r`, or `s` in the `select new`? – Pablo Jan 04 '21 at 12:23

Arion · Answer 2 · 2012-11-19T09:02:40.407

41

this might help:

(
    from r in db.Rider
    join s in db.Spaces
        on r.SpaceID equals s.SpaceID
    group new { r,s } by new { r.SpaceID, s.SpaceCode }
    into grp
    select new
    {
        Count=grp.Count(),
        grp.Key.SpaceID,
        grp.Key.SpaceCode
    }
)

Where db is the database context

edited Nov 19 '12 at 09:02

answered Jan 04 '12 at 10:00

Arion

31,011
10
70
88

1

Select part should be: `select new { Count = grp.Count(), grp.Key.SpaceID, grp.Key.SpaceCode }` – Petr Behenský Jan 04 '12 at 10:39
@Arion: Petr Behensky's correction still doesn't appear in your solution. – Buggieboy Nov 19 '12 at 03:24
2

In this case, how to get other fields of r, s in select new? grp.r or grp.s seem not working. – Jun 13 '16 at 07:12
How can I sum a column like `grp.Sum(x => x.Col1)`? I'm trying but it's not working – Pablo Jan 04 '21 at 12:24

score 0 · Answer 3 · edited May 07 '21 at 14:52

0

You can use the following command.

grp.Sum(x => x.r.Col1)

It works well.

edited May 07 '21 at 14:52

4b0

21,981
30
95
142

answered May 07 '21 at 14:27

Xiao

1

Group Multiple Tables in LINQ

3 Answers3

Linked