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 ReportHelper { 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 :)

回答1:

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() 
               };

回答2:

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.

回答3:

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

回答4:

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.