Thomas Jespersen Thomas Jespersen - 16 days ago 7
SQL Question

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

Answer

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.