Entity Framwork 4 – Model First und die Dokumentation mit sp_addextendedproperty auf dem SQL Server

Letzte Woche bekam ich eine interessante Frage gestellt: Ist es möglich die Dokumentation aus dem Entity Data Model auf dem SQL-Server mithilfe der Extended Property - Prozeduren zu übertragen.

Interessant an der Frage fand ich, dass es wirklich Entwickler gibt, die sich Gedanken darüber machen pragmatisch zu dokumentieren und nicht mit dem Argument: "Guter Code braucht keine Dokumentation" um sich schlagen.

Bei der DB First-Variante können die Huagati Tools mit den MS_Descriptions auf dem SQL Server umgehen und diese in das Entity Data Model einarbeiten. Beim Model First-Ansatz wird jedoch nur ein DDL-Skript erstellt, dass die Dokumentation nicht auf das Datenmodell überträgt. Es ist aber möglich dies mithilfe der Prozeduren durch eine angepasste T4-Vorlage zu generieren.

Dazu muss man sich eine eigene DDL-Vorlage anlegen. Die Vorgehensweise dazu habe ich bereits hier beschrieben. Als Vorlage dient die bereits vorhandene DDL T4-Vorlage, die entsprechend angepasst und erweitert werden muss.

Im Standard sind die Funktionen für das Store-Schema vorhanden, aus denen das SQL für die Datenbank erstellt wird. Es stehen also nicht die Entities aus dem konzeptionellen Modell zur Verfügung. Das Problem dabei ist, die Dokumentation befindet sich im konzeptionellen Modell.

: Abbildung 1 Zeigt die Dokumentation der Id-Eigenschaft im konzeptionellen Modell, welche als Grundlage für die Dokumentation auf dem SQL Server dienen soll

Aus diesem Grund muss die Include-Datei EF.Utility.CS.ttinclude und der entsprechende Initialcode hinzugefügt werden. Damit wird ein Mapping von den EntitySets auf die Entities möglich und der Zugriff auf die Dokumentation ist gewährleistet.


// required for documentation
MetadataLoader loader = new MetadataLoader(this);
MetadataTools ef = new MetadataTools(this);
var ItemCollection = loader.CreateEdmItemCollection(edmxPath, new string[] {});
EntityContainer container = ItemCollection.GetItems<EntityContainer>().FirstOrDefault();
// ----

Für die Dokumentation muss in der T4-Vorlage der notwendige Prozedur-Code mit den Kommentaren hinzugefügt werden, der durch das Mapping über die EntitySets erreichbar wird.


this.WriteLine("-- EDM Dokumentation");
// EDM-Dokumentation der Entity auf SQL-Server übertragen
		
// get entity from entity set
string entityType = (from c in container.BaseEntitySets
					where c.Name == entitySet.Name
					select c.ElementType.Name).FirstOrDefault();

EntityType entity = (from e in ItemCollection.GetItems<EntityType>()
					where e.Name == entityType
					select e).FirstOrDefault();

string tableDoku = String.Empty;
if (entity.Documentation != null && 
	entity.Documentation.Summary != null)
{
	tableDoku = entity.Documentation.Summary;
}
	
string propertyProcedure = @"
	exec sp_addextendedproperty N'MS_Description', '{0}', N'user', N'dbo', 
		N'table', N'{1}'
	GO";

if (String.IsNullOrEmpty(tableDoku) == false)
	this.WriteLine(propertyProcedure, tableDoku, tableName);
	
foreach (EdmProperty item in entity.Properties)
{
	if (item.Documentation != null && item.Documentation.Summary != null)
	{
		string columnDoku = item.Documentation.Summary;
		string columnPropertyProcedure = @"exec sp_addextendedproperty N'MS_Description', '{0}', N'user', 
				N'dbo', N'table', N'{1}', N'column', N'{2}'
				GO";
		this.WriteLine(columnPropertyProcedure, columnDoku, tableName, item.Name);
	}
}

this.WriteLine("-- End documentation");

