How to get extended properties of sql table in Entity framework (EF6)?

Question

How to get extended properties of sql table in Entity framework (EF6)?

c# edmx entity-framework-6 sql-server-2008 t4

Question

I am working in VS-2013. I've added ADO.NET Entity Data Model containing:

EF Designer from Database,
Code First From Database

Connected to some of the tables in my database, it generated tt file with partial class and add annotation on every field of tables as below:

ms_item_cat.cs

public partial class ms_item_cat
{
    public ms_item_cat()
    {
        ms_items = new HashSet<ms_items>();
    }

    [Key]
    public int pk_icat_id { get; set; }

    [Required]
    [StringLength(50)]
    public string icat_name { get; set; }

    [StringLength(50)]
    public string icat_image_path { get; set; }
}

It does not show some extended properties that I've added to table e.g. Description, etc

below script is to show those extended properties:

select p.*, t.*
from sys.extended_properties p
inner join sys.tables t on p.major_id = t.object_id
where class = 1

Adhik

View more on Stack Overflow

prime_z · Accepted Answer · 11/9/2019 5:31:21 PM

I've managed to retrieve my extended properties from the Db this way:

Add via Nuget Tiraggo library (Install-Package Tiraggo.Edmx). It brings access to some useful metadata in edmx file.

Add listed below references and namespaces to your *.tt file (some of them can be redundant for your purposes, but I needed all of them). I've added them between
<#@ include file="EF6.Utility.CS.ttinclude"#> and
<#@ output extension=".cs"#>

<#@ assembly name="$(ProjectDir)TiraggoEdmx\TiraggoEdmx.EF.dll" #> 
<#@ assembly name="System.Configuration" #>
<#@ import namespace="System.Data" #>
<#@ import namespace="System" #>
<#@ import namespace="System.Xml.Serialization" #>
<#@ import namespace="TiraggoEdmx_v3" #>
<#@ import namespace="System.Data.Entity" #>
<#@ import namespace="System.Configuration" #>
<#@ import namespace="System.Linq" #>

Then create an instance of db context with a proper connection string (you can do it somewhere near with instantiation of typeMapper, ef, code etc.)

var dbContext = new DbContext(yourConnectionString)
Using Tiraggo library create an instance of edmx object. (edmxLocation is a path string to edmx file)

Edmx edmx = Edmx.Load(edmxLocation);

Add static method to the end of *.tt file

<#+ public static string GetExtendedProperty(DbContext context, string schema, string tableName, string columnName, string propName) {
     var query = context.Database.SqlQuery<string>(string.Format("SELECT value FROM fn_listextendedproperty (NULL, 'schema', '{0}', 'table', '{1}', 'column', '{2}') where objname = '{2}' AND name = '{3}'", schema, tableName, columnName, propName));

     return query.FirstOrDefault();
}#>

So, this method will retrieve an extended property from a table's column by name.

Inside foreach loop create an info object to get metadata for a concrete entity

var entityInfo = new TiraggoEntityInfo(edmx, entity.FullName);

Retrieve an extended property value by calling a static method:

string extendedPropertyValue = GetExtendedProperty(dbContext, entityInfo.StorageInfo.Schema, entityInfo.StorageInfo.Name, edmProperty.Name, extendedPropName);

Here is my full template. I've removed some code and the template could be in an uncompilable state, but you can see the usage of code there.

P.S. Hope, it will help. Sorry for improper formatting

How to get extended properties of sql table in Entity framework (EF6)?

Question

ms_item_cat.cs

Fastest Entity Framework Extensions

Popular Answer

Performance

Expression Evaluator

Related