如何在模型第一种方法中使用结构注释来将SQL类型设置为Date [英] How do I use structural annotations to set SQL type to Date in model first approach

问题描述

可以通过实体框架设计器将类型设置为仅日期（NOT datetime）吗？

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

我看了一眼，唯一的答案是我从一年前的MSDN论坛发帖...

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...

我可以通过生成的SQL脚本来改变每一行，但我宁愿不这样做...

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

推荐答案

结构注释 - 不错。这是我第一次听说这个功能，但它是有效的。我刚刚试过我会尝试解释一下。

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.

结构注释只是将随机的xml添加到EDMX文件中。 EDMX文件实际上只是XML，它有4个部分 - CSDL，MSL，SSDL和与设计器中定位元素相关的部分。

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描述实体之间的实体和关联（在设计器中定义）


• SSDL描述表和关系


• MSL描述CSDL和SSDL之间的映射

如果您首先从模型开始（您要从模型生成数据库），则只有CSDL部分，并且SSDL和MSL都将由一旦SSDL被创建，一些自动进程（T4模板在工作流中执行）另一个T4模板将生成用于创建数据库的SQL脚本。

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.

链接的MSDN论坛的线程中描述的结构注释是暗示。您将结构注释放在EDMX的CSDL部分（您必须打开EDMX作为XML - 单击解决方案资源管理器中的文件，然后选择打开）。我的测试CSDL描述了具有三个属性的单个用户实体（实体在答案后面的截图中可见）：

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>

我已经在 Schema中添加了自定义命名空间定义元素： xmlns：custom =http://tempuri.org/custom并定义了 CreatedAt 属性：

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>

用于结构注释的命名空间或元素的名称不重要 - 绝对取决于您你使用什么名字？唯一重要的是 edmx：CopyToSSDL =true属性。此属性由用于SSDL创建的T4模板识别，它只需要此元素并将其放置到SSDL。生成的SSDL看起来像：

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>

唯一的一点是将结构注释移动到SSDL。所有注释都可以通过某些名称值集合在元数据中访问。现在，您需要修改负责SQL脚本生成的T4模板，以识别此注释，并使用注释中定义的值，而不是属性中定义的类型。您可以在以下位置找到模板：

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

现在最后一点是更改用于生成SQL脚本的模板。在设计器中打开EDMX文件，并转到模型的属性（只需单击设计器中某处，同时打开属性窗口）。将DDL生成模板更改为您修改的模板。

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.

运行从模型生成数据库，它将创建包含以下内容的SQL脚本：

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

这可能是我看过的EDMX最先进和隐藏的功能。注释与自定义T4模板一起可以让您对类和SQL生成都有很大的控制。我可以想象使用这个来定义例如使用模型时的数据库索引或唯一键，或者选择性地添加一些自定义属性来生成POCO类。

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.

这是什么原因隐藏的是在VS开箱即用的情况下没有工具支持。

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

这篇关于如何在模型第一种方法中使用结构注释来将SQL类型设置为Date的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！