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

时间:2022-05-17 11:32:25
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


0  

Try something like below

尝试类似下面的内容

EF6

EF6

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

EF4

EF4

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

tryAlso

tryAlso

double resultAverage = ctx.Person.Average(p => (DateTime.Now - p.BirthDay).Days);

#2


0  

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


0  

Try something like below

尝试类似下面的内容

EF6

EF6

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

EF4

EF4

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

tryAlso

tryAlso

double resultAverage = ctx.Person.Average(p => (DateTime.Now - p.BirthDay).Days);

#2


0  

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