如何在linq中获取Sql datediff（）函数 [英] How to get Sql datediff() function in linq

问题描述

如何在linq中获取Sql datediff（）函数，我需要获得两天之间的平均天数



我的sql查询是

How to get Sql datediff() function in linq, I need to get the average days between two days

my sql query is

select  year(recDate) as year1,DateName(month,(recDate)) as mnth,
relDept, count(*) as tot,
avg(datediff(day,RecDate,isnull(intl_ack,getdate())+1)) as avg1,

avg(datediff(day,RecDate,isnull(Resol_Date,getdate())+1)) as res1,
sum(case when status='Open' then 1 else 0 end) as open1

 from  Cust_Complaints  group by year(recDate),relDept,DateName(month,(recDate))

需要转换为LINQ查询

need to convert to LINQ query

推荐答案

C＃等价物将是这样的：

The C# equivalent would be like this:

DateTime endDate = ...;
DateTime startDate = ...;
int dayDiff = (endDate - startDate).Days;

以上返回之间的差异两个日期时间。如果您有日期的字符串表示形式，请使用 DateTime .Parse [ ^ ]解析它。



您可以将上述代码放在可以从LINQ查询中调用的方法中。

The above returns the difference between two DateTimes. If you have the string representation of a date, use DateTime.Parse[^] to parse it.

You can put the above code in a method that you can call from your LINQ query.

请阅读我对该问题的评论。我们无法在不查看数据的情况下将您的SQL查询转换为Linq查询。



看看这里：

SqlMethods.DateDiffDay方法 [ ^ ]

LINQ to SQL提示和技巧 [ ^ ]

Please, read my comment to the question. We can't "convert" your sql query to Linq query without seeing the data.

Have a look here:
SqlMethods.DateDiffDay Method[^]
LINQ to SQL Tips and Tricks[^]

var qry = from a in md
        group a by new{Year = a.RecDate.Year, Month = a.RecDate.ToString("MMM"), Dept = a.RelDept} into grp
        select new
        {
            Year = grp.Key.Year,
            Month = grp.Key.Month,
            Dept = grp.Key.Dept,
            AvgIA = grp.Average(g=>(g.RecDate - (g.Intl_Ack !=null ? g.Intl_Ack : DateTime.Today)).Days),
            AvgRD = grp.Average(g=>(g.RecDate - (g.Resol_Date !=null ? g.Resol_Date : DateTime.Today)).Days),
            SumOp = grp.Sum(g=>g.Status=="Open" ? 1 : 0)
        };

这篇关于如何在linq中获取Sql datediff（）函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！