Sql语句转换成linq语句

Question

select Line_Number, ODP_Date,sum(Quantity)

from [dbo].[Line_Daily_ODP_Detail]
where Is_Completed=1

and DATEDIFF (day ,ODP_Date,getdate())=0
and (
( datepart(dw,getdate())>1 and DATEDIFF (week ,ODP_Date,getdate())=0 and datepart(dw,ODP_Date)<>1)
or ( datepart(dw,getdate())=1 and ( DATEDIFF (week ,ODP_Date,getdate()-1 )=0

or DATEDIFF (week ,ODP_Date,getdate())=0 )
)
)
group by Line_Number, ODP_Date order by Line_Number, ODP_Date 如何把这个转换成linq语句

Answer 1

可以尝试使用以下的 LINQ 语句来实现相同的功能：

var today = DateTime.Today;

var query = from detail in dbContext.Line_Daily_ODP_Detail
            where detail.Is_Completed
               && EntityFunctions.DiffDays(detail.ODP_Date, today) == 0
               && (
                    (DbFunctions.DiffDays(today, detail.ODP_Date) > 1 && EntityFunctions.DiffWeeks(detail.ODP_Date, today) == 0 && EntityFunctions.DiffDays(detail.ODP_Date.DayOfWeek, DayOfWeek.Monday) != 0)
                    || (DbFunctions.DiffDays(today, detail.ODP_Date) == 1 && (EntityFunctions.DiffWeeks(detail.ODP_Date, today.AddDays(-1)) == 0 || EntityFunctions.DiffWeeks(detail.ODP_Date, today) == 0))
                  )
            group detail by new { detail.Line_Number, detail.ODP_Date } into g
            orderby g.Key.Line_Number, g.Key.ODP_Date
            select new
            {
                Line_Number = g.Key.Line_Number,
                ODP_Date = g.Key.ODP_Date,
                Quantity = g.Sum(x => x.Quantity)
            };

值得注意的是，这里假设 dbContext 是数据库上下文对象。此外，由于 DATEDIFF 等函数并不被 EF 6 直接支持，因此我们要使用 EntityFunctions 或 DbFunctions 进行日期计算。