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

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


I'm using VS 2013 to work. I includedADO.NET Entity Data Model containing:

  1. Database EF Designer,
  2. First Code From Database

It generated data when connected to several of the tables in my Add annotations to every table field in the following file with partial class: as follows


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

    public int pk_icat_id { get; set; }

    public string icat_name { get; set; }

    public string icat_image_path { get; set; }

Some extended properties that I added to the table, such as Description, etc., are not displayed.

The script following will display 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
12/3/2014 6:54:49 AM

Popular Answer

This is how I was able to get my extended properties from the database:

  1. Tiraggo library addition through NugetInstall-Package Tiraggo.Edmx It gives users access to some helpful metadata included in edmx files.
  2. Add the references and namespaces indicated below to your *.tt file (some of them can be redundant for your purposes, but I needed all of them). I sandwiched them in 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" #>
  3. Then, build a db context object with a suitable connection string (you can do this very close to the creation oftypeMapper , ef , code etc.)

    var dbContext = new DbContext(yourConnectionString)

  4. To build an instance of an edmx object, use the Tiraggo library. (The path string edmxLocation points toedmx file)

    Edmx edmx = Edmx.Load(edmxLocation);

  5. The *.tt file should have a static method added.

    <#+ 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();

Therefore, this method will use a table's column's name to obtain an extended property.

  1. Inside foreach In order to obtain metadata for a specific entity, loop build an info object.

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

  2. Utilize a static method to get the value of an extended property:

    string extendedPropertyValue = GetExtendedProperty(dbContext, entityInfo.StorageInfo.Schema, entityInfo.StorageInfo.Name, edmProperty.Name, extendedPropName);
  3. This is my complete template. You can still see the use of code in the template even though I may have removed some code and made it incompilable.

PS: I hope it will be useful. I apologize for the poor formatting.

11/9/2019 5:31:21 PM

Related Questions


Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow