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

Question

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?

Answer 1

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

Answer 2

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;

Answer 3

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;