Entity Framework - update field value to current D

问题:

I've pulling back an entity object from a database and I need to update the date to the DB server's date/time - usually you could accomplish this by setting it with the SQL getDate() function.

How can I accomplish this in the following scenario:

var client = context.client.Where(c=>c.clientID == 1).FirstOrDefault();

// the value needs to be the value of the server's current date, i.e, getDate()... not DateTime.Now
client.someDate = <somevalue>;
context.SaveChanges();

回答1:

Try inserting the following in place of the fourth line in your sample:

var dateQuery = context.CreateQuery<DateTime>("CurrentDateTime() ");
client.SomeDate = dateQuery.AsEnumerable().First();

回答2:

DateTime.Now will give the current date from your server(Web Server not the DB Server). if client.someDate is DateTime field then you can assign DateTime.Now and update it back to the database.

回答3:

Here's a way I'm doing this in an EF6/MVC5 VB.NET application.

Problem: using one query to return the getdate() value, then using another query to update a record with the retrieved getdate() value is unacceptable IMHO. That's two queries where only one ought to be required. Plus, the returned getdate() value technically isn't accurate by the time it's written back, since time has passed.

Solution: the below snippet updates a single nullable datetime field to the current value of getdate(), and returns the updated value of getdate() (if you don't need the value returned, just remove the OUTPUT INSERTED.theTime segment). All this is done in a SQL query.

Dim theTime As DateTime? = db.Database.SqlQuery(Of DateTime?) _
("UPDATE myTable SET theTime = getdate() OUTPUT INSERTED.theTime WHERE someRecordID = @someRecordID", New SqlParameter("@someRecordID", someRecordID)).First()

It seems like there must be some better solution (or this is a gross oversight on the part of MS, isn't using getdate() a common and proper practice while updating records?). I'd be thrilled if someone could point out a cleaner approach in the comments or a different answer.

Ideally, I'd like to be able to phrase this like:

Dim craftBeer = context.CraftBeers.Find(id)
... (update other craftBeer properties) ...
craftBeer.date_emptied = SqlServer.SqlFunction.GetDate
context.Entry(craftBeer).State = EntityState.Modfied
context.SaveChanges()

... but unfortunately, apparently SqlServer.SqlFunctions.GetDate doesn't work with EF like this.

回答4:

I see this is a very old question, but I am having this issue too. It just occurred to me that you could create a scalar function on the SQL Server that simply RETURNS GETDATE(). Then in entity framework, pull that SQL function into your context. Seems a little contrived, but I think it would work.

回答5:

Maybe too late, but I leave it if it can help someone, We can get the current date time from the database server with something like:

public DateTime GetDate()
{ 
  var date = context.client.Select(c => DateTime.Now).First(); 
  return date;
}

so We use the function:

var client = context.client.Where(c=>c.clientID == 1).FirstOrDefault();
client.someDate = GetDate();
context.client.Add(client);