How do I use SQL's GETDATE() and DATEADD() in a Linq to SQL expression?

Question

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()) 

Answer 1

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.