LINQ query on birth date to get age

Question

I'm runing a query (in northwind db) on the employees birth date and I want to get their age. How do i do that?

    private void Form1_Load(object sender, EventArgs e)
    {
        using (NorthWindDataContext dataContext = new NorthWindDataContext())
        {
            DateTime today = DateTime.Today;

            var q1 = from z in dataContext.Employees
                     select new
                     {
                         z.FirstName,
                       today-z.BirthDate <---- problem  here
                     };

            dataGridView1.DataSource = q1;

        }
    }

in my final grid i want column of age.

birthdate = 29/05/1969 (example)

Answer 1

This can be tricky because you need to make sure the birthday has passed this year, otherwise your age will be out by one.

var query = from e in dataContext.Employees
            //get the difference in years since the birthdate
            let years = DateTime.Now.Year - e.BirthDate.Year
            //get the date of the birthday this year
            let birthdayThisYear = e.BirthDate.AddYears(years)
            select new
            {
                //if the birthday hasn't passed yet this year we need years - 1
                Age = birthdayThisYear > DateTime.Now ? years - 1 : years
            };

Doing it this way has the advantage of calculating the age in the generated SQL query. This means you don't need to iterate through the result set on the client side to calculate the age.

In case you're interested, it will produce the following SQL:

-- Region Parameters
DECLARE @p0 Int = 2013
DECLARE @p1 DateTime = '2013-02-14 09:08:46.413'
DECLARE @p2 Int = 1
-- EndRegion
SELECT [t2].[value] AS [years], [t2].[value2] AS [birthdayThisYear], 
    (CASE 
        WHEN [t2].[value2] > @p1 THEN [t2].[value] - @p2
        ELSE [t2].[value]
     END) AS [Age]
FROM (
    SELECT [t1].[value], DATEADD(YEAR, [t1].[value], [t1].[BirthDate]) AS [value2]
    FROM (
        SELECT [t0].[BirthDate], @p0 - DATEPART(Year, [t0].[BirthDate]) AS [value]
        FROM [Employees] AS [t0]
        ) AS [t1]
    ) AS [t2]

Answer 2

Create anonymous object that has 3 properties: FirstName, Age and BirthDate (this is needed for check if employee birthday has passed this year or not).

var q1 = (from z in dataContext.Employees
    select new
    {
         FirstName = z.FirstName,
         Age = today.Year - z.BirthDate.Year,
         BirthDate = z.BirthDate
    }).ToList();

q1.ForEach(x => if (x.BirthDate > today.AddYears(-x.Age)) { x.Age--; });

According to comments (that are right) you should also check if BirthDate has passed or not, so after selecting data you have to run check for that.