Extended Property Names Feature

This feature enhancement allows you to access database extended properties by their name in addition to their value. Previously, only the extended property value was available as a string in column.ExtendedProperty. Now you can access individual extended properties by name using the new column.ExtendedProperties dictionary.

How to Use

SQL Server Setup

First, add extended properties to your database column:

-- Add a JsonPropertyName extended property to a column
EXEC sp_addextendedproperty 
    @name = N'JsonPropertyName', 
    @value = N'id', 
    @level0type = N'SCHEMA', 
    @level0name = N'dbo', 
    @level1type = N'TABLE', 
    @level1name = N'YourTable', 
    @level2type = N'COLUMN', 
    @level2name = N'SystemId'

-- You can add multiple extended properties to the same column
EXEC sp_addextendedproperty 
    @name = N'CustomValidator', 
    @value = N'EmailValidator', 
    @level0type = N'SCHEMA', 
    @level0name = N'dbo', 
    @level1type = N'TABLE', 
    @level1name = N'YourTable', 
    @level2type = N'COLUMN', 
    @level2name = N'EmailAddress'

Code Generation Setup

In your Database.tt, use the UpdateColumn delegate. Settings.ApplyJsonPropertyNameAttribute is the built-in helper that turns a JsonPropertyName extended property into an attribute; the other Apply* helpers are unrelated to extended properties but are shown because the shipped template calls all four together:

Settings.UpdateColumn = delegate (Column column, Table table, List<EnumDefinition> enumDefinitions, List<JsonColumnMapping> jsonColumnMappings)
{
    // Your own rules go here, reading column.ExtendedProperties

    Settings.ApplyJsonPropertyNameAttribute(column);
    Settings.ApplyJsonColumnMappings(column, table, jsonColumnMappings);
    Settings.ApplyDataAnnotations(column);
    Settings.ApplyEnumTypeReplacement(column, table, enumDefinitions);
};

Generated Code Example

Given the SQL setup above, the generated code will look like:

public class YourTable
{
    [JsonPropertyName("id")]
    public string SystemId { get; set; }

    [CustomValidator(typeof(EmailValidator))]
    public string EmailAddress { get; set; }
}

Supported Databases

Database	Extended Property Name Support	Notes
SQL Server	✅ Yes	Reads real extended property names from sys.extended_properties. Azure SQL Database does not report extended properties, so this read is skipped there
PostgreSQL	⚠️ Placeholder	pg_description comments, read under the fixed name Comment
MySQL	⚠️ Placeholder	COLUMN_COMMENT and TABLE_COMMENT, read under the fixed name Comment
Oracle	⚠️ Placeholder	ALL_COL_COMMENTS and ALL_TAB_COMMENTS, read under the fixed name Comment
SQLite	❌ No	SQLite stores no column comments at all

Only SQL Server has genuinely named properties. On the other three, everything arrives in column.ExtendedProperties["Comment"], because those databases have one comment per object and no name to give it. column.ExtendedProperty (the plain string) is populated everywhere the dictionary is.

Use Cases

1. JSON Property Mapping: Map database column names to different JSON property names
2. Custom Validation: Specify validators via extended properties
3. API Visibility: Control which properties appear in APIs
4. Documentation: Store property-specific documentation by category
5. Feature Flags: Enable/disable features per column
6. Custom Attributes: Add any custom attributes based on extended properties

Example: Multiple Extended Properties on One Column

-- Add multiple extended properties to one column
EXEC sp_addextendedproperty @name = N'JsonPropertyName', @value = N'userId', 
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE', @level1name = N'Orders', 
    @level2type = N'COLUMN', @level2name = N'UserId'

EXEC sp_addextendedproperty @name = N'Validator', @value = N'Required|Guid', 
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE', @level1name = N'Orders', 
    @level2type = N'COLUMN', @level2name = N'UserId'

EXEC sp_addextendedproperty @name = N'Description', @value = N'The unique identifier for the user', 
    @level0type = N'SCHEMA', @level0name = N'dbo', 
    @level1type = N'TABLE', @level1name = N'Orders', 
    @level2type = N'COLUMN', @level2name = N'UserId'

Settings.UpdateColumn = delegate (Column column, Table table, List<EnumDefinition> enumDefinitions, List<JsonColumnMapping> jsonColumnMappings)
{
    // Access all three extended properties
    if (column.ExtendedProperties.ContainsKey("JsonPropertyName"))
        column.Attributes.Add($"[JsonPropertyName(\"{column.ExtendedProperties["JsonPropertyName"]}\")]");
    
    if (column.ExtendedProperties.ContainsKey("Validator"))
    {
        var validators = column.ExtendedProperties["Validator"].Split('|');
        foreach (var validator in validators)
            column.Attributes.Add($"[{validator}]");
    }
    
    if (column.ExtendedProperties.ContainsKey("Description"))
        column.SummaryComments = column.ExtendedProperties["Description"];
};

Notes

• Extended property names are case-insensitive (the dictionary uses StringComparer.OrdinalIgnoreCase)
• Where a database has no named extended properties, the fixed name Comment is used - see the table above
• Property names are trimmed of whitespace
• column.ExtendedProperty (the plain string) still holds the value and is unchanged, so anything you wrote against it keeps working
• The reading itself happens in the efrpg dotnet tool, not in the T4 template. The values reach your callbacks the same way regardless