[Entity Framework 6] DB First - Create Metadata class and services with T4

 C#   Entity Framework   Code-first

▌Introduction

---

This articles was inspired by WASICHRIS’ article:

[Entity Framework] 利用T4快速建立Entity Data Model之MetaData類別

Since I have a UOW (Unit of work) pattern on my Entity Framework 6 library, every POCO entity should inherits the base entity class and has its own CRUD service class as following,

PS. We need to create a Partial class which inherits UowEntity in DB-first.

However the time for creating the Partial classes and Service classes for a whole new DB-first data model (ADO.NET Entity Data Model) is expensive, so that we will use T4 to generate them automatically.

▋Related articles

[Entity Framework 6] Use MetadataType to define the attribure of DAO properties

[Visual Studio 2015] Create and distribute a Poject Template with Wizard (1)

[Visual Studio 2015] Create and distribute a Poject Template with Wizard (2)

▌Environment

---

▋Visual Studio 2017 community

▋Entity Framework 6.1.3

▌Implement

---

▋T4 for Models

OK, the code is complex and I won’t give too much explanation on it.

The flow is,

1.  Load .edmx and get all entity types (EntityType) from it

2.  For every EntityType, generate a .cs file with same class name (if not exists)

3.  Write namespaces and partial class structure into the .cs file

