Using LINQ how do I have a grouping by a "calculated field"

Question

I am using LINQ to EF and have the following LINQ query:

var results = (from x in ctx.Items
               group x by x.Year into decades
               orderby decades.Count() descending
               select new { Decade = decades.Key, DecadeCount = decades.Count() });

So this kind of gets me to where I want to be, in that I get the items broken down by year and a count of items in that year. (i.e. 2001 - 10, 1975 - 15, 2005 - 5, 1976 - 1) The thing I really want to do though is to break them down by decade (i.e. 2000s - 15, 1970s - 16).

How does one have a "calculated field" in the "by" part of the group clause for a Linq statement. I think what I want is basically something like:

var results = (from x in ctx.Items
               group x by (x => x.Year.Value.ToString().Substring(0, 3) + "0s") into decades
               orderby decades.Count() descending
               select new { Decade = decades.Key, DecadeCount = decades.Count() });

Or more generally the syntax so that I can do some more complicated evaluation/calculation to do the group by on. Any ideas?

EDIT (update):

(x => x.Year.Value.ToString().Substring(0, 3) + "0s") - Doesn't Work - "LINQ to Entities does not recognize the method 'System.String ToString()' method, and this method cannot be translated into a store expression."

(x.Year / 10 * 10) - Functionally works (thank you) - the only "problem" is that the 's' is not on the end (i.e. 1970 vs. 1970s)

Is there anyway to put a function in the by clause? i.e. group x by this.ManipulateYear(x.Year) into decades ... or ... x => x.Year.Value.ToString().Substring(0,3) + "0s" ?? It would be nice to have some technique (such as calling a function or using a lambda expression) so that I can cover any case that I can think of.

Thanks again for everyone's help on this.

Answer 1

You can use the let clause to avoid counting the decades multiple times:

from x in ctx.Items
group x by (x.Year / 10 * 10) into decades
let decadeCount = decades.Count()
orderby decadeCount descending
select new { Decade = decades.Key, DecadeCount = decadeCount }