Is there an easy way to make EntityFramework use SQL default values?

Question

For example, most of my entities have DateCreated and DateModified fields. The defaults to these are set to GetUtcDate() in SQL Server.

If I try and create an entity and don't set these values, I get an exception saying it can't run the SQL insert because the date value is out of range. Makes sense because C# default dates are 1/1/0001 while SQL Server's minimum date is 1/1/1753.

So is there a way I can tell EF to either use the SQL Server default values, or to NOT try and insert columns which have not been set?

score 12 · Accepted Answer · answered May 11 '11 at 14:10

12

You must set StoreGeneratedPattern for those properties to Identity for DateCreated and Computed for DataModified. It is available in designer. Once you do that you cannot modify those values in your application - only database can set those properties. I wrote about this some article because this feature had bug before VS2010 SP1 but there are reports that it still doesn't work sometimes.

answered May 11 '11 at 14:10

Ladislav Mrnka

360,892
59
660
670

Thanks, I did give that a try before posting this but it wasn't working. I'm using VS 2010 Ultimate, SP1 and EF4 – Rachel May 11 '11 at 14:26
In such case check that correct store generated pattern is set both in CSDL and SSDL part of EDMX file (you must open it as XML) because this is valid way to do that. – Ladislav Mrnka May 11 '11 at 14:31
@Ladislav I am giving this another try, however I am getting errors about a Non-Nullable column getting mapped to a nullable entity property. Do you know what it's talking about? – Rachel May 11 '11 at 14:59
Is any of these properties nullable? – Ladislav Mrnka May 11 '11 at 15:20
No. They are not nullable in the database or in the model – Rachel May 11 '11 at 15:23
@Ladislav I got past the error... not sure what it was but I rebuilt some things and it worked afterwards. Also, thank you - you're right about setting the `StoreGeneratedPattern` and your article contains some useful information. By any chance did you create that blog you were referring to about updating items that used a `StoreGeneratedPattern` of `Computed`? – Rachel May 11 '11 at 15:39
@Rachel: I'm not sure if I tested it with both `Computed` and `Identity` settings. – Ladislav Mrnka May 11 '11 at 15:46
1

@Rachel: In such case Computed will not work as well because neither computed or identity properties are included in insert / update SQL command. You will have to handle default value in the application if you need that. – Ladislav Mrnka May 11 '11 at 15:54
@Ladislav Thanks, its starting to make more sense now :) – Rachel May 11 '11 at 15:59
This saved me a lot of time. Thank you. – WorkSmarter Sep 03 '14 at 21:54

James McCormack · Answer 2 · 2011-05-11T14:30:00.250

One solution is to override your generated entitycontext class by using partial. This will intercept inserts/updates on all the entity classes in your EF context:

public partial class MyEntities : ObjectContext
{
    public override int SaveChanges(SaveOptions options)
    {
        this.DetectChanges();

        foreach (var insert in this.ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Added))
        {
            if (insert.Entity.HasProperty("DateCreated"))
                insert.Entity.GetType().GetProperty("DateCreated").SetValue(insert.Entity, DateTime.UtcNow, null);
            if (insert.Entity.HasProperty("LastModified"))
                insert.Entity.GetType().GetProperty("LastModified").SetValue(insert.Entity, DateTime.UtcNow, null);
        }

        foreach (var update in this.ObjectStateManager.GetObjectStateEntries(System.Data.EntityState.Modified))
        {
            if (update.Entity.HasProperty("LastModified"))
                update.Entity.GetType().GetProperty("LastModified").SetValue(update.Entity, DateTime.UtcNow, null);
        }

        return base.SaveChanges(options);
    }
}

Or do something similar, looking for inserts/updates on your datestamp fields and removing them from the ObjectStateEntries collection?

score 0 · Answer 3 · answered May 11 '11 at 13:54

0

Have you tryed to set the DefaultValue of the Property in the Entity?

answered May 11 '11 at 13:54

JTorrecilla

208
1
4

I'm really hoping to avoid having to go through all my entities and adjusting the default value of every property. Also, it means if I change the default in SQL server I need to change the default in EF and recompile – Rachel May 11 '11 at 14:06

Is there an easy way to make EntityFramework use SQL default values?

3 Answers3

Linked