compare age with birthdate in where clause

Question

how to compare age with birthdate in where clause? Somthing like this code:

myRepository.Where(x =>
    fromAge <= x.BirthDate.Age && x.BirthDate.Age <= toAge)
    .Select(x).toList();

Data Types:

fromAge, toAge : int

x.BirthDate: DateTime

What's wrong with your code? – Peter Smith Jul 10 '17 at 11:55 — Peter Smith, Jul 10 '17 at 11:55

score 1 · Accepted Answer · answered Jul 10 '17 at 15:43

after searching, i ended up with this solution:

toDate = DateTime.Now.AddYears(-fromAge).Date.AddYears(1).AddDays(-1);
fromDate = DateTime.Now.AddYears(-toAge).Date.AddYears(-1).AddDays(+1);

consider changing "fromAge" to "toDate" and "toAge" to "fromDate". so:

myRepository.Where(x => fromDate <= x.BirthDate && x.BirthDate <= toDate)
    .Select(x).toList();

score 0 · Answer 2 · answered Jul 10 '17 at 12:06

0

I presume x.BirthDate is a DateTime.

You can get a person's age given a DateTime representing their D.O.B. like so:

double age = Math.Floor(DateTime.Now.Subtract(BirthDate).TotalDays / 365.25m);

You can make this into an extension method:

public static int Age(this DateTime birthDate)
{
    return (int)Math.Floor(DateTime.Now.Subtract(birthDate).TotalDays / 365.25m);
}

And then you can do:

myRepository.Where(x => fromAge <= x.BirthDate.Age() && x.BirthDate.Age() <= toAge).ToList();

Be advised however, if myRepository is an EntityFramework DbSet, this will not work. You will have to do myRepository.AsEnumerable().Where(// etc), otherwise you will get an exception.

answered Jul 10 '17 at 12:06

stelioslogothetis

9,371
3
28
53

LINQ to Entities does not recognize the method 'Int32 Age(System.DateTime)' method, and this method cannot be translated into a store expression. – Ali Dehqan Jul 10 '17 at 12:12
@AliDehqan I explained how to avoid that in the last line of my answer. – stelioslogothetis Jul 10 '17 at 12:13
you're getting all data from repository with AsEnumerable() and then using where. which is not the correct way – Ali Dehqan Jul 10 '17 at 15:17
@AliDehquan There is no "correct" way. There are many different ways, each with its own pros and cons. The one I have provided works perfectly, and will only cause problems if you're dealing with extremely large amounts of data (millions of rows). – stelioslogothetis Jul 10 '17 at 15:33

Kemal Güler · Answer 3 · 2017-07-10T12:36:39.530

0

Construct a WHERE clause in the following way

myRepository.Where(x =>
        fromAge <= (Datetime.Now.Year - x.BirthDate.Year) && (Datetime.Now.Year - x.BirthDate.Year) <= toAge)
        .Select(x).toList();

edited Jul 10 '17 at 12:36

answered Jul 10 '17 at 12:07

Kemal Güler

608
1
6
21

that's not the correct way of computing age. – Ali Dehqan Jul 10 '17 at 15:11

score 0 · Answer 4 · answered Jul 10 '17 at 12:08

0

Assuming fromAge and toAge are years:

myRepository.Where(x => DateTime.Now.AddYears(-fromAge) <= x.BirthDate && x.BirthDate <= DateTime.Now.AddYears(-toAge)).ToList();

answered Jul 10 '17 at 12:08

Pablo notPicasso

3,031
3
17
22

that's not the correct way of computing age. – Ali Dehqan Jul 10 '17 at 15:11

mpk · Answer 5 · 2017-07-10T17:54:35.813

0

I think you have to add this to your BirthDate class:

[NotMapped]
public int Age { 
    get {
        DateTime now = DateTime.Now;
        int age = now.Year - BirthDate.Year;
        if(now < BirthDate.AddYears(age)) age--;
        return age;
        }
   }

this is make your age property for auto calculate from birthdate

edited Jul 10 '17 at 17:54

answered Jul 10 '17 at 12:10

mpk

21
4

your function is correct, but we can not use Age method in query expression. – Ali Dehqan Jul 10 '17 at 12:14
myRepository.Where(x => fromAge <= x.BirthDate.Age).Where(x.BirthDate.Age <= toAge) .Select(x).toList(); – mpk Jul 10 '17 at 12:20
LINQ to Entities does not recognize the method 'Int32 Age(System.DateTime)' method, and this method cannot be translated into a store expression. – Ali Dehqan Jul 10 '17 at 15:09
you have to create a [NotMapped] property next to the BirthDate property called Age like mine – mpk Jul 10 '17 at 15:21

compare age with birthdate in where clause

5 Answers5