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.

Solution 1

For grouping multiple tables you can do as:

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

Solution 2

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

Comments

[Petr Behenský]

Select part should be: select new { Count = grp.Count(), grp.Key.SpaceID, grp.Key.SpaceCode }

[Buggieboy]

@Arion: Petr Behensky's correction still doesn't appear in your solution.

[Admin]

In this case, how to get other fields of r, s in select new? grp.r or grp.s seem not working.

[Pablo]

How can I sum others columns from r, or s in the select new?

[Pablo]

How can I sum a column like grp.Sum(x => x.Col1)? I'm trying but it's not working