Is there a way to write a group by query in LinqToSql grouping not on a scalar value?

I have those maps in my repository.

public IQueryable<AwType> GetAwTypes()
{
    return from awt in _db.AwTypes
        select new AwType
        {
         Id = awt.Id,
         Header = awt.Header,
         Description = awt.Description
        };
}

public IQueryable<Aw> GetAws()
{
    return from aw in _db.Aws
        select new Aw
        {
         Id = aw.Id,
         Bw = (from bw in GetBws()
           where bw.Id == aw.Bw
           select bw
          ).SingleOrDefault(),
         AwType = (from awt in GetAwTypes()
          where awt.Id == awAwType
          select awt
         ).SingleOrDefault(),
         AwAttribute = aw.AwAttribute
        };
}

In service I want to get count of Bws grouped by AwType as List<KeyValuePair<AwType, int>>. When I call that linq query :

var awGroups = from aw in _repository.GetAws()
group aw by aw.AwType into newGroup
select newGroup;

List<KeyValuePair<AwType, int>> RetGroups = new List<KeyValuePair<AwType, int>>();
foreach (var group in awGroups)
{
    RetGroups.Add(new KeyValuePair<AwType, int>(group.Key, group.Count()));
}
return RetGroups;

I get an error that is saying I can't group by on an object I have to group by a scalar value like aw.AwType.Id.

Is there a way to get "AwType, int" pairs in one call?

From stackoverflow spinodal

• You can group by an anonymous type, eg new { Foo, Bar }

  spinodal : do you mean group aw by new {Foo, Bar} ... I didn't understand what you mean...
  From leppie

• AwType is a reference type. It would be a bad idea to group on that reference type... Each AwType in that query is a unique reference, so n elements would yield n groups.

  Try this:

  var awGroups = from aw in _repository.GetAws()
  group aw by aw.AwType.ID into newGroup  //changed to group on ID
  select newGroup;
  
  List<KeyValuePair<AwType, int>> RetGroups = new List<KeyValuePair<AwType, int>>();
  foreach (var group in awGroups)
  {
      //changed to get the first element of the group and examine its AwType
      RetGroups.Add(new KeyValuePair<AwType, int>(group.First().AwType, group.Count()));
  }
  return RetGroups;
  

  spinodal : Thanks, I've nearly gave up waiting an answer... :)
  From David B

• From what I understand of linq, what you would be equivalent to trying to group by every column of a table. This can only be done by listing every single field in a table in the group statement, so in your case, you would need to do the following (I'm not very knowledgable about Linq)

  var awGroups = from aw in _repository.GetAws()
  group aw by aw.AwType.Id, aw.AwType.Header, aw.AwType.Description into newGroup
  select newGroup;
  

  or maybe you can just group by Id if that's the only column you need.

  var awGroups = from aw in _repository.GetAws()
  group aw by aw.AwType.Id into newGroup
  select newGroup;
  

  From Kibbee