Gist for EdmxModel-metadata.tt

	<#@ template language="C#" debug="true" hostspecific="true"#>
	<#@ assembly name="System.Core" #>
	<#@ import namespace="System.IO" #>
	<#@ import namespace="System.Diagnostics" #>
	<#@ import namespace="System.Linq" #>
	<#@ import namespace="System.Text" #>
	<#@ import namespace="System.Text.RegularExpressions" #>
	<#@ import namespace="System.Collections.Generic" #>
	<#@ include file="EF.Utility.CS.ttinclude"#>
	<#@ output extension=".cs" #>
	<#
	// Formatting helper for code
	CodeGenerationTools code = new CodeGenerationTools(this);
	// object for creating entity information
	MetadataLoader loader = new MetadataLoader(this);
	// TODO: NEED TO PROVIDE EDMX FILE LOCATION
	string inputFile = string.Empty;
	var currentPath = this.Host.ResolvePath("");
	string[] files = System.IO.Directory.GetFiles(currentPath, "*.edmx");
	if(files==null || files.Length <=0)
	return string.Empty;
	else
	inputFile = files[0];
	// File generation suffix
	string suffix = "Metadata";
	// Meta data information for the conceptual model
	EdmItemCollection ItemCollection = loader.CreateEdmItemCollection(inputFile);
	// Suggested namespace
	string namespaceName = code.VsNamespaceSuggestion();// + suffix;
	// File generator according to different section
	EntityFrameworkTemplateFileManager fileManager =
	EntityFrameworkTemplateFileManager.Create(this);
	// Loop through each entity type
	foreach (EntityType entity in
	ItemCollection.GetItems<EntityType>().OrderBy(e => e.Name))
	{
	// File name for data annotation file
	string fileName = entity.Name + suffix + ".cs";
	// Check for file existence, If it does not
	// exist create new file for data annotation
	if (!DoesFileExist(fileName))
	{
	// Header for file
	writeHeader(fileManager);
	// Create new file
	fileManager.StartNewFile(fileName);
	// Add namespaces into file
	BeginNamespace(namespaceName, code);
	#>
	/// <summary>
	/// <#=code.Escape(entity)#> class
	/// </summary>
	[Description("<#=GetFriendlyName(code.Escape(entity))#>")]
	[MetadataType(typeof(<#=code.Escape(entity) + suffix#>))]
	<#= Accessibility.ForType(entity)#> <#=code.SpaceAfter(code.AbstractOption(entity))#> partial class <#=code.Escape(entity)#> : UowEntity
	{
	}
	/// <summary>
	/// <#=code.Escape(entity)#> Metadata class
	/// </summary>
	internal <#=code.SpaceAfter(code.AbstractOption(entity))#> class <#=code.Escape(entity) + suffix#>
	{
	<#
	// Loop through each primitive property of entity
	foreach (EdmProperty edmProperty in entity.Properties.Where(p =>
	p.TypeUsage.EdmType is PrimitiveType && p.DeclaringType == entity))
	{
	#>
	<#= CodeRegion.GetIndent(1) #>
	/// <summary>
	/// <#=GetFriendlyName(code.Escape(edmProperty))#>
	/// </summary>
	<#
	// Write display name data annotation
	writeDisplayName(edmProperty);
	// Write description data annotation
	writeDescriptionAttribute(edmProperty);
	// Write required field data annotation
	writeRequiredAttribute(edmProperty);
	// Write string length annotation
	writeStringLengthAttribute(edmProperty);
	#>
	<#=Accessibility.ForProperty(edmProperty)#> <#=code.Escape(edmProperty.TypeUsage)#> <#=code.Escape(edmProperty)#> { <#=Accessibility.ForGetter(edmProperty)#>get; <#=Accessibility.ForSetter(edmProperty)#>set; }
	<#
	}
	#>
	<#= CodeRegion.GetIndent(1) #>
	}
	<#
	// End namespace
	EndNamespace(namespaceName);
	}
	else
	{
	// Write with original file
	fileManager.StartNewFile(fileName);
	this.Write(OutputFile(fileName));
	}
	}
	fileManager.Process();
	#>
	<#+
	// Write display name data annotation
	void writeDisplayName(EdmProperty edmProperty) {
	string displayName = edmProperty.Name;
	// Check for property name
	if (!string.IsNullOrEmpty(displayName))
	{
	// Generate user friendly name
	displayName = GetFriendlyName(edmProperty.Name);
	// Populate actual string to be written
	WriteLine("{0}[DisplayName(\"{1}\")]", CodeRegion.GetIndent(1), displayName);
	}
	}
	//Write description attribute
	void writeDescriptionAttribute(EdmProperty edmProperty) {
	string displayName = edmProperty.Name; //Use Name as description
	// Check for property name
	if (!string.IsNullOrEmpty(displayName))
	{
	// Generate user friendly name
	displayName = GetFriendlyName(edmProperty.Name);
	// Populate actual string to be written
	WriteLine("{0}[Description(\"{1}\")]", CodeRegion.GetIndent(1), displayName);
	}
	}
	//Write required attribute
	void writeRequiredAttribute(EdmProperty edmProperty) {
	// Check for required property
	if (!edmProperty.Nullable)
	{
	WriteLine("{0}[Required(ErrorMessage = \"{1} is required\")]",
	CodeRegion.GetIndent(2),GetFriendlyName(edmProperty.Name));
	}
	}
	//Write max string length
	void writeStringLengthAttribute(EdmProperty edmProperty) {
	// Object for retrieving additional information from property
	Facet maxLengthfacet;
	// Try to get max length from property
	if (edmProperty.TypeUsage.Facets.TryGetValue("MaxLength", true, out maxLengthfacet))
	{
	// Max length for property
	double lengthAttribute;
	// Try to parse max length value
	if (double.TryParse(maxLengthfacet.Value.ToString(), out lengthAttribute))
	{
	// Generate actual string for attribute
	WriteLine("{0}[MaxLength({1}, ErrorMessage = \"{2} cannot " +
	"be longer than {1} characters\")]",
	CodeRegion.GetIndent(2),lengthAttribute,GetFriendlyName(edmProperty.Name));
	}
	}
	}
	// Initialize header
	void writeHeader(EntityFrameworkTemplateFileManager fileManager, params string[] extraUsings)
	{
	fileManager.StartHeader();
	#>
	using System;
	using System.ComponentModel;
	using System.ComponentModel.DataAnnotations;
	using JB.Infra.Util.EF.Entity;
	<#=String.Join(String.Empty, extraUsings.Select(u => "using " + u + ";" + Environment.NewLine).ToArray())#>
	<#+
	fileManager.EndBlock();
	}
	// Add namespace
	void BeginNamespace(string namespaceName, CodeGenerationTools code)
	{
	// Generate region
	CodeRegion region = new CodeRegion(this);
	// Check for namespace value
	if (!String.IsNullOrEmpty(namespaceName))
	{
	#>
	namespace <#=code.EscapeNamespace(namespaceName)#>
	{
	<#+
	// Add indent
	PushIndent(CodeRegion.GetIndent(1));
	}
	}
	// End namespace
	void EndNamespace(string namespaceName)
	{
	if (!String.IsNullOrEmpty(namespaceName))
	{
	PopIndent();
	#>
	}
	<#+
	}
	}
	#>
	<#+
	// Check for file existence
	bool DoesFileExist(string filename)
	{
	return File.Exists(Path.Combine(GetCurrentDirectory(),filename));
	}
	// Get current folder directory
	string GetCurrentDirectory()
	{
	return System.IO.Path.GetDirectoryName(this.Host.TemplateFile);
	}
	// Get content of file name
	string OutputFile(string filename)
	{
	using(StreamReader sr =
	new StreamReader(Path.Combine(GetCurrentDirectory(),filename)))
	{
	return sr.ReadToEnd();
	}
	}
	// Get friendly name for property names
	string GetFriendlyName(string value)
	{
	return Regex.Replace(value,
	"([A-Z]+)", " $1",
	RegexOptions.Compiled).Trim();
	}
	#>

view raw EdmxModel-metadata.tt hosted with ❤ by GitHub

Gist for EdmxModel-simple.tt

	<#@ template language="C#" debug="true" hostspecific="true"#>
	<#@ assembly name="System.Core" #>
	<#@ import namespace="System.IO" #>
	<#@ import namespace="System.Diagnostics" #>
	<#@ import namespace="System.Linq" #>
	<#@ import namespace="System.Text" #>
	<#@ import namespace="System.Text.RegularExpressions" #>
	<#@ import namespace="System.Collections.Generic" #>
	<#@ include file="EF.Utility.CS.ttinclude"#>
	<#@ output extension=".cs" #>
	<#
	// Formatting helper for code
	CodeGenerationTools code = new CodeGenerationTools(this);
	// object for creating entity information
	MetadataLoader loader = new MetadataLoader(this);
	// TODO: NEED TO PROVIDE EDMX FILE LOCATION
	string inputFile = string.Empty;
	var currentPath = this.Host.ResolvePath("");
	string[] files = System.IO.Directory.GetFiles(currentPath, "*.edmx");
	if(files==null || files.Length <=0)
	return string.Empty;
	else
	inputFile = files[0];
	// File generation suffix
	string suffix = "Metadata";
	// Meta data information for the conceptual model
	EdmItemCollection ItemCollection = loader.CreateEdmItemCollection(inputFile);
	// Suggested namespace
	string namespaceName = code.VsNamespaceSuggestion();// + suffix;
	// File generator according to different section
	EntityFrameworkTemplateFileManager fileManager =
	EntityFrameworkTemplateFileManager.Create(this);
	// Loop through each entity type
	foreach (EntityType entity in
	ItemCollection.GetItems<EntityType>().OrderBy(e => e.Name))
	{
	// File name for data annotation file
	string fileName = entity.Name + suffix + ".cs";
	// Check for file existence, If it does not
	// exist create new file for data annotation
	if (!DoesFileExist(fileName))
	{
	// Header for file
	writeHeader(fileManager);
	// Create new file
	fileManager.StartNewFile(fileName);
	// Add namespaces into file
	BeginNamespace(namespaceName, code);
	#>
	/// <summary>
	/// <#=code.Escape(entity)#> class
	/// </summary>
	[Description("<#=GetFriendlyName(code.Escape(entity))#>")]
	<#= Accessibility.ForType(entity)#> <#=code.SpaceAfter(code.AbstractOption(entity))#> partial class <#=code.Escape(entity)#> : UowEntity
	{
	}
	<#
	// End namespace
	EndNamespace(namespaceName);
	}
	else
	{
	// Write with original file
	fileManager.StartNewFile(fileName);
	this.Write(OutputFile(fileName));
	}
	}
	fileManager.Process();
	#>
	<#+
	// Write display name data annotation
	void writeDisplayName(EdmProperty edmProperty) {
	string displayName = edmProperty.Name;
	// Check for property name
	if (!string.IsNullOrEmpty(displayName))
	{
	// Generate user friendly name
	displayName = GetFriendlyName(edmProperty.Name);
	// Populate actual string to be written
	WriteLine("{0}[DisplayName(\"{1}\")]", CodeRegion.GetIndent(1), displayName);
	}
	}
	//Write description attribute
	void writeDescriptionAttribute(EdmProperty edmProperty) {
	string displayName = edmProperty.Name; //Use Name as description
	// Check for property name
	if (!string.IsNullOrEmpty(displayName))
	{
	// Generate user friendly name
	displayName = GetFriendlyName(edmProperty.Name);
	// Populate actual string to be written
	WriteLine("{0}[Description(\"{1}\")]", CodeRegion.GetIndent(1), displayName);
	}
	}
	//Write required attribute
	void writeRequiredAttribute(EdmProperty edmProperty) {
	// Check for required property
	if (!edmProperty.Nullable)
	{
	WriteLine("{0}[Required(ErrorMessage = \"{1} is required\")]",
	CodeRegion.GetIndent(2),GetFriendlyName(edmProperty.Name));
	}
	}
	//Write max string length
	void writeStringLengthAttribute(EdmProperty edmProperty) {
	// Object for retrieving additional information from property
	Facet maxLengthfacet;
	// Try to get max length from property
	if (edmProperty.TypeUsage.Facets.TryGetValue("MaxLength", true, out maxLengthfacet))
	{
	// Max length for property
	double lengthAttribute;
	// Try to parse max length value
	if (double.TryParse(maxLengthfacet.Value.ToString(), out lengthAttribute))
	{
	// Generate actual string for attribute
	WriteLine("{0}[MaxLength({1}, ErrorMessage = \"{2} cannot " +
	"be longer than {1} characters\")]",
	CodeRegion.GetIndent(2),lengthAttribute,GetFriendlyName(edmProperty.Name));
	}
	}
	}
	// Initialize header
	void writeHeader(EntityFrameworkTemplateFileManager fileManager, params string[] extraUsings)
	{
	fileManager.StartHeader();
	#>
	using System;
	using System.ComponentModel;
	using System.ComponentModel.DataAnnotations;
	using JB.Infra.Util.EF.Entity;
	<#=String.Join(String.Empty, extraUsings.Select(u => "using " + u + ";" + Environment.NewLine).ToArray())#>
	<#+
	fileManager.EndBlock();
	}
	// Add namespace
	void BeginNamespace(string namespaceName, CodeGenerationTools code)
	{
	// Generate region
	CodeRegion region = new CodeRegion(this);
	// Check for namespace value
	if (!String.IsNullOrEmpty(namespaceName))
	{
	#>
	namespace <#=code.EscapeNamespace(namespaceName)#>
	{
	<#+
	// Add indent
	PushIndent(CodeRegion.GetIndent(1));
	}
	}
	// End namespace
	void EndNamespace(string namespaceName)
	{
	if (!String.IsNullOrEmpty(namespaceName))
	{
	PopIndent();
	#>
	}
	<#+
	}
	}
	#>
	<#+
	// Check for file existence
	bool DoesFileExist(string filename)
	{
	return File.Exists(Path.Combine(GetCurrentDirectory(),filename));
	}
	// Get current folder directory
	string GetCurrentDirectory()
	{
	return System.IO.Path.GetDirectoryName(this.Host.TemplateFile);
	}
	// Get content of file name
	string OutputFile(string filename)
	{
	using(StreamReader sr =
	new StreamReader(Path.Combine(GetCurrentDirectory(),filename)))
	{
	return sr.ReadToEnd();
	}
	}
	// Get friendly name for property names
	string GetFriendlyName(string value)
	{
	return Regex.Replace(value,
	"([A-Z]+)", " $1",
	RegexOptions.Compiled).Trim();
	}
	#>

view raw EdmxModel-uow.tt hosted with ❤ by GitHub

The difference between EdmxModel-metadata.tt and EdmxModel-simple.tt:

1.   The partial class created from EdmxModel-metadata.tt inherits UowEntity and defines the MetadataType

2.   The partial class created from EdmxModel-simple.tt only inherits UowEntity

Assume that there is a POCO: MyModel in EDMX, the above T4 will generate:

1.  MyModels.cs (by EdmxModel-simple.tt)

2.  MyModelsMetadata.cs (by EdmxModel-metadata.tt)

And their contents:

▋MyModels.cs

namespace JB.Infra.Util.EF.Models

{

    using System;

    using System.Collections.Generic;

   

    public partial class MyModels

    {

        public int Id { get; set; }

        public string Name { get; set; }

        public string Department { get; set; }

    }

}

▋MyModelsMetadata.cs

using System;

using System.ComponentModel;

using System.ComponentModel.DataAnnotations;

using JB.Infra.Util.EF.Entity;

namespace JB.Infra.Utility.EF.DbFirst.UnitTest.Models

{

    /// <summary>

    /// MyModels class

    /// </summary>

    [Description("My Models")]

    [MetadataType(typeof(MyModelsMetadata))]

    public partial class MyModels : UowEntity

    {

    }

    /// <summary>

    /// MyModels Metadata class

    /// </summary>

    internal class MyModelsMetadata

    {

        /// <summary>

        /// Id

        /// </summary>       

        [DisplayName("Id")]

        [Description("Id")]

        [Required(ErrorMessage = "Id is required")]

        public int Id { get; set; }

        /// skip ...

▋T4 for Services

Same process on generating CRUD Service class.

Notice the relative path of .edmx is updated to “../Models”

Gist for EdmxService.tt

	<#@ template language="C#" debug="true" hostspecific="true"#>
	<#@ assembly name="System.Core" #>
	<#@ import namespace="System.IO" #>
	<#@ import namespace="System.Diagnostics" #>
	<#@ import namespace="System.Linq" #>
	<#@ import namespace="System.Text" #>
	<#@ import namespace="System.Text.RegularExpressions" #>
	<#@ import namespace="System.Collections.Generic" #>
	<#@ include file="EF.Utility.CS.ttinclude"#>
	<#@ output extension=".cs" #>
	<#
	// Formatting helper for code
	CodeGenerationTools code = new CodeGenerationTools(this);
	// object for creating entity information
	MetadataLoader loader = new MetadataLoader(this);
	// TODO: NEED TO PROVIDE EDMX FILE LOCATION
	string inputFile = string.Empty;
	var currentPath = this.Host.ResolvePath("../Models");
	string[] files = System.IO.Directory.GetFiles(currentPath, "*.edmx");
	if(files==null || files.Length <=0)
	return string.Empty;
	else
	inputFile = files[0];
	// File generation suffix
	string suffix = "Service";
	// Meta data information for the conceptual model
	EdmItemCollection ItemCollection = loader.CreateEdmItemCollection(inputFile);
	// Suggested namespace
	string namespaceName = code.VsNamespaceSuggestion();// + suffix;
	// File generator according to different section
	EntityFrameworkTemplateFileManager fileManager =
	EntityFrameworkTemplateFileManager.Create(this);
	// Loop through each entity type
	foreach (EntityType entity in
	ItemCollection.GetItems<EntityType>().OrderBy(e => e.Name))
	{
	// File name for data annotation file
	string fileName = entity.Name + suffix + ".cs";
	// Check for file existence, If it does not
	// exist create new file for data annotation
	if (!DoesFileExist(fileName))
	{
	// Header for file
	string modelNamespace= string.Concat(code.EscapeNamespace(namespaceName).ToString().Replace(".Service",".Models"));
	writeHeader(fileManager, modelNamespace);
	// Create new file
	fileManager.StartNewFile(fileName);
	// Add namespaces into file
	BeginNamespace(namespaceName, code);
	#>
	/// <summary>
	/// <#=code.Escape(entity)#> Service
	/// </summary>
	<#=Accessibility.ForType(entity)#> class <#=code.Escape(entity)#>Service<T> : JB.Infra.Util.EF.Service.BaseDalService<T> where T : <#=code.Escape(entity)#>
	{
	public <#=code.Escape(entity)#>Service(DbContext dbContext):base(dbContext)
	{
	}
	}
	<#
	// End namespace
	EndNamespace(namespaceName);
	}
	else
	{
	// Write with original file
	fileManager.StartNewFile(fileName);
	this.Write(OutputFile(fileName));
	}
	}
	fileManager.Process();
	#>
	<#+
	// Initialize header
	void writeHeader(EntityFrameworkTemplateFileManager fileManager, params string[] extraUsings)
	{
	fileManager.StartHeader();
	#>
	using System.Data.Entity;
	<#=String.Join(String.Empty, extraUsings.Select(u => "using " + u + ";" + Environment.NewLine).ToArray())#>
	<#+
	fileManager.EndBlock();
	}
	// Add namespace
	void BeginNamespace(string namespaceName, CodeGenerationTools code)
	{
	// Generate region
	CodeRegion region = new CodeRegion(this);
	// Check for namespace value
	if (!String.IsNullOrEmpty(namespaceName))
	{
	#>
	namespace <#=code.EscapeNamespace(namespaceName)#>
	{
	<#+
	// Add indent
	PushIndent(CodeRegion.GetIndent(1));
	}
	}
	// End namespace
	void EndNamespace(string namespaceName)
	{
	if (!String.IsNullOrEmpty(namespaceName))
	{
	PopIndent();
	#>
	}
	<#+
	}
	}
	#>
	<#+
	// Check for file existence
	bool DoesFileExist(string filename)
	{
	return File.Exists(Path.Combine(GetCurrentDirectory(),filename));
	}
	// Get current folder directory
	string GetCurrentDirectory()
	{
	return System.IO.Path.GetDirectoryName(this.Host.TemplateFile);
	}
	// Get content of file name
	string OutputFile(string filename)
	{
	using(StreamReader sr =
	new StreamReader(Path.Combine(GetCurrentDirectory(),filename)))
	{
	return sr.ReadToEnd();
	}
	}
	// Get friendly name for property names
	string GetFriendlyName(string value)
	{
	return Regex.Replace(value,
	"([A-Z]+)", " $1",
	RegexOptions.Compiled).Trim();
	}
	#>

view raw EdmxService-uow.tt hosted with ❤ by GitHub

Result:

▌Reference

---

▋ [Entity Framework] 利用T4快速建立Entity Data Model之MetaData類別

[Entity Framework 6] Code First (6) - Add or update extended property from code-first models

  C#     Entity Framework 6   Sql server    Extended property

▌Introduction

---

Ok, this article is about adding or updating table or column extended properties with the information of the code-first models in in Microsoft SQL Server and Entity Framework 6.

Why?

The scenario is that I need to generate a DB Schema document from SQL Server.

My friend, Chris had written a great open-source toolkit, Tool-TemplateBasedDbDocumentMaker on generating the document.

However, the issue is that the following information must be maintained as property: Description, on both Table and Column in SQL Server.

However I don’t really want to maintain the Description property by SSMS(Sql Server Management Studio) since I am using the Entity Framework code-first methodology.

I came to an idea to maintain the information on the code-first model as below by the System.ComponentModel.DescriptionAttribute and then synchronize it to the table or column’s Description property.

The flow will be like this, we will create a DB Schema Initializer to get the Attribute values from code-first models and then synchronize the DescriptionAttribute values to the database.

▌Environment

---

▋Visual Studio 2017 community

▋Microsoft Sql Server 2012

▋Entity Framework 6.1.3

▌Implement

---

▋sp_addextendedproperty & sp_updateextendedproperty

Before we write any code, we need to know how to add or update the Description property of a table or column.

We can use the Store procedure: sp_addextendedproperty and sp_updateextendedproperty to add/update the Description property of Table or column as following.

▋Add/Update Description of Table

EXEC sp_addextendedproperty  

     @name = N'MS_Description', 

     @value = 'my description for table', 

     @level0type = N'Schema', @level0name = dbo, 

     @level1type = N'Table',  @level1name = myTableName;

EXEC sp_updateextendedproperty  

     @name = N'MS_Description', 

     @value = 'my description for table', 

     @level0type = N'Schema', @level0name = dbo, 

     @level1type = N'Table',  @level1name = myTableName;

Notice the extended property name is “MS_Description” for the Description property.

Change “my description for table” and “myTableName” to your values.

▋Add/Update Description of Column

EXEC sp_updateextendedproperty  

    @name = N'MS_Description',

    @value = 'my description for column', 

    @level0type = N'Schema', @level0name = dbo,

    @level1type = N'Table',  @level1name = myTableName,

    @level2type = N'Column', @level2name = myColumnName;

EXEC sp_updateextendedproperty  

    @name = N'MS_Description',

    @value = 'my description for column', 

    @level0type = N'Schema', @level0name = dbo,

    @level1type = N'Table',  @level1name = myTableName,

    @level2type = N'Column', @level2name = myColumnName;

Notice the extended property name is “MS_Description” for the Description property.

Change “my description for table”, “myTableName” and “myColumnName” to your values.

▋List tables and columns

Furthermore, we can use the following TSQL to list Tables and Columns which we will use later.

▋Get table list

SELECT name FROM sys.tables ORDER BY name

PS. However, consider that maybe not all tables must be synchronized and to get the CLASS (Entity) type of a table, we will skip this TSQL and use the DbSet<TEntity> declarations in DbContext.

▋Get column list of a table

SELECT COLUMN_NAME

FROM INFORMATION_SCHEMA.COLUMNS

WHERE TABLE_NAME = 'myTableName' AND TABLE_SCHEMA='dbo'

▋How to get all TEntity Classes in DbContext?

We need to use Reflection on DbContext class to get all DbSet<TEntity> properties.

Here is an extension method for extract them as List<PropertyInfo> like this,

public static List<PropertyInfo> GetDbSetProperties(this DbContext context)

{

            var dbSetProperties = new List<PropertyInfo>();

            var properties = context.GetType().GetProperties();

            foreach (var property in properties)

            {

                var setType = property.PropertyType;

                var isDbSet = setType.IsGenericType && (typeof(IDbSet<>).IsAssignableFrom(setType.GetGenericTypeDefinition()) || setType.GetInterface(typeof(IDbSet<>).FullName) != null);

                if (isDbSet)

                {

                    dbSetProperties.Add(property);

                }

            }

            return dbSetProperties;

}

Notice the returned objects are DbSet<TEntity>, so we need to extract the TEntity Type as following,

var dbsetProps = context.GetDbSetProperties();

 dbsetProps.ForEach(prop =>

            {

                //Get DbSet's model. For example, DbSet<MyModel> => MyModel

                Type typeArgument = prop.PropertyType.GetGenericArguments()[0];

            }

Now we have the TEntity type, but how to get the Attribute values from it?

▋Create an Utility to get Attributes from class or property

Here is the AttributeUtility to get the Attribute objects from a class or a property.

public class AttributeUtility

    {

        public static Dictionary<string, object> GetClassAttributes<T>(bool isGetCustomAttribute = false)

        {

            Dictionary<string, object> dict =

                typeof(T).GetCustomAttributes(isGetCustomAttribute).ToDictionary(a => a.GetType().Name, a => a);

            return dict;

        }

        public static Dictionary<string, List<object>> GetPropertyAttributes<T>(String propertyName, bool isGetCustomAttribute=false)

        {
            //Due to the multiple attributes on the same attribute type, use object array to keep the values

            var dict = new Dictionary<string, List<object>>();

            object[] attrs = typeof(T).GetProperty(propertyName).GetCustomAttributes(isGetCustomAttribute);

            for(var i =0; i<attrs.Length; i++)

            {

                string key = attrs[i].GetType().Name;

                if (dict.ContainsKey(key))

                {

                    dict[key].Add(attrs[i]);

                }

                else

                    dict.Add(key, new List<object>() { attrs[i] });

            }

            return dict;

        }

    }

Notice that we often define multiple [Index(XXX)] on a property in a TEntity class.

So we return Dictionary<string, List<object>> to avoid KEY CONFLICT problem in GetPropertyAttributes<T>!

▋Get Attributes from TEntity class

Since we have

1.  TEntity type

2.  AttributeUtility class

We can INVOKE the methods in AttributeUtility class with TEntity type by Reflection.

See my previous article: [C#] Reflection on create Generic Class or invoking Generic Method for reference.

For example, we can get the DescriptionAttribute value like this,

private string getTableAttribute(Type typeArgument)

 {

            var method = typeof(AttributeUtility).GetMethod("GetClassAttributes");

            var generic = method.MakeGenericMethod(typeArgument);

            var result = generic.Invoke(null, new object[] { false });

            var dics = (result as Dictionary<string, object>);

            Object value = null;

            if (dics.TryGetValue("DescriptionAttribute", out value))

            {

                return (value as System.ComponentModel.DescriptionAttribute).Description;

            }

            else

            {

                return string.Empty;

            }

 }

I refactored the above codes to have more flexibility on it.

▋Get Attribute Object from TEntity class

private object getTableAttribute(Type typeArgument, string attribute)

{

            var method = typeof(AttributeUtility).GetMethod("GetClassAttributes");

            var generic = method.MakeGenericMethod(typeArgument);

            var result = generic.Invoke(null, new object[] { false });

            var dics = (result as Dictionary<string, object>);

            Object value = null;

            if (dics.TryGetValue(attribute, out value))

                return value;

            else

                return null;

 }

▋Get Attribute Object from a property in class

private List<object> getPropAttribute(Type typeArgument, string propName, string attribute)

 {

            var method = typeof(AttributeUtility).GetMethod("GetPropertyAttributes");

            var generic = method.MakeGenericMethod(typeArgument);

            var result = generic.Invoke(null, new object[] { propName, false });

            var dics = (result as Dictionary<string, List<object>>);

            List<Object> values = null;

            if (dics.TryGetValue(attribute, out values))

                return values;

            else

                return null;

 }

▋Combine all the things!

Okay, now we know how to

1.  get the TEntity Types of all code-first models

2.  get the DescriptionAttribute value from class

3.  get the DescriptionAttribute value from property

4.  Add or update the value of extended property: MS_Description, on SQL Server Table or column

Lets create the DB Schema Initializer as the one in the picture,

I put my DB Schema Initializer in Gist, all you have to do is call it on the Seed method in Migraions\Configuration.cs

protected override void Seed(Welfare.DAL.SvDbContext context)

{

            var schemaIni = new DbSchemaInitializer(context);

            schemaIni.UpdateDescriptions();

  }

▋Full code of DbSchemaInitializer

	public class DbSchemaInitializer : IDisposable
	{
	private DbContext _dbContext = null;
	public DbSchemaInitializer(DbContext dbContext)
	{
	if (dbContext == null)
	throw new ArgumentNullException("dbContext:DbContext cannot be null!");
	else
	this._dbContext = dbContext;
	}
	/// Update the descriptions of Table and its columns from Code first model's DescriptionAttribute
	public void UpdateTableColDescriptions()
	{
	var context = this._dbContext;
	var dbsetProps = context.GetDbSetProperties();
	dbsetProps.ForEach(prop =>
	{
	#region Get DAO type
	//Get DbSet's model. For example, DbSet<MyModel> => MyModel
	Type typeArgument = prop.PropertyType.GetGenericArguments()[0];
	#endregion
	#region Get Table description
	string tableName = string.Empty, desc = string.Empty;
	Object tableNameAttr = this.getTableAttribute(typeArgument, "TableAttribute");
	Object descAttr = this.getTableAttribute(typeArgument, "DescriptionAttribute");
	if (tableNameAttr != null)
	tableName = (tableNameAttr as System.ComponentModel.DataAnnotations.Schema.TableAttribute).Name;
	if (descAttr != null)
	desc = (descAttr as System.ComponentModel.DescriptionAttribute).Description;
	if (!string.IsNullOrEmpty(tableName) && !string.IsNullOrEmpty(desc))
	{
	//Sync to database
	this.syncTableDescription(context, tableName, desc);
	}
	#endregion
	#region Get Columns description
	if (!string.IsNullOrEmpty(tableName))
	{
	List<string> cols = this.getColsFromTable(context, tableName);
	var methodProp = typeof(AttributeUtility).GetMethod("GetPropertyAttributes");
	cols.ForEach(col =>
	{
	List<Object> propDescAttrs = this.getPropAttribute(typeArgument, col, "DescriptionAttribute");
	if (propDescAttrs != null && propDescAttrs.Count > 0)
	{
	string colDesc = (propDescAttrs[0] as System.ComponentModel.DescriptionAttribute).Description;
	Debug.WriteLine($"{tableName}.{col} = {colDesc}");
	//Sync to database
	this.syncColDescription(context, tableName, col, colDesc);
	}
	});
	}
	#endregion
	});
	}
	/// Get attribute of class
	private object getTableAttribute(Type typeArgument, string attribute)
	{
	var method = typeof(AttributeUtility).GetMethod("GetClassAttributes");
	var generic = method.MakeGenericMethod(typeArgument);
	var result = generic.Invoke(null, new object[] { false });
	var dics = (result as Dictionary<string, object>);
	Object value = null;
	if (dics.TryGetValue(attribute, out value))
	return value;
	else
	return null;
	}
	/// Get attribute of property
	private List<object> getPropAttribute(Type typeArgument, string propName, string attribute)
	{
	var method = typeof(AttributeUtility).GetMethod("GetPropertyAttributes");
	var generic = method.MakeGenericMethod(typeArgument);
	var result = generic.Invoke(null, new object[] { propName, false });
	var dics = (result as Dictionary<string, List<object>>);
	List<Object> values = null;
	if (dics.TryGetValue(attribute, out values))
	return values;
	else
	return null;
	}
	/// Get all Column names of a Table
	private List<string> getColsFromTable(DbContext context, string tableName)
	{
	string sql = $@"
	SELECT COLUMN_NAME
	FROM INFORMATION_SCHEMA.COLUMNS
	WHERE TABLE_NAME = '{tableName}' AND TABLE_SCHEMA='dbo'";
	return context.Database.SqlQuery<string>(sql).ToList();
	}
	/// Use sp_addextendedproperty/sp_updateextendedproperty to update the Description of a Table
	private void syncTableDescription(DbContext context, string tableName, string description)
	{
	string sql = string.Empty;
	int rslt = 0;
	try
	{
	sql = $@"
	EXEC sp_updateextendedproperty
	@name = N'MS_Description',
	@value = '{description}',
	@level0type = N'Schema', @level0name = dbo,
	@level1type = N'Table', @level1name = {tableName}";
	rslt = context.Database.ExecuteSqlCommand(sql);
	}
	catch (Exception ex)
	{
	sql = $@"
	EXEC sp_addextendedproperty
	@name = N'MS_Description',
	@value = '{description}',
	@level0type = N'Schema', @level0name = dbo,
	@level1type = N'Table', @level1name = {tableName}";
	context.Database.ExecuteSqlCommand(sql);
	}
	}
	/// Use sp_addextendedproperty/sp_updateextendedproperty to update the Description of a Column
	private void syncColDescription(DbContext context, string tableName, string colName, string description)
	{
	string sql = string.Empty;
	try
	{
	sql = $@"
	EXEC sp_updateextendedproperty
	@name = N'MS_Description'
	,@value = '{description}'
	,@level0type = N'Schema', @level0name = dbo
	,@level1type = N'Table', @level1name = {tableName}
	,@level2type = N'Column', @level2name = ""{colName}"";";
	context.Database.ExecuteSqlCommand(sql);
	}
	catch (Exception ex)
	{
	sql = $@"
	EXEC sp_addextendedproperty
	@name = N'MS_Description'
	,@value = '{description}'
	,@level0type = N'Schema', @level0name = dbo
	,@level1type = N'Table', @level1name = {tableName}
	,@level2type = N'Column', @level2name = ""{colName}""";
	context.Database.ExecuteSqlCommand(sql);
	}
	}
	/// <summary>
	/// Dispose
	/// </summary>
	public void Dispose()
	{
	if (this._dbContext != null)
	{
	this._dbContext.Dispose();
	}
	}
	}

view raw DbSchemaInitializer.cs hosted with ❤ by GitHub

▋Demo

▋Extra actions

▋Fail to add/update the extended property on columns named as Reserved Keywords

When we try to add or update the extended property on columns named as Reserved Keywords, such as [Order],

The following TSQL will fail with error message:
“incorrect syntax near keyword 'Order'”

EXEC sp_addextendedproperty  

    @name = N'MS_Description',

    @value = 'Order for raw', 

    @level0type = N'Schema', @level0name = dbo,

    @level1type = N'Table',  @level1name = MyTable,

    @level2type = N'Column', @level2name = Order

GO

So make sure adding bracket or double quote to the column name:

@level2name = [Order]

@level2name = "Order" 

▋For those don’t want to sync

We can create our own class which inherit System.ComponentModel.DescriptionAttribute to support another argument: Ignore.

[AttributeUsage(AttributeTargets.All)]

public class AdvDescriptionAttribute : System.ComponentModel.DescriptionAttribute

 {

        public new static readonly AdvDescriptionAttribute Default;

        public AdvDescriptionAttribute(string description, bool ignore = false) : base(description)

        {

            this.IgnoreValue = ignore;

        }

        public virtual bool Ignore { get{ return this.IgnoreValue; } }

        protected bool IgnoreValue { get; set; }

 }

Use the new AdvDescriptionAttribute on code-first models as following,

[Table("AnnProds")]

[AdvDescription("Product list")]

 public class AnnProd

  {

        [Key]

        [AdvDescription("ID")]

        public int Id { get; set; }

       

[StringLength(500)]

        [AdvDescription("Name", ignore:true)]

        public string Name { get; set; }

       

[StringLength(200)]

        [AdvDescription("Company", ignore: true)]

        public string Company { get; set; }

       

        [AdvDescription("Price", ignore: true)]

        public decimal Price { get; set; }

        [AdvDescription("Need transport", ignore: true)]

        public bool IsTransport { get; set; }

}

Update the DbSchemaInitializer class to support checking ignore value on AdvDescriptionAttribute and decide if synchronizing to database or not.

For example, check ignore value before updating the Description of column in database.

bool ignore = (propDescAttrs[0] as AdvDescriptionAttribute).Ignore;

if (!ignore)

 {

     //Sync to database

this.syncColDescription(context, tableName, col, colDesc);

}

The above model: AnnProd, will result in the following result in document.

▌Reference

---

▋wasichris/Tool-TemplateBasedDbDocumentMaker

▋sp_addextendedproperty (Transact-SQL)

▋如何編輯 SQL Server 中物件的擴充屬性(Extended Property)(保哥)