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

DateTime.Now.AddDays(2)

to

DATEADD(dd, 2, GETDATE())

Answer 1

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

I know it seems like very little gain (or perhaps on the contrairy) over ADO.NET though.

Answer 2

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

Answer 3

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.