How to use SQL server clock in LINQ query

Question

I am writing an application with the "check" function - basically an application in the system tray that is checked every 20 seconds, writing SQL db.

Then I have a second application that checks this table to check if the client has been registered, and performs an action if the client has not verified it for 60 seconds.

I need to make sure that the time written to the sql database is the local server time, not the client time, because otherwise I will have synchronization problems.

I am using Linq-to-SQL - how can I get this?

Answer 1

In fact, as a rule, you should also not use the local server time - use UTC ( Coordinated Universal Time ) instead.

In Linq to Sql, you can use SqlFunctions.GetUtcDate() for this, which maps to GETUTCDATE() in SQL Server.

Answer 2

Write a custom function that returns GetDate() and adds it to the dbml file

Answer 3

Try the following:

 [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 should be part of your DataContext class.

Answer 4

This solution is for any version of .NET: it should be more profitable for speed and network traffic than other solutions.

1) create a function on the SQL server:

 CREATE FUNCTION [dbo].[GetSQLServerDate] () RETURNS datetime AS BEGIN DECLARE @Result datetime SELECT @Result = getdate() RETURN @Result END GO 

2) Add a function to the LINQ to SQL diagram in the Stored Procedures section

3) Call your function from your code

 THEDBDataContext DBConn = new THEDBDataContext (); DateTime dt = (DateTime) DBConn.GetSQLServerDate(); 

Answer 5

Acaz Souza's answer works, but it should be part of the data context. To put it outside a specific data context class, which is probably more reused, try this as an extension method:

 public static DateTime GetSystemDate(this DataContext db) { return db.ExecuteQuery<DateTime>("SELECT GETDATE()").Single(); }