Linq to Sql, group by 2 property and substring

Question

I have an initial query that I want to modify to increase granularity in my results. But Visual Studio tells me my query isn't valid and I can't understand why. Basically I want to group my data according to 2 property (columns) and also group one of the property by the first N characters.

Initial query that works:

List<PostalCode> codes = (from customer in bd.Customers
                        group customer by customer.postalcode.Substring(0, postalCodeLength) into postalCodes
                        select new PostalCode
                        {
                            Postal = postalCodes.Key,
                            Count = postalCodes.Count()
                        }).ToList();
                return codes;

Query marked by ** as wrong by VS2010:

List<PostalCode> codes = (from customer in bd.Customers
                          group customer by new { **customer.postalcode.Substring(0, postalCodeLength)**, customer.CustomerGroupType}
                          into postalCodes
                          select new PostalCode 
                          { 
                                Postal = postalCodes.Key.postalcode,
                                CustomerGroupType = postalCodes.Key.CustomerGroupType,
                                Count = postalCodes.Count() 
                          }).ToList();
 return codes;

Answer 1

The new { } object syntax requires that properties have names - something your original query did not require. It cannot infer a name from your method call. So I'd recommend changing it to something like:

from customer in bd.Customers
group customer by new { TrimmedPostalCode = customer.postalcode.Substring(0, postalCodeLength), customer.CustomerGroupType}
into postalCodes
select new PostalCode 
{ 
    Postal = postalCodes.Key.TrimmedPostalCode,
    CustomerGroupType = postalCodes.Key.CustomerGroupType,
    Count = postalCodes.Count() 
}