Grouping and count with LinqToSQL

Question

I have an artist table and a style table.

artist table: id name styleid

style table id name

An artist has a style id

I'm trying to return a style summary which includes style id, style name and number of artists belonging to that style.

I've got the following statement which gets me the id and number of artists but I cant see how to also get the style name.

          return from s in DBContext.Styles
               join artist in DBContext.Artists on s.ID equals artist.StyleID
               group artist by artist.StyleID into a
               select new DTO.StyleSummary
               {
                   ID = a.Key,
                   NumberOfArtists = a.Count()
               };

Is this possible with a single round trip?

Answer 1

You could do a count in a sub query like this...

var results =
    from s in DBContext.Styles
    select new
    {
        Id = s.Key,
        NumberOfArtists = 
        (
            from a in DBContext.Artists
            where a.StyleId = s.StyleId
            select a
        ).Count()
    }

Answer 2

You can use an anonymous type to group by multiple values:

    return from s in DBContext.Styles
           join artist in DBContext.Artists on s.ID equals artist.StyleID
           group artist by new {s.ID, s.Name } into a
           select new DTO.StyleSummary {
               ID = a.Key.ID,
               Name = a.Key.Name,
               NumberOfArtists = a.Count()
           };