Zusammengefasst hat die Vorlage folgenden Inhalt:


<# 
//---------------------------------------------------------------------
// <copyright file="SsdlToSql10.tt" company="Microsoft">
//      Copyright (c) Microsoft Corporation.  All rights reserved.
// </copyright>
//---------------------------------------------------------------------
// This T4 template generates T-SQL from an instance of 
// System.Data.Metadata.Edm.StoreItemCollection, an object representation
// of the SSDL. This T-SQL is compatible with SQL 2008, 2005, CE, and Azure databases.
//---------------------------------------------------------------------
// Note: We will resolve all paths in assembly directives at runtime, taking 
// macros into account (e.g. $(DevEnvDir), $(ProjectDir), etc.)
#>
<#@ assembly name="System.Core" #>
<#@ assembly name="System.Data.Entity" #>
<#@ assembly name="System.Data.Entity.Design" #>
<#@ assembly name="$(DevEnvDir)Microsoft.Data.Entity.Design.DatabaseGeneration.dll"#>
<#@ import namespace="System.Linq" #>
<#@ import namespace="System.Text" #>
<#@ import namespace="System.Collections.Generic" #>
<#@ import namespace="System.Data.Entity.Design" #>
<#@ import namespace="System.Data.Metadata.Edm" #>
<#@ import namespace="Microsoft.Data.Entity.Design.DatabaseGeneration" #>
<#@ import namespace="System.Runtime.Remoting.Messaging" #>
<#@ import namespace="System.Text.RegularExpressions" #>
<#@ template language="C#" debug="true" hostspecific="true" #>
<#@ include file="GenerateTSQL.Utility.ttinclude"#>
<#@ include file="EF.Utility.CS.ttinclude"#> -- Dokumentation
<#@ output extension = ".sql" #>
<#

// +++++++++++++++++++++++++++++++++++++++++++++++++
// Setup for the template (initializing variables, etc.)
// +++++++++++++++++++++++++++++++++++++++++++++++++

    string databaseName = this.GetInput<string>(EdmParameterBag.ParameterName.DatabaseName.ToString());
    string edmxPath = this.GetInput<string>(EdmParameterBag.ParameterName.EdmxPath.ToString());
    Version targetVersion = this.GetInput<Version>(EdmParameterBag.ParameterName.TargetVersion.ToString());
 
	// required for documentation
	MetadataLoader loader = new MetadataLoader(this);
	MetadataTools ef = new MetadataTools(this);
	var ItemCollection = loader.CreateEdmItemCollection(edmxPath, new string[] {});
	EntityContainer container = ItemCollection.GetItems<EntityContainer>().FirstOrDefault();
	// ----
	
    if (false == InitializeAndValidateExistingStore()) 
    {
#>
-- Warning: There were errors validating the existing SSDL. Drop statements
-- will not be generated.
<#
    }
#>
-- --------------------------------------------------
<#
    if (this.IsSQLCE) {
#>
-- Entity Designer DDL Script for SQL Server Compact Edition
<#
    } else {
#>
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
<#
    }
#>
-- --------------------------------------------------
-- Date Created: <#=DateTime.Now#>
<#
    if (!String.IsNullOrEmpty(edmxPath))
    {
#>
-- Generated from EDMX file: <#=Id(edmxPath)#>
<#
    }
#>
-- --------------------------------------------------

