locked
Entity Framework ddl generator changes xml and timestamp types RRS feed

  • Question

  • Hi!

    I am using xml and timestamp SQL Server data types with Entity Framework. I had to set the xml and the timestamp types by hand in the xml view of the edmx file, and then they appear correctly in the Mappings window in the designer. However, when i generate the DDL script for my model, the xml type is substituted with nvarchar(MAX), and the timestamp type is substituted with varbinary(MAX), and they are changed like this in the edmx model as well (but only when I generate the DDL script).
    Does anybody have any idea why this is happening?

    Thanks,
    DM
    Monday, March 1, 2010 9:20 PM

Answers

  • The DDL generator takes as its input the conceptual model (CSDL.) As there is nothing in the CSDL that tells us that a particular column should be XML or timestamp in the database, what ends up in the SSDL is the straighforward/naive translation of the EDM type to a database type.

    We would like to fix this issue in a future release, and would like to update the Database Generation Power Pack with this capability. For now, you can customize the T4 template that does the DDL generation as follows:

    1. Add a structured annotation to the CSDL, e.g.:

    <Property name="foo" type="String">

    <my:databasetype="XML" edmx:CopyToSSDL="true"/>

    </Property>

    The CopyToSSDL annotation tells the database generation pipeline to copy the annotation to the SSDL.

    2. Modify the T4 DDL generation template used by the model-first workflow to read this annotation and use it to determine the database type, rather than use the built-in logic.

    Lots of examples of customization of the database generation workflow here:

    http://blogs.msdn.com/adonet/archive/2010/02/08/entity-designer-database-generation-power-pack.aspx

    HTH,
     Noam


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, April 8, 2010 5:06 PM
    Moderator

All replies

  • The DDL generator takes as its input the conceptual model (CSDL.) As there is nothing in the CSDL that tells us that a particular column should be XML or timestamp in the database, what ends up in the SSDL is the straighforward/naive translation of the EDM type to a database type.

    We would like to fix this issue in a future release, and would like to update the Database Generation Power Pack with this capability. For now, you can customize the T4 template that does the DDL generation as follows:

    1. Add a structured annotation to the CSDL, e.g.:

    <Property name="foo" type="String">

    <my:databasetype="XML" edmx:CopyToSSDL="true"/>

    </Property>

    The CopyToSSDL annotation tells the database generation pipeline to copy the annotation to the SSDL.

    2. Modify the T4 DDL generation template used by the model-first workflow to read this annotation and use it to determine the database type, rather than use the built-in logic.

    Lots of examples of customization of the database generation workflow here:

    http://blogs.msdn.com/adonet/archive/2010/02/08/entity-designer-database-generation-power-pack.aspx

    HTH,
     Noam


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, April 8, 2010 5:06 PM
    Moderator

  • It's quite useful, Thanks for your analysis! Now I understand more about it.
    Thursday, November 25, 2010 10:32 AM
  • I propose an easier way.

    1) Do the usual stuff required to change SsdlToSql10.tt

    - Copy "C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\SSDLToSQL10.tt" int your project and name it something else like DDLGenerator.tt.

    - Set the "DDL Generation Template" of your model to "DDLGenerator.tt"

    2) Change the following

    <#
        for (int p = 0; p < entitySet.ElementType.Properties.Count; p++)
        {
          EdmProperty prop = entitySet.ElementType.Properties[p];
    #>
      [<#=Id(prop.Name)#>] <#=prop.ToStoreType()#> <#=WriteIdentity(prop, targetVersion)#> <#=WriteNullable(prop.Nullable)#><#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""#>
    <#
        }
    #>
    

    to this:

    <#
        for (int p = 0; p < entitySet.ElementType.Properties.Count; p++)
        {
          EdmProperty prop = entitySet.ElementType.Properties[p];
    			string storeType = prop.ToStoreType();
    			if (prop.Name == "timestamp")
    			{
    				storeType = "timestamp";
    			}
    #>
      [<#=Id(prop.Name)#>] <#=storeType#> <#=WriteIdentity(prop, targetVersion)#> <#=WriteNullable(prop.Nullable)#><#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""#>
    <#
        }
    #>
    

    This will make it so that any field named "timestamp" is created as type timestamp.  The designer will still show type Binary but just ignore that, it works fine.  This is much simpler than adding and maintaining custom properties in the EDMX file.  Yuck.

    Hopefully this will be fixed in the 2011 EF release.  It is really silly that timestamp is not supported in the designer, even though optimistic concurrency is a major feature of EF.

    Thursday, February 17, 2011 10:42 PM