How to Use Structural Annotations to Set SQL Type to Date in Model First Approach

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

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.

How to define a concurrency property in Model-First

Your expectations are correct but model first default database creation workflow is not. Current model first workflow never creates timestamp columns - it uses just varbinary. To force your expected behavior you must modify the workflow which is not an easy task: here is something related to the topic.

How to integrate my own structural annotations engine in the Entity Framework designer?

Entity framework has very low extensibility on ORM layer but it has surprisingly good extensibility on designer layer. The core of extensibility is based on few interfaces which can be implemented to add custom features to EDMX designer as VS extension.

The step by step process of adding custom property to designer is described in Entity Framework 4 in Action book. You can also use Designer Extension Starter Kit to get some prepared templates and kick start for creating your own extensions.

These features targets EFv4. Extension starter kit doesn't work with June 2011 CTP and its new designer.

Entity Framework Database First POCO t4 generation and validation

In such case you must either give up with POCO generator and create classes manually yourselves - it will allow you to use any attributes you want OR you must modify T4 template to add attributes for you. To modify T4 template you must either use information from metadata to setup correct attributes or you must use structural annotations and manually define attributes in EDMX opened as XML. I described structural annotations here but the example is not related to your problem.

How to add a filestream column in the Entity Framework 4.0 model first designer?

Entity framework doesn't support Filestream. Even if you add it, it will still be used as any other varbinary(max) column. To use Filestream during database generation you must use custom structural annotation and modify generation template.

How to fix the datetime2 out-of-range conversion error using DbContext and SetInitializer?

You have to ensure that Start is greater than or equal to SqlDateTime.MinValue (January 1, 1753) - by default Start equals DateTime.MinValue (January 1, 0001).

---

Related Topics