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.

Which version of .NET and Visual Studio are you using? – bryanjonker Apr 06 '10 at 14:04 — bryanjonker, Apr 06 '10 at 14:04

score 13 · Accepted Answer · edited Feb 11 '16 at 15:55

13

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 ?

edited Feb 11 '16 at 15:55

David Sherret

101,669
28
188
178

answered Apr 06 '10 at 14:11

Nix

57,072
29
149
198

1

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. – Levelbit Apr 06 '10 at 14:57
3

Update: no longer have direct access to CreateQuery method - need to cast it first - like this: var query = ((IObjectContextAdapter)dbContext).ObjectContext.CreateQuery("CurrentDateTime() "); – Graham Laight Jun 26 '14 at 10:02

score 12 · Answer 2 · edited Feb 11 '16 at 15:55

12

This is an update of @Nix response to EF4:

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

edited Feb 11 '16 at 15:55

David Sherret

101,669
28
188
178

answered Jun 24 '15 at 12:31

Rodrigo

567
7
24

score 3 · Answer 3 · answered Feb 21 '19 at 10:05

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; }
}

Dzmitry Lahoda · Answer 4 · 2020-08-06T07:37:24.293

1

EF 3

var dual = await db
            .Set<Dual>()
            .FromSqlRaw(@"
                SELECT 
                    CURRENT_TRANSACTION_ID() as Id, 
                    SYSDATETIMEOFFSET() AS DateTime
            ")
            .FirstAsync();
DateTimeOffset serverTime = dual.DateTime;

public class Dual
{
    public long Id { get; set; }
    public DateTimeOffset DateTime { get; set; }
}

modelBuilder.Entity<Dual>(entity =>
{
    entity.HasNoKey();
    entity.ToView(nameof(Dual));
});

edited Aug 06 '20 at 07:37

answered Aug 05 '20 at 14:10

Dzmitry Lahoda

939
1
13
34

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()` – Defkon1 May 11 '21 at 10:01

score 0 · Answer 5 · edited May 23 '17 at 12:07

In VS 2008, if you add a function template to return a scalar, it does not add the code to make it easy to use. You need to access the function template directly -- I use the partial class to build the needed methods for ease of use. They fixed this in VS2010.

    public DateTime GetDateTime()
    {
        var returnValue = new DateTime();
        using (var connection = new EntityConnection(Connection.ConnectionString))
        {
            connection.Open();
            using (var command = connection.CreateCommand())
            {
                command.CommandText = "myStoredProc";
                command.CommandType = CommandType.StoredProcedure;
                try
                {
                    returnValue = Convert.ToDateTime(command.ExecuteScalar());
                }
                finally
                {
                    connection.Close();
                }
            }
        }
        return returnValue;
    }

More information: Function Imports in Entity Model with a non-Entity Return Type

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

5 Answers5

Linked

Related