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

I believe you would chain the SelectMany method to the end of your result. This returns an IEnumerable of IEnumerables.

Answer 2

How about grouping by x.Year/10? (haven't tested this!)

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

EDIT1: Changed (x.Year / 10) to (x.Year / 10 * 10) to get, for example, 1980 instead of 198.

EDIT2: In your example, "group x by x.Year.Value.ToString().Substring(0, 3) + "0s") into decades" should work, too. No need for the lamba syntax.

EDIT3: Removed an extra * 10. Thanks Marc!

Answer 3

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 }

Answer 4

It looks like we cannot do a grouping or select or similar on calculated fields that are definied in the partial classes on the entity framework.

The calculated fields can be used on LINQ to objects (so you could return all the data as objects and then do a grouping)