C# Entity Framework 6 Sql server Extended property
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.
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.
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.
▋Visual Studio 2017 community
▋Microsoft Sql Server 2012
▋Entity Framework 6.1.3
▋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.
▋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.
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
▋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
[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.
