如何在 Linq to SQL 表达式中使用 SQL 的 GETDATE() 和 DATEADD()？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/200617/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How do I use SQL's GETDATE() and DATEADD() in a Linq to SQL expression?
提问by Thomas Jespersen
If I have a Linqto SQLexpression like this:
如果我有Linq这样的SQL表达：
from subscription in dbContext.Subscriptions
where subscription.Expires > DateTime.Now
select subscription
I want this to to use the SQL Servers GETDATE()function instead of the time of the machine running the C#program.
我希望它使用 SQL ServersGETDATE()函数而不是机器运行C#程序的时间。
The next question would be how to translate this:
下一个问题是如何翻译：
DateTime.Now.AddDays(2)
to this:
对此：
DATEADD(dd, 2, GETDATE())
回答by liggett78
Try this:
试试这个：
[Function(Name="GetDate", IsComposable=true)]
public DateTime GetSystemDate()
{
MethodInfo mi = MethodBase.GetCurrentMethod() as MethodInfo;
return (DateTime)this.ExecuteMethodCall(this, mi, new object[]{}).ReturnValue;
}
EDIT: this needs to be a part of your DataContext class.
编辑：这需要成为您的 DataContext 类的一部分。
Now you can use GetSystemDate() instead of DateTime.Now in your queries. As for date differences take a look at System.Data.Linq.SqlClient namespace, especially DayDiffXXX functions of SqlMethods class.
现在，您可以在查询中使用 GetSystemDate() 而不是 DateTime.Now。至于日期差异，请查看 System.Data.Linq.SqlClient 命名空间，尤其是 SqlMethods 类的 DayDiffXXX 函数。
回答by Panos
If you don't mind querying the database before every use, I would suggest the following workaround: Use ExecuteQuery in one place to get the date in the data context like this:
如果您不介意在每次使用前查询数据库，我建议采用以下解决方法：在一处使用 ExecuteQuery 以获取数据上下文中的日期，如下所示：
public partial class YourDataContext
{
public DateTime GetDate()
{
return ExecuteQuery<DateTime>("SELECT GETDATE()").First();
}
}
and then you can write
然后你可以写
from subscription in dbContext.Subscriptions
where subscription > dbContext.GetDate().AddDays(2)
select subscription
回答by Per Hornsh?j-Schierbeck
You could use the ExecuteQuery to gain full control of the sql http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions.aspx
您可以使用 ExecuteQuery 来完全控制 sql http://weblogs.asp.net/scottgu/archive/2007/08/27/linq-to-sql-part-8-executing-custom-sql-expressions。 aspx
I know it seems like very little gain (or perhaps on the contrairy) over ADO.NET though.
我知道它似乎比 ADO.NET 获得的收益很少（或者相反）。
