CODEWITHSUNDEEP
c#sql-serverentity-frameworksql-server-ce
jordanbtucker
jordanbtucker

Reputation: 6088

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

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.)

Upvotes: 3

Views: 5314

Answers (2)

jordanbtucker
jordanbtucker

Reputation: 6088

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.

Upvotes: 2

ssis_ssiSucks
ssis_ssiSucks

Reputation: 1506

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

Upvotes: 1

Related Questions