none
datetime2

    Question

  • I used model first approach on my project and I let the EF 4.1 generate database for my classes. I'm having problem when it comes to datetime datatype on SQL 2008. The EF 4.1 generate "datetime" as datafield type for the DateTime? property type of my classes. Now when I insert data, I'm getting "The conversion of a datetime2 data type to a datetime data type resulted in an out-of-range value". I can fix this by manually modifying the "datetime" datafield to "datetime2" but it will requires me to do this for almost all tables. Is there a way to force the EF 4.1 to use the datetime2 on SQL during the generation of my tables for my classes on database. 
    Friday, April 22, 2011 8:08 AM

Answers

  • Hi!

    The generation of the SQL is actually done by the T4 template system. Which T4 that is used while executing "Generate Database from model" is defined in your edmx in the "DDL Generation Template" property.

    These are fully customizable, and you can create your own and use them. What you could do is to create your own copy of the file SSDLToSQL10.tt in the %ProgramFiles%\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen folder, and do the following modifications.

    1. First add the following code in the bottom of your .tt file:

    <#+
    private static string WriteMSSQLType(EdmProperty property)
    {
     if(property.TypeUsage.EdmType.Name.Equals("DateTime", StringComparison.OrdinalIgnoreCase) == true)
     return "datetime2";
     
     return property.ToStoreType();
    }
    #>
    

     2. On line 165 replace prop.ToStoreType() with WriteMSSQLType(prop) like this:

     [<#=Id(prop.Name)#>] <#=WriteMSSQLType(prop)#> <#=WriteIdentity(prop, targetVersion)#> <#=WriteNullable(prop.Nullable)#><#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""#>
    

    3. change the "DDL Generation Template" property in your edmx model and generate again

    Now, all datetimes will be datetime2.

    Hope this helps!


    --Rune

    Friday, April 22, 2011 1:23 PM
  • Will this work on POCO classes? I don't use edmx model and I only use POCO classes on my project. I used combination of DataAnnotation and Fluent API for my mapping and relationship.

    I tried this but there's no effect on the generated database. Not sure if I missing something or this is only working on EDMX.

    Currently I'm using  Fluent API and it works : Property(a => a.CreateDate).HasColumnType("datetime2"), the generated datafield is now showing datatime2.

     

     

    Friday, April 22, 2011 3:24 PM

All replies

  • Hi!

    The generation of the SQL is actually done by the T4 template system. Which T4 that is used while executing "Generate Database from model" is defined in your edmx in the "DDL Generation Template" property.

    These are fully customizable, and you can create your own and use them. What you could do is to create your own copy of the file SSDLToSQL10.tt in the %ProgramFiles%\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen folder, and do the following modifications.

    1. First add the following code in the bottom of your .tt file:

    <#+
    private static string WriteMSSQLType(EdmProperty property)
    {
     if(property.TypeUsage.EdmType.Name.Equals("DateTime", StringComparison.OrdinalIgnoreCase) == true)
     return "datetime2";
     
     return property.ToStoreType();
    }
    #>
    

     2. On line 165 replace prop.ToStoreType() with WriteMSSQLType(prop) like this:

     [<#=Id(prop.Name)#>] <#=WriteMSSQLType(prop)#> <#=WriteIdentity(prop, targetVersion)#> <#=WriteNullable(prop.Nullable)#><#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""#>
    

    3. change the "DDL Generation Template" property in your edmx model and generate again

    Now, all datetimes will be datetime2.

    Hope this helps!


    --Rune

    Friday, April 22, 2011 1:23 PM
  • Will this work on POCO classes? I don't use edmx model and I only use POCO classes on my project. I used combination of DataAnnotation and Fluent API for my mapping and relationship.

    I tried this but there's no effect on the generated database. Not sure if I missing something or this is only working on EDMX.

    Currently I'm using  Fluent API and it works : Property(a => a.CreateDate).HasColumnType("datetime2"), the generated datafield is now showing datatime2.

     

     

    Friday, April 22, 2011 3:24 PM
  • Hi,

    You are using Code-First, not Model-First as you wrote in your original post.

    Sorry, I haven't been able to check out Code-First yet, so I can't help you with that. My suggestion above works for Model-First approach (that is, you design your datamodel in a edmx file)


    --Rune
    Friday, April 22, 2011 3:38 PM
  • Sorry for the misleading but I though Code-First is just a name for the new features on 4.1. Code-First support 3 different approaches: Database First, Model First, and Code First. It's the reason I used the "model first approach" on my original post as defined on the article:

    http://blogs.msdn.com/b/adonet/archive/2011/03/07/when-is-code-first-not-code-first.aspx

     

    -- Francis


    Friday, April 22, 2011 3:54 PM
  • Actually I should use the "Code First generating a database" which is better define my approach. :). Your right the model first definition is using EF  designer and then generate database.
    Friday, April 22, 2011 3:59 PM
  • I'm facing this problem in Model First design (regardless of what the orginal author actually meant), and the various issues with datetime2 problems confuse me a bit.

    In many other threads the problem for developers seems to be the opposite, getting datetime2 when they want datetime. Solutions are written that suggests ProviderManifestToken="2005" gives datetime, while ProviderManifestToken="2008" gives datetime2 behaviour.

    I want datetime2 and I have ProviderManifestToken="2008", but still only get datetime.
    What gives? Is tweaking the T4 (or the EDMX-file in a text editor) the only ways of accomplishing datetime2 behaviour in Model First? That's not a nice way of adding precision datetime properties to your EF model...

    Regards
    Anders

    Monday, November 07, 2011 4:54 PM
  • Any news on the topic? Now that EF6 is out the behavior is still the same -- datetime instead of datetime2.

    CTRL + H ("datetime " -> "datetime2 ") is the only solution atm...

    Tuesday, January 07, 2014 8:31 PM