<#  if (!this.IsSQLCE) 
    { 
#>
SET QUOTED_IDENTIFIER OFF;
GO
<#  if (!String.IsNullOrEmpty(databaseName))
    {
#>
USE [<#=Id(databaseName)#>];
GO
<#
    }
    foreach (string unescapedSchemaName in (from es in Store.GetAllEntitySets() select es.GetSchemaName()).Distinct())
    {
#>
IF SCHEMA_ID(N'<#=Lit(unescapedSchemaName)#>') IS NULL EXECUTE(N'CREATE SCHEMA [<#=Id(unescapedSchemaName)#>]');
<#
    }
#>
GO
<#  } #>

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
<#  if (this.IsSQLCE)
    {
#>
-- NOTE: if the constraint does not exist, an ignorable error will be reported.
<#  } #>
-- --------------------------------------------------

<#
    foreach (AssociationSet associationSet in ExistingStore.GetAllAssociationSets())
    {
        ReferentialConstraint constraint = associationSet.ElementType.ReferentialConstraints.Single();
        string constraintName = Id(WriteFKConstraintName(constraint));
        AssociationSetEnd dependentSetEnd = associationSet.AssociationSetEnds.Where(ase => ase.CorrespondingAssociationEndMember == constraint.ToRole).Single();
        string schemaName = Id(dependentSetEnd.EntitySet.GetSchemaName());
        string dependentTableName = Id(dependentSetEnd.EntitySet.GetTableName());
        
        if (!this.IsSQLCE)
        {
#>
IF OBJECT_ID(N'[<#=Lit(schemaName)#>].[<#=Lit(constraintName)#>]', 'F') IS NOT NULL
<#      } #>
    ALTER TABLE <# if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=dependentTableName#>] DROP CONSTRAINT [<#=constraintName#>];
GO
<#
    }
#>

-- --------------------------------------------------
-- Dropping existing tables
<#  if (this.IsSQLCE)
    {
#>
-- NOTE: if the table does not exist, an ignorable error will be reported.
<#  } #>
-- --------------------------------------------------

<#
    foreach (EntitySet entitySet in ExistingStore.GetAllEntitySets())
    { 
        string schemaName = Id(entitySet.GetSchemaName());
        string tableName = Id(entitySet.GetTableName());
        
        if (!this.IsSQLCE)
        {
#>
IF OBJECT_ID(N'[<#=Lit(schemaName)#>].[<#=Lit(tableName)#>]', 'U') IS NOT NULL
<#      } #>
    DROP TABLE <# if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=tableName#>];
GO
<#
    }
#>

-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

<#

    foreach (EntitySet entitySet in Store.GetAllEntitySets())
    {
		
        string schemaName = Id(entitySet.GetSchemaName());
        string tableName = Id(entitySet.GetTableName());
#>
-- 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)#>] <#=prop.ToStoreType()#> <#=WriteIdentity(prop, targetVersion)#> <#=WriteNullable(prop.Nullable)#><#=(p < entitySet.ElementType.Properties.Count - 1) ? "," : ""#>
<#
        }
	
#>
);
GO

<#
this.WriteLine("-- EDM Dokumentation");
// EDM-Dokumentation der Entity auf SQL-Server übertragen
		
// get entity from entity set
string entityType = (from c in container.BaseEntitySets
					where c.Name == entitySet.Name
					select c.ElementType.Name).FirstOrDefault();

EntityType entity = (from e in ItemCollection.GetItems<EntityType>()
					where e.Name == entityType
					select e).FirstOrDefault();

string tableDoku = String.Empty;
if (entity.Documentation != null && 
	entity.Documentation.Summary != null)
{
	tableDoku = entity.Documentation.Summary;
}
	
string propertyProcedure = @"
	exec sp_addextendedproperty N'MS_Description', '{0}', N'user', N'dbo', 
		N'table', N'{1}'
	GO";

if (String.IsNullOrEmpty(tableDoku) == false)
	this.WriteLine(propertyProcedure, tableDoku, tableName);
	
foreach (EdmProperty item in entity.Properties)
{
	if (item.Documentation != null && item.Documentation.Summary != null)
	{
		string columnDoku = item.Documentation.Summary;
		string columnPropertyProcedure = @"exec sp_addextendedproperty N'MS_Description', '{0}', N'user', 
				N'dbo', N'table', N'{1}', N'column', N'{2}'
				GO";
		this.WriteLine(columnPropertyProcedure, columnDoku, tableName, item.Name);
	}
}

this.WriteLine("-- End documentation");
    } 
#>
-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

<#
    foreach (EntitySet entitySet in Store.GetAllEntitySets())
    {
        string schemaName = Id(entitySet.GetSchemaName());
        string tableName = Id(entitySet.GetTableName());
#>
-- Creating primary key on <#=WriteColumns(entitySet.ElementType.GetKeyProperties(), ',')#> in table '<#=tableName#>'
ALTER TABLE <# if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=tableName#>]
ADD CONSTRAINT [PK_<#=tableName#>]
    PRIMARY KEY <# if (!IsSQLCE) {#><#=WriteClustered(Store, entitySet.ElementType)#> <#}#>(<#=WriteColumns(entitySet.ElementType.GetKeyProperties(), ',')#> <# if (!IsSQLCE) {#>ASC<#}#>);
GO

<#
    }
#>
-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

<#
    foreach (AssociationSet associationSet in Store.GetAllAssociationSets())
    {
        ReferentialConstraint constraint = associationSet.ElementType.ReferentialConstraints.Single();
        AssociationSetEnd dependentSetEnd = associationSet.AssociationSetEnds.Where(ase => ase.CorrespondingAssociationEndMember == constraint.ToRole).Single();
        AssociationSetEnd principalSetEnd = associationSet.AssociationSetEnds.Where(ase => ase.CorrespondingAssociationEndMember == constraint.FromRole).Single();
        string schemaName = Id(dependentSetEnd.EntitySet.GetSchemaName());
        string dependentTableName = Id(dependentSetEnd.EntitySet.GetTableName());
        string principalTableName = Id(principalSetEnd.EntitySet.GetTableName());
#>
-- Creating foreign key on <#=WriteColumns(constraint.ToProperties, ',')#> in table '<#=dependentTableName#>'
ALTER TABLE <#if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=dependentTableName#>]
ADD CONSTRAINT [<#=WriteFKConstraintName(constraint)#>]
    FOREIGN KEY (<#=WriteColumns(constraint.ToProperties, ',')#>)
    REFERENCES <# if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=principalTableName#>]
        (<#=WriteColumns(constraint.FromProperties, ',')#>)
    ON DELETE <#=GetDeleteAction(constraint)#> ON UPDATE NO ACTION;
<#      
        // if the foreign keys are part of the primary key on the dependent end, then we should not add a constraint.
        if (!dependentSetEnd.EntitySet.ElementType.GetKeyProperties().Take(constraint.ToProperties.Count()).OrderBy(r => r.Name).SequenceEqual(constraint.ToProperties.OrderBy(r => r.Name)))
        {
#>

-- Creating non-clustered index for FOREIGN KEY '<#=WriteFKConstraintName(constraint)#>'
CREATE INDEX [IX_<#=WriteFKConstraintName(constraint)#>]
ON <#if (!IsSQLCE) {#>[<#=schemaName#>].<#}#>[<#=dependentTableName#>]
    (<#=WriteColumns(constraint.ToProperties, ',')#>);
<#      
        }
#>
GO

<#
    }
#>
-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------

Nach diesen Anpassungen wird aus meinem Modell, folgendes DDL-Skript erstellt. Dabei ist ersichtlich, dass die Extended Property - Prozeduren für die Dokumentation nach dem Tabellenskript generiert werden.


-- Dokumentation
-- --------------------------------------------------
-- Entity Designer DDL Script for SQL Server 2005, 2008, and Azure
-- --------------------------------------------------
-- Date Created: 05/11/2011 21:35:51
-- Generated from EDMX file: DokuTest.edmx
-- --------------------------------------------------

SET QUOTED_IDENTIFIER OFF;
GO
USE [dbDWHQueryTest];
GO
IF SCHEMA_ID(N'dbo') IS NULL EXECUTE(N'CREATE SCHEMA [dbo]');
GO

-- --------------------------------------------------
-- Dropping existing FOREIGN KEY constraints
-- --------------------------------------------------


-- --------------------------------------------------
-- Dropping existing tables
-- --------------------------------------------------


-- --------------------------------------------------
-- Creating all tables
-- --------------------------------------------------

-- Creating table 'ProductMenge'
CREATE TABLE [dbo].[ProductMenge] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL,
    [CategoryID] int  NOT NULL
);
GO

-- EDM Dokumentation

	exec sp_addextendedproperty N'MS_Description', 'Enthält alle Produkte und dient als Beispiel für die SQL Server Extended Properties', N'user', N'dbo', 
		N'table', N'ProductMenge'
	GO
exec sp_addextendedproperty N'MS_Description', 'Identifiziert das Produkt', N'user', 
				N'dbo', N'table', N'ProductMenge', N'column', N'ID'
				GO
exec sp_addextendedproperty N'MS_Description', 'Name des Produkts', N'user', 
				N'dbo', N'table', N'ProductMenge', N'column', N'Name'
				GO
exec sp_addextendedproperty N'MS_Description', 'Fremdschlüssel auf die Kategorie', N'user', 
				N'dbo', N'table', N'ProductMenge', N'column', N'CategoryID'
				GO
-- End documentation
-- Creating table 'CategoryMenge'
CREATE TABLE [dbo].[CategoryMenge] (
    [ID] int IDENTITY(1,1) NOT NULL,
    [Name] nvarchar(max)  NOT NULL
);
GO

-- EDM Dokumentation

	exec sp_addextendedproperty N'MS_Description', 'Enthält alle Produkte und dient als Beispiel für die SQL Server Extended Properties', N'user', N'dbo', 
		N'table', N'CategoryMenge'
	GO
exec sp_addextendedproperty N'MS_Description', 'Identifiziert die Kategorie', N'user', 
				N'dbo', N'table', N'CategoryMenge', N'column', N'ID'
				GO
exec sp_addextendedproperty N'MS_Description', 'Bezeichnung der Kategorie', N'user', 
				N'dbo', N'table', N'CategoryMenge', N'column', N'Name'
				GO
-- End documentation
-- --------------------------------------------------
-- Creating all PRIMARY KEY constraints
-- --------------------------------------------------

-- Creating primary key on [ID] in table 'ProductMenge'
ALTER TABLE [dbo].[ProductMenge]
ADD CONSTRAINT [PK_ProductMenge]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- Creating primary key on [ID] in table 'CategoryMenge'
ALTER TABLE [dbo].[CategoryMenge]
ADD CONSTRAINT [PK_CategoryMenge]
    PRIMARY KEY CLUSTERED ([ID] ASC);
GO

-- --------------------------------------------------
-- Creating all FOREIGN KEY constraints
-- --------------------------------------------------

-- Creating foreign key on [CategoryID] in table 'ProductMenge'
ALTER TABLE [dbo].[ProductMenge]
ADD CONSTRAINT [FK_CategoryProduct]
    FOREIGN KEY ([CategoryID])
    REFERENCES [dbo].[CategoryMenge]
        ([ID])
    ON DELETE NO ACTION ON UPDATE NO ACTION;

-- Creating non-clustered index for FOREIGN KEY 'FK_CategoryProduct'
CREATE INDEX [IX_FK_CategoryProduct]
ON [dbo].[ProductMenge]
    ([CategoryID]);
GO

-- --------------------------------------------------
-- Script has ended
-- --------------------------------------------------

Auf dem SQL-Server werden dadurch auf Tabellen und Spaltenebene die MS_Descriptions angelegt.

Folgende Abbildungen verdeutlichen dies:

: Abbildung 2 Beschreibung aus EDM auf Ebene der Tabelleneigenschaften

: Abbildung 3 Beschreibung aus DEM auf Ebene der Tabellenspalten

Die Dokumentation des Datenmodells auf dem SQL Server mithilfe der Extended Property - Prozeduren ist realisierbar, wenn man sich an den Gedanken gewöhnen kann, dies Selbst zu tun. Die Erweiterungspunkte mithilfe von T4 und Workflows machen daraus auch ein Kinderspiel.

Für mich war dieses Beispiel auch eine kleine Fallstudie, die nicht bis zum Ende durchdacht ist.

Was fehlt in dieser Vorlage im Bezug auf die Dokumentation noch?