Entity Framework 4 and SYSUTCDATETIME ()

Question

I have a Table in my DataBase (MS SQL 2008) with a default value for a column SYSUTCDATETIME ().

The Idea is to automatically add Date and Time as soon as a new record is Created.

I create my Conceptual Model using EF4, and I have created an ASP.PAGE with a DetailsView Control in INSERT MODE.

My problems:

When I create a new Record. EF is not able to insert the actual Date and Time value but it inserts instead this value 0001-01-01 00:00:00.00.

I suppose the EF is not able to use SYSUTCDATETIME () defined in my DataBase

Any idea how to solve it? Thanks

Here my SQL script

    CREATE TABLE dbo.CmsAdvertisers
(
    AdvertiserId            int             NOT NULL    IDENTITY
        CONSTRAINT PK_CmsAdvertisers_AdvertiserId PRIMARY KEY,
    DateCreated             dateTime2(2)    NOT NULL
        CONSTRAINT DF_CmsAdvertisers_DateCreated DEFAULT sysutcdatetime (),
    ReferenceAdvertiser     varchar(64)     NOT NULL,
    NoteInternal            nvarchar(256)   NOT NULL
        CONSTRAINT DF_CmsAdvertisers_NoteInternal DEFAULT ''
);

My Temporary solution: Please guys help me on this

e.Values["DateCreated"] = DateTime.UtcNow;

More info here:

http://msdn.microsoft.com/en-us/library/bb387157.aspx

How to use the default Entity Framework and default date values

http://msdn.microsoft.com/en-us/library/dd296755.aspx

score 1 · Accepted Answer · answered Feb 21 '11 at 15:26

The problem is that EF doesn't know about that default value so it sends .NET default value to database. It is possible to force EF to use default value from DB but you must manually modify EDMX file (XML not designer). You must find column definition in SSDL part of EDMX and add StoreGeneratedPattern="Computed" attribute. You also must the same in CSDL part (this can be done in designer).

This setting will have several problems. First of all each update from database will delete your changes in SSDL. Also each insert and update will trigger select which will requery value from this column (because EF thinks that it is computed every time).

Exactly what I thought so I keep simple and use DateTime.UtcNow. My only question is: If a User change the Date-Time for his SO, could be effect the DateTime value pass to EF? Thanks — GibboK, Feb 22 '11 at 11:41

score 1 · Answer 2 · answered Feb 21 '11 at 15:26

In your ASP .NET page when you create the record are you actually adding a value for the DateCreated value. I see that you are doing that now, which is what you needed to be doing in the first place.

So I would guess what was happening earlier is that you were creating your new record but not setting a value for the DateCreated Field. But since a DateTime has a default value of 0001-01-01 00:00:00.00 is why you were getting that behavior.

The default value would only work for the case where you insert a row but provide no indication of what to do with the DateCreated field. But the way that EF works it is always going to provide a value for this (unless you write your own insert query to override the generated ones).

You will also be potentially changing the value any time that you update the record as EF won't know what to do with it.

Entity Framework 4 and SYSUTCDATETIME ()

2 Answers2