CODEWITHSUNDEEP
c#linq-to-sql
darko
darko

Reputation: 793

LINQ query on birth date to get age

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)

Upvotes: 9

Views: 11741

Answers (5)

Doctor Jones
Doctor Jones

Reputation: 21664

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]

Upvotes: 18

daryal
daryal

Reputation: 14919

var temp = from o in dataContext.Employees
    select new
    {
         FirstName = o.FirstName,
         Age = DateTime.Now.Year - z.BirthDate.Year -1
    };

Upvotes: 1

gzaxx
gzaxx

Reputation: 17600

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.

Upvotes: 6

Marius Bancila
Marius Bancila

Reputation: 16328

You need a named property, something like this:

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
                 {
                     Name = z.FirstName,
                     Age = today - z.BirthDate
                 };

        dataGridView1.DataSource = q1;

    }
}

Upvotes: 1

Darren
Darren

Reputation: 70728

 var q1 = from z in dataContext.Employees
          select new
         {
            z.FirstName,
            z.BirthDate,
            Age = DateTime.Now.Year - z.BirthDate.Year
         };

Upvotes: 0

Related Questions