27

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())
massko
  • 589
  • 1
  • 7
  • 22
Thomas Jespersen
  • 11,493
  • 14
  • 47
  • 55

3 Answers3

43

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.

liggett78
  • 11,260
  • 2
  • 29
  • 29
  • 1
    Note: this gets translated directly into GETDATE() function, e.g. no separate calls to SQL Server. Just verified it. – liggett78 Oct 14 '08 at 13:23
  • Genius! Nice solution and much appreciated. – Stephen Kennedy Jul 11 '11 at 19:14
  • 1
    MethodInfo is System.Reflection.MethodInfo and Function is System.Data.Linq.Mapping.Function – Trisped Mar 26 '12 at 19:26
  • I don't see how the date diff methods can take place of DATEADD(). DATEADD() returns a date depending on the difference specified, but the date diff methods listed in the System.Data.Linq.SqlClient namespace only return the differences between dates, which the user in this case already knows. http://msdn.microsoft.com/en-us/library/system.data.linq.sqlclient.sqlmethods.aspx – Jesslyn Sep 19 '12 at 13:00
6

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
Panos
  • 18,992
  • 6
  • 45
  • 54
0

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.

Per Hornshøj-Schierbeck
  • 15,097
  • 21
  • 80
  • 101