Set SQL Server time to Entity Framework CreatedOn column

Question

I want to fill CreatedOn column of my entities. I know the approach of overriding SaveChanges() and setting there value, something like this:

public override int SaveChanges()
{
    foreach (var item in ChangeTracker.Entries<ITrackableEntity>().Where(c => c.State == EntityState.Added))
    {
        item.Entity.CreatedOn = DateTime.Now;
    }

    return base.SaveChanges();
}

interface ITrackableEntity
{
    DateTime? CreatedOn { get; set; }
}

The problem is that I'm setting client side time which is unacceptable in my case (multiuser desktop app, users could have different time on theirs computers). How can I set Sql server time to CreatedOn column? I don't want to do this in triggers. I want something that translates into that SQL:

INSERT INTO SomeTable (Column1, CreatedOn)
VALUES Value1, GETDATE();

You're mixing up `INSERT` and `UPDATE` syntax - you cannot use `INSERT` with `SET .....`. The best place to do this would be a **trigger** - why are you categorically exluding this option?? — marc_s, Nov 18 '14 at 13:46
@marc_s thanks, I've corrected sql syntax. I believe that trigger is a hidden logic that I want to avoid. And I use database-first approach and don't want to edit my numerous tables. — Oleg Gliznutsa, Nov 18 '14 at 13:55

score 0 · Accepted Answer · edited May 23 '17 at 12:27

0

How important actual datetime value is ?

If You dont need precisely value You can use :

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

Original link: How to ask database server for current datetime using entity framework?

edited May 23 '17 at 12:27

Community

1
1

answered Nov 18 '14 at 13:35

dzaba

90
1
7

Nice workaround but is there any alternatives without extra db querying? – Oleg Gliznutsa Nov 18 '14 at 14:01
You can use what @xxMURoXX suggested but for the sake of future readability You can sacrifice one query for keeping business logic in one place imho – dzaba Nov 18 '14 at 16:01

score 0 · Answer 2 · answered Nov 18 '14 at 14:00

Have you alter permissions to the database table. If so you can modify the corresponding column:

CREATE TABLE test
(
      ...
      CreatedOn DATETIME2(7) NOT NULL DEFAULT(GETDATE())
)

Then, after updating your edmx file, you set the corresponding property to StoreGeneratedPattern=Computed. Now you can save your entity without Setting CreatedOn in your code. Everything is done by the SQL-Server

Set SQL Server time to Entity Framework CreatedOn column

2 Answers2