c# - LINQ query on birth date to get age -

c# - LINQ query on birth date to get age -

i'm runing query (in northwind db) on employees birth date , want age. how do that?

private void form1_load(object sender, eventargs e) { using (northwinddatacontext datacontext = new northwinddatacontext()) { datetime today = datetime.today; var q1 = z in datacontext.employees select new { z.firstname, today-z.birthdate <---- problem here }; datagridview1.datasource = q1; } }

in final grid want column of age.

birthdate = 29/05/1969 (example)

this can tricky because need create sure birthday has passed year, otherwise age out one.

var query = e in datacontext.employees //get difference in years since birthdate allow years = datetime.now.year - e.birthdate.year //get date of birthday year allow birthdaythisyear = e.birthdate.addyears(years) select new { //if birthday hasn't passed yet year need years - 1 age = birthdaythisyear > datetime.now ? years - 1 : years };

doing way has advantage of calculating age in generated sql query. means don't need iterate through result set on client side calculate age.

in case you're interested, produce next sql:

-- part parameters declare @p0 int = 2013 declare @p1 datetime = '2013-02-14 09:08:46.413' declare @p2 int = 1 -- endregion select [t2].[value] [years], [t2].[value2] [birthdaythisyear], (case when [t2].[value2] > @p1 [t2].[value] - @p2 else [t2].[value] end) [age] ( select [t1].[value], dateadd(year, [t1].[value], [t1].[birthdate]) [value2] ( select [t0].[birthdate], @p0 - datepart(year, [t0].[birthdate]) [value] [employees] [t0] ) [t1] ) [t2]

c# linq-to-sql