我正在尝试将Linq上的这个SQL查询转换为sql查询，但我对Avg（）和DateDiff（）有一些困难

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().

我正在尝试在Linq to SQL查询中翻译此SQL查询。我的问题是AVG（）和DATEDIFF（）。

2 个解决方案

#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);

#2

**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.

注意：我已经像上面一样解决了它，因为p.Birthday以“yyyy”格式返回一个字符串。

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

或者我也可以通过应用“Take（1）”而不是“Distinct（）”来完成它，如下所示：

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);

#1