How to use default DateTime values with SQL Server CE and Entity Framework

Question

For simplicity's sake, let's say I have a SQL Server CE table called Widgets:

create table [Widgets] (
  [Id] int not null primary key,
  [Created] datetime not null default getdate())

Inserting without specifying a value for the Created column works as expected:

insert into [Widgets] ([Id]) values (1)

After generating an Entity Framework model called Database, I write following code:

Database db = new Database();
db.Widgets.AddObject(new Widget { Id = 2 });
db.SaveChanges();

But it raises an exception: An overflow occurred while converting to datetime. I tracked this down to the fact that EF sees the Created column as a non-nullable DateTime, and so when a new Widget is constructed, its Created property is set to the default DateTime (0001-01-01 12:00:00), which is outside of the valid range for SQL Server CE.

So, how do I get the above sample of C# to work? I'd prefer not to change the database schema. And I know I could set the Created property to DateTime.Now in the Widget constructor, but it may take a few minutes from the time a Widget is constructed until its actually inserted into the database, which is that actual time I want to capture.

Update: I tried setting StoreGeneratedPattern to Identity and Computed, but it raises an exception: Server-generated keys and server-generated values are not supported by SQL Server Compact. Default values do work in SQL Server CE, so it must be a limitation of Entity Framework. (Apparently this is fixed in SQL Server CE 4.0.)

Possible duplicate http://stackoverflow.com/questions/6846952/disabling-entity-frameworks-default-value-generation-code-first — Tony Hopkinson, Apr 26 '12 at 23:31
@TonyHopkinson The answer to that question does not satisfy my question. As stated in the last paragraph, setting the default value in the constructor does not invoke the proper behavior. The time at which the Widget is constructed may be minutes away from the time it's actually inserted into the database. — jordanbtucker, Apr 26 '12 at 23:38
Related question: http://stackoverflow.com/questions/6237619/ef-4-1-how-to-add-a-default-on-insertion-for-datetime-column — devuxer, Apr 27 '12 at 00:35
Philosophically speaking, when is an entity really created? When a new entity gets saved to the database, or when the entity is created in code? Does it matter? Does the end user care? Does it assist with database administration somehow? In my opinion, the moment the user clicks "Submit" or "Save" should be the moment the created date is set. If you can live with this, the solution in the above link should work. — devuxer, Apr 27 '12 at 00:39
@Jordan. There were several other answers on there I believe. It also says EF doesn't support the behaviour you want, though I've no idea whether that's right. As far as the time goes, depends on what you are using time created for. — Tony Hopkinson, Apr 27 '12 at 01:06
@DanM I like the philosophical question. :) In my case, it does matter. The Updated column should really be a timestamp column, but SQL Server CE doesn't support that the way full SQL Server does. And the related question is interesting. I'll look into change tracking. Thanks. — jordanbtucker, Apr 27 '12 at 02:03
@TonyHopkinson I checked all of the answers and none of them work/apply. Thanks though. — jordanbtucker, Apr 27 '12 at 02:04
@DanM: "Does it raise too many questions?" ;) Sorry, couldn't help. — Andriy M, Apr 27 '12 at 10:12

jordanbtucker · Accepted Answer · 2014-10-08T17:35:28.167

Looks like DanM's related question link pointed me in the right direction toward change tracking. The following code will get pretty close to the behavior I'm looking for:

public partial class Database
{
  public override int SaveChanges(SaveOptions options)
  {
    foreach(var w in this.ObjectStateManager
                         .GetObjectStateEntries(EntityState.Added)
                         .Where(e => e.Entity is Widget)
                         .Select(e => (Widget)e.Entity))
    {
      if(w.Created == default(DateTime))
        w.Created = DateTime.Now;
    }

    return base.SaveChanges(options);
  }
}

All of the Widgets' Created values will be set to the current date and time. The only other issues I see is that the value is set according to the client's clock, not the server's. And if you're inserting many rows at once, the timestamps will be a little ahead of what they would be with a normal insert.

The best solution would allow SQL Server to do what it's configured to do—set the value of the column if not provided. C'est la vie.

score 1 · Answer 2 · answered Apr 27 '12 at 04:00

1

You could set the Created property of the Widget partial class in its constructor:

public partial class Widget
    {
        public Widget()
        {
            this.Created = System.DateTime.Now;
        }
    }

answered Apr 27 '12 at 04:00

ssis_ssiSucks

1,476
1
12
11

While this may work for some implementations, it won't work for this one. Like I said in the OP and in a few comments already: The time at which the Widget is constructed may be minutes away from the time it's actually inserted into the database. – jordanbtucker Apr 27 '12 at 15:15

How to use default DateTime values with SQL Server CE and Entity Framework

2 Answers2