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.
Confessions of a coder 
Thanks for the tip, its cool to see that we can use attributes to access SQL functions like that.
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?
Jay – there’s no advantage to this, other than it’s more Linq trickery.
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
There is a much easier and more efficient way of doing this. Simply navigate to your linq to sql(.dbml) file, click on the appropriate column and in the properties window set the “Auto Generated Value” to true. Now you dont have to spend time creating this function and calling it every time you want this to occur.
It would be nice if you didnt even have to set “Auto Generated Value” property at all however.
Nice – I’ll have a try. I originally wrote this because I couldn’t find a way to get the auto value, and the documentation for L2S was sparse at the time.
The preceding 2 examples do have a distinct advantage over the dbml method, though: you don’t have to declare getdate() as a default method on your datetime column to get it to populate the record field with the current (database) server time. I have a scenario where I want the datetime to be null by default, and only sometimes be filled in either with the current timestamp, or an explicitly supplied value.
So thanks to Pete and Jay for your solutions also! (That being said, the dbml method is also really good to know. Thanks!)
thanks very much
az taraf barobach
Ideally you’ll store all dates as UTC.. use getutcdate() instead of getdate().. then just use DateTime.UtcNow in C# where needed. This way, you can easily translate this to any local time imaginable.
What are these namespaces used? It couldn’t identify the attribue “Function” keyword.
using System;
using System.Data.Linq.Mapping;
using System.Reflection;