How to ask the database server for current datetime using entity framework?

Question

I have an entity with a ModifiedDateTime property which I want to be updated with the current datetime from the database instead of the "application" server executing the application.

Every time I want to update or add a person to my datebase on SQL Server 2008 I want to fill ModifiedDateTime filed. It's not like I can change update query as with data adapter command when I work with dataset and to define for my ModifiedDateTime filed to be GetDate(). I created stored function to return me a value of GetDate() method, but I have a problem to import procedure which returns values as int, string or no value at all, already just entity values as Person for example in my case. Why is that?

Anyway, it would be of great help if you can help me to retrieve the current DateTime from the database server.

Solution 1

Is there a reason you just can't push it down to your database? If you include DateTime.Now in your entity query, it will push it down (getdate) to the database.

Example linq to entities

 var dQuery = dbContext.CreateQuery<DateTime>("CurrentDateTime() ");
 DateTime dbDate = dQuery.AsEnumerable().First();

SQL Generated ..

SELECT GetDate() AS [C1] FROM  ( SELECT cast(1 as bit) AS X ) AS [SingleRowTable1]

Might be a better way to do it ?

Solution 2

This is an update of @Nix response to EF4:

var dateQuery = dbContext.Database.SqlQuery<DateTime>("SELECT getdate()");
DateTime serverDate = dateQuery.AsEnumerable().First();

Solution 3

An update for .net core 2.0

var dual =  databaseContext
            .Set<Dual>()
            .FromSql("SELECT -1 AS Id, GETDATE() AS DateTime")
            .First();

The fake entity

public class Dual
{
    public int Id { get; set; }
    public DateTime DateTime { get; set; }
}

Comments

[Levelbit]

This works just fine. I found out about this datetime canonical functions, but I didn't know how to use them from code behind. I saw that they can be used in CSDL. Thanks.

[Graham Laight]

Update: no longer have direct access to CreateQuery method - need to cast it first - like this: var query = ((IObjectContextAdapter)dbContext).ObjectContext.CreateQuery‌​<DateTime>("CurrentD‌​ateTime() ");

[Defkon1]

please note that CURRENT_TRANSACTION_ID() is a recognized function name only in SQL SERVER 2016+. In older version you could use @@SPID or CURRENT_REQUEST_ID()