OK, you’ve put a default value of GETDATE() on a column in your database and you’re inserting a record via InsertOnSubmit. Oh no, the current server date isn’t applied to your record. How can you get the server date and apply it?
Well, it turns out that there’s a really simple trick that just requires a little bit of skullduggery on your part. (I like that word). Create a partial class that corresponds to your data context. In that class, put the following code:
[Function(Name="GetDate", IsComposable=true)]
public DateTime GetSystemDate()
{
MethodInfo mi = MethodBase.GetCurrentMethod() as MethodInfo;
return (DateTime)this.ExecuteMethodCall(this, mi, new object[]{}).ReturnValue;
}
Then, all your code needs to do is call GetSystemDate() whenever you need to the current date from SQL.
Note: You may wonder why I’ve gone to all of this trouble, rather than using DateTime.Now in the client code. By using this method, we get the date and time based on the database server, rather than on client connections which may be set to different times.
Thanks for the tip, its cool to see that we can use attributes to access SQL functions like that.
Comment by DW — January 21, 2008 @ 11:09 pm
Is there an advantage to this method over ExecuteQuery?
(VB)
Private Function GetServerTime() as DateTime
Dim result as IEnumerable(Of DateTime) = _
Me.ExecuteQuery(GetType(DateTime), _
“SELECT GETDATE() AS CurrentSQLDateTime”, _
New Object() {})
Return result.Single
End Function
If so, any idea of the VB syntax for those bracketed attributes?
Comment by Jay — February 14, 2008 @ 8:11 pm
Jay - there’s no advantage to this, other than it’s more Linq trickery.
Comment by peteohanlon — February 15, 2008 @ 10:40 am
Actually there is an advantage, and quite a big one..
If you use the code that Jay provided, the query will be ran separately, and the date will be passed as a parameter. Furthermore, it can’t be used in precompiled queries!
With the code provided by peteohanlon, linq will replace your GetSystemDate call in the linq query with GetDate in the resulting SQL query! And it can be used in precompiled queries
It can also be used just to get the datetime though. In this case both are pretty much identical..
Regards,
Artiom
Comment by Artiom — June 11, 2008 @ 7:07 pm