How do I use structural annotations to set SQL type to Date in model first approach

Question

Is it possible to set type to just date (NOT datetime) via entity framework designer?

I had a look around and the only answer that I've found is a post from MSDN forum from a year ago...

http://social.msdn.microsoft.com/Forums/en/adodotnetentityframework/thread/28e45675-f64b-41f0-9f36-03b67cdf2e1b

I'm very new here and I don't really understand the instructions where they talk about structural annotations...

I can go through the generated SQL script and change each line but I rather not do that...

Answer 1

Structural annotation - nice. It is the first time I heard about this feature but it works. I just tried it. I will try to explain it little bit.

Structural annotations are just random xml added to EDMX file. EDMX file is in fact just XML wich has 4 parts - CSDL, MSL, SSDL and part related to positioning elements in the designer.

• CSDL describes entities and associations among entities (defined in the designer)
• SSDL describes tables and relations
• MSL describes mapping between CSDL and SSDL

If you start with model first (you want to generate database from your model), you have only CSDL part and both SSDL and MSL will be generated by some automatic process (T4 templates executed in workflow) once SSDL is created another T4 template will generate SQL script for database creation.

Structural annotation described in linked MSDN forum's thread is a hint. You will place structural annotation into CSDL part of the EDMX (you must open EDMX as XML - click on the file in solution explorer and choose Open with). My test CSDL describes single User entity with three properties (entity is visible on screenshot later in the answer):

<!-- CSDL content -->
<edmx:ConceptualModels>
  <Schema xmlns="http://schemas.microsoft.com/ado/2008/09/edm" 
          xmlns:cg="http://schemas.microsoft.com/ado/2006/04/codegeneration" 
          xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator"
          xmlns:annotation="http://schemas.microsoft.com/ado/2009/02/edm/annotation"
          xmlns:custom="http://tempuri.org/custom"
          Namespace="Model" Alias="Self" >
    <EntityContainer Name="ModelContainer" annotation:LazyLoadingEnabled="true">
      <EntitySet Name="UsersSet" EntityType="Model.User" />
    </EntityContainer>
    <EntityType Name="User">
      <Key>
        <PropertyRef Name="Id" />
      </Key>
      <Property Type="Int32" Name="Id" Nullable="false" annotation:StoreGeneratedPattern="Identity" />
      <Property Type="String" Name="Login" Nullable="false" />
      <Property Type="DateTime" Name="CreatedAt" Nullable="false">
        <custom:SqlType edmx:CopyToSSDL="true">Date</custom:SqlType>
      </Property>
    </EntityType>
  </Schema>
</edmx:ConceptualModels>

I have added custom namespace definition in Schema element: xmlns:custom="http://tempuri.org/custom" and defined custom structural annotation for CreatedAt property:

<Property Type="DateTime" Name="CreatedAt" Nullable="false">
   <custom:SqlType edmx:CopyToSSDL="true">Date</custom:SqlType>
</Property>

The name of the namespace or element used for structural annotation are not important - it is absolutely up to you what names do you use. The only important thing is edmx:CopyToSSDL="true" attribute. This attribute is recognized by T4 template used for SSDL creation and it just takes this element and places it to SSDL. Generated SSDL looks like:

<Schema Namespace="Model.Store" Alias="Self" 
        Provider="System.Data.SqlClient" ProviderManifestToken="2008" 
        xmlns:store="http://schemas.microsoft.com/ado/2007/12/edm/EntityStoreSchemaGenerator" 
        xmlns="http://schemas.microsoft.com/ado/2009/02/edm/ssdl">
  <EntityContainer Name="ModelStoreContainer">
    <EntitySet Name="UsersSet" EntityType="Model.Store.UsersSet" store:Type="Tables" Schema="dbo" />
  </EntityContainer>
  <EntityType Name="UsersSet">
    <Key>
      <PropertyRef Name="Id" />
    </Key>
    <Property Name="Id" Type="int" StoreGeneratedPattern="Identity" Nullable="false" />
    <Property Name="Login" Type="nvarchar(max)" Nullable="false" />
    <Property Name="CreatedAt" Type="datetime" Nullable="false">
      <custom:SqlType xmlns:custom="http://tempuri.org/custom">Date</custom:SqlType>
    </Property>
  </EntityType>
</Schema>

The only point was moving the structural annotation to SSDL. All annotations are accessible in metadata through some name value collection. Now you need to modify T4 template responsible for SQL script generation to recognize this annotation and use the value defined in the annotation instead of type defined in the property. You can find the template in:

C:\Program Files (x86)\Microsoft Visual Studio 10.0\Common7\IDE\Extensions\Microsoft\Entity Framework Tools\DBGen\SSDLToSQL10.tt

Copy template file to new location (so that you don't modify the original one) and replace default table creation with this:

-- Creating table '<#=tableName#>'
CREATE TABLE <# if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=tableName#>] (
<#
        for (int p = 0; p < entitySet.ElementType.Properties.Count; p++)
        {
            EdmProperty prop = entitySet.ElementType.Properties[p];
#>
    [<#=Id(prop.Name)#>] <#
            if (prop.MetadataProperties.Contains("http://tempuri.org/custom:SqlType"))
            {
                MetadataProperty annotationProperty = prop.MetadataProperties["http://tempuri.org/custom:SqlType"];
                XElement e = XElement.Parse(annotationProperty.Value.ToString());
                string value = e.Value.Trim();
    #>
    <#=value#> <# } else { #> <#=prop.ToStoreType()#> <# } #> <#=WriteIdentity(prop, targetVersion)#> <#=WriteNullable(prop.Nullable)#><#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""#>
<#
        }
#>
);
GO

Now the last point is changing the template used for SQL script generation. Open EDMX file in the designer and go to model's properties (just click somewhere in the designer while you have properties window opened). Change DDL Generation Template to the template you modified.

Run Generate Database from Model and it will create SQL script containing:

-- Creating table 'UsersSet'
CREATE TABLE [dbo].[UsersSet] (
    [Id]  int  IDENTITY(1,1) NOT NULL,
    [Login]  nvarchar(max)   NOT NULL,
    [CreatedAt]     Date   NOT NULL
);
GO

This is probably the most advanced and hidden feature of EDMX I have seen yet. Annotations together with custom T4 templates can get you a lot of control over both class and SQL generation. I can imagine using this to define for example database indexes or unique keys when using model first or add selectively some custom attributes to generated POCO classes.

The reason why this is so hidden is that there is no tooling support in VS out-of-the box to use this.