How do I use SQL's GETDATE() and DATEADD() in a Linq to SQL expression?
If I have a Linq
to SQL
expression like this:
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.
The next question would be how to translate this:
DateTime.Now.AddDays(2)
to this:
DATEADD(dd, 2, GETDATE())
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.
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.
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:
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