How do I get Age distribution list by grouping

I want to return a List which contains data related to the Age Distribution (Eg. For Age 0-9 there are 10 persons, for 10-19 there are 7 persons etc)

This is my query :-

 public List<ReportHelper> GetAgeDistribution(int userId)
        {
            var itemList = (from item in this.ObjectContext.TreeMembers
                            where item.UserId == userId
                            group (DateTime.Now - (item.Birthdate ?? DateTime.Now)).Days/365.25
                            by ((DateTime.Now - (item.Birthdate ?? DateTime.Now)).Days / 365.25) / 9);

            List<ReportHelper> list = new List<ReportHelper>();

            foreach (var item in itemList)
                list.Add(new ReportHelp开发者_如何学Goer { Data = item.Count(), Label = item.Key + "-" + (item.Key + 9) });


            return list;
        }

When I run this query I get dbarithmeticexpression exception :

DbArithmeticExpression arguments must have a numeric common type.

How do I solve this error? I know Entity Framework 4 is not able to translate my expression to plain old sql. How can I hint it what I am trying to do?

Thanks in advance :)

Saeed is right; the best way to go here is to use the canonical DiffYears function through the EntityFunctions.DiffYears method.

In addition, the logic you have for dividing ages into the buckets [0 - 9] years, [10 - 19] years etc. seems incorrect; you should divide complete years by 10, not 9.

This query should work fine:

var itemList = from item in this.ObjectContext.TreeMembers
               where item.UserId == userId
               let age = EntityFunctions.DiffYears
                              (item.Birthdate, DateTime.Now) ?? 0
               group item by age / 10 into ageGroup
               select new
               {
                   AgeInCompletedDecades = ageGroup.Key, 
                   Count = ageGroup.Count() 
               };

you can't do this in EF:

DateTime.Now - (item.Birthdate ?? DateTime.Now))....

you can use Entity canonical functions for this purpose.

In fact you can't do DateTime1 - DateTime2 in EF.

My guess would be to change 365.25 to 365 (so it's an int like the Days property).

What if you converted your dates to numerical values using the mydate.ToOADate() function? This function converts dates to a double value where the whole number portion of the double represents what day it is and the fractional part of the double represents a fractional day (i.e. time of day).

I don't know much about Entity arithmetic, but if the dates were doubles then it might not throw that exception - just a guess.