1
select  AVG(DATEDIFF(yyyy, (p.BirthDay), GETDATE()))
from Person p

I'm trying to translate this SQL query in a Linq to SQL query. My problems is with AVG() and DATEDIFF().

lewis
  • 2,936
  • 2
  • 37
  • 72
pptr
  • 21
  • 4
  • Instead of trying to translate it, try thinking about what it is doing and think about ways to do it. https://stackoverflow.com/questions/4590704/get-average-using-linq https://stackoverflow.com/questions/9/calculate-age-in-c-sharp – lewis Aug 06 '17 at 12:05
  • Also DATEDIFF(yyyy,p.Birthday, GetDate()) returns the number of New Year's Eve's the person has seen, not their age. – David Browne - Microsoft Aug 06 '17 at 15:46

2 Answers2

1

Try something like below

EF6

double resultAverage = ctx.Person.Average(p => DbFunctions.DiffDays(p.BirthDay, DateTime.Now));

EF4

double resultAverage = ctx.Person.Average(p => SqlFunctions.DateDiff("day" ,p.BirthDay, DateTime.Now));

tryAlso

double resultAverage = ctx.Person.Average(p => (DateTime.Now - p.BirthDay).Days);
Eid Morsy
  • 966
  • 6
  • 8
1

**I SOLVED doing like below: **

var query = (from A in ctx.Persons
             select new {
             AverageAge = ctx.Persons.Average(p => (Convert.ToInt32(DateTime.Now.ToString("yyyy")) - Convert.ToInt32(p.BirthDay)))
             }).Distinct();

NOTE: i have solved it like above because p.Birthday returns a string in "yyyy" format.

Or i could do it also by applying "Take(1)" instead of "Distinct()" like below:

var query = (from A in ctx.Persons
             select new {
             AverageAge = ctx.Persons.Average(p => (Convert.ToInt32(DateTime.Now.ToString("yyyy")) - Convert.ToInt32(p.BirthDay)))
             }).Take(1);
pptr
  • 21
  • 4