Default valued DateTime column, mapping with EF4

Question

In my database there's a datetime column "Created" with default value getutcdate(). I would like my EF datacontext to generate an insert query that doesn't set this column, and fetch the resulting value. Is there a way to do this?

I tried setting StoreGeneratedPattern to either None, Identity or Computed, I get an exception that DateTime can't be converted to datetime2 (meaning it's trying to insert 0:00 at 0-0-0)

How do I set up my edmx to allow creating objects without specifying the initial Created value?

@uhleeka unfortunately I didn't. I've built generic code that checks wether modified and/or added objects implement an interface, and if they do set created and/or modified. I can add that as an answer to this question, but it's more of a work around. I wonder why the given answer doesn't work for me. — Sander Rijken, May 22 '10 at 21:21
after more digging, it turns out that by (inept) design, setting the StoreGeneratedPattern modifies the CDSL, but not the SSDL. So you have to manually edit the edmx xml and set your SSDL StoreGeneratedPattern attributes to identity or computed. But whenever you update your edmx via the designer, i assume your changes will get blown away. https://connect.microsoft.com/VisualStudio/feedback/details/505178/storegeneratedpattern-property-in-ado-net-entity-model-designer-sets-cdsl-annotation-but-not-ssdl-attribute — uhleeka, Jun 02 '10 at 23:33

score 0 · Answer 1 · edited May 23 '17 at 12:07

Worked on my machine. Just tried this out, added a property to my model called "Property" ( default name ), set the column to be Nullable: false, StoreGeneratedPattern: computed:

Here is the generated sql for the example I just tried:

insert [dbo].[Foods]
      ([CommonName],
       [ScientificName],
       [ShortDescription],
       [CategoryId],
       [LongDescription])
values('sdfg' /* @0 */,
       'sdfg' /* @1 */,
       'sdfg' /* @2 */,
       1 /* @3 */,
       'sdfg' /* @4 */)
select [Id],
       [Property]
from   [dbo].[Foods]
where  @@ROWCOUNT > 0
       and [Id] = scope_identity()

EF4 even knew how to read the Property property back after inserting.

Edit: Do you have this problem: The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value

I knew the exception your getting sounded wrong. You should be getting a different error for having a datetime string fall below the minimum that the sql date format allows. Looks like your mappings are just mapping to the wrong schema.

I do have the problem described in your Edit: indeed. I'll try Nullable: false, StoreGeneratedPattern: computed once again. — Sander Rijken, Dec 16 '09 at 10:54
@jfar, did you test this using EF1 or using the EF that comes with VS2010/.NET4? I have the property set up as "Nullable: false, StoreGeneratedPattern: computed", and in the DB as NOT NULL and default value/binding getutcdate() — Sander Rijken, Dec 17 '09 at 13:39

Default valued DateTime column, mapping with EF4

1 Answers1