How to add MS_DESCRIPTION property to column in a M:N join table?

Question

Referencing back to my previous questions:

How do you add column description for a foreign key in another table utilizing EF 6?

and this previous post:

How to add description to columns in Entity Framework 4.3 code first using migrations?

Based on the above references, how can I insert an extended property (e.g. MS_Description) for a many-many relationship where EF 6 will automatically create a join table behind the scene?

FYI, more reference materials for you:

Towards the Self-Documenting SQL Server Database

SQL Server extended properties

sp_addextendedproperty (Transact-SQL)

score 1 · Accepted Answer · edited Nov 30 '20 at 05:35

Ok, yes it is possible through migrations. This was definitely an interesting dive into EF's workings as Migrations are something I tend to avoid.

The first clue was here: (https://karatejb.blogspot.com/2018/09/entity-framework-6-code-first-6-add-or.html) using EF to read and write the MS_Description. From there it is a case of enabling this for migrations.

For a DbContext containing two entities, Fidget and Spinner, where each is a many-to-many where I want a FidgetSpinner table generated by EF:

[Table("Fidgets")]
public class Fidget
{
    [Key, DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int FidgeId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Spinner> Spinners { get; set; } = new List<Spinner>();
}

[Table("Spinners")]
public class Spinner
{
    [Key]
    public int SpinnerId { get; set; }
    public string Name { get; set; }

    public virtual ICollection<Fidget> Fidgets { get; set; } = new List<Fidget>();
}

Then the mapping to nominate my FidgetSpinner table:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        modelBuilder.Entity<Fidget>()
            .HasMany(x => x.Spinners)
            .WithMany(x => x.Fidgets)
            .Map(x =>
            {
                x.MapLeftKey("FidgetId"); 
                x.MapRightKey("SpinnerId"); 
                x.ToTable("FidgetSpinners"); 
            });
    }

Pretty straight forward, but we want to add Descriptions to the FidgetId and SpinnerId columns on this generated table. The solution that worked was to leverage Table Annotations and Migrations:

    protected override void OnModelCreating(DbModelBuilder modelBuilder)
    {
        base.OnModelCreating(modelBuilder);

        string descriptions = JsonConvert.SerializeObject(new[]
        {
            new KeyValuePair<string, string>("SpinnerId", "FK Reference to a Spinner"),
            new KeyValuePair<string, string>("FidgetId", "FK Reference to a Fidget")
        });

        modelBuilder.Entity<Fidget>()
            .HasMany(x => x.Spinners)
            .WithMany(x => x.Fidgets)
            .Map(x =>
            {
                x.MapLeftKey("FidgetId"); 
                x.MapRightKey("SpinnerId"); 
                x.ToTable("FidgetSpinners"); 
                x.HasTableAnnotation("Descriptions", descriptions);
            });
    }

Here I'm appending my annotations as a JSON string containing KeyValuePairs for the column name and description I want to add. This could just as easily be a custom type container. Whatever you send has to be serializable to a string however.

The next thing will be to create an SQL Generator that will respond to Create Table operations and check for our Description Table annotations, then for any matching columns, append their descriptions using sp_addextendedproperty:

public class DescriptionMigrationSqlGenerator : SqlServerMigrationSqlGenerator
{
    protected override void Generate(CreateTableOperation createTableOperation)
    {
        base.Generate(createTableOperation);
        if (createTableOperation.Annotations.TryGetValue("Descriptions", out object descriptionData))
        {
            try
            {
                var descriptionValues = JsonConvert.DeserializeObject<KeyValuePair<string, string>[]>(descriptionData.ToString());

                foreach (var descriptionValue in descriptionValues)
                {
                    var column = createTableOperation.Columns.SingleOrDefault(x => x.Name.ToLower() == descriptionValue.Key.ToLower());
                    if (column != null)
                    {
                        var tableNameParts = createTableOperation.Name.Split('.');
                        var schemaName = tableNameParts.First();
                        var tableName = tableNameParts.Last();
                        var statement = string.Format(@"EXEC sp_addextendedproperty @name=N'MS_Description', @value = N'{0}', @level0type=N'Schema', @level0name= {1}, @level1type=N'Table', @level1name={2}, @level2type=N'Column', @level2name={3}",
                            descriptionValue.Value, schemaName, tableName, column.Name);
                        Statement(statement);
                    }
                }
            }
            catch { } // TODO: Handle situation where invalid description annotation provided. (I.e. not provided collection of key value pairs...
        }
    }
}

This is a template only and should extend exception handling and such for handling invalid values or cases where a non-existent column is requested. (currently ignored) The createTableOperation.Name includes the schema name so we split that to get the schema and table names for the stored procedure call (Really rough, some better checking is advisable for this).

The last step is to register this SQL generator with a migrations configuration:

internal sealed class Configuration : DbMigrationsConfiguration<TestDbContext>
{
    public Configuration()
    {
        AutomaticMigrationsEnabled = true;
        SetSqlGenerator("System.Data.SqlClient", new DescriptionMigrationSqlGenerator());
    }
}

This assumes an automatic generation, alternatively you can set it up for manual generation.

Now when the migrations create the joining table that we annotated with the descriptions, the descriptions we requested are added to the DB columns.

score 0 · Answer 2 · answered Nov 16 '20 at 09:37

Thanks to Steve Py's answer, it gave me the idea to create my own attributes and to combine with the previous solution from this question.

Previous solution works for 1:1 easily and requires a fully defined relationship (see here) for 1:n relationships. Since EF 6 automatically creates the join-table for m:n relationships, the solution will throw an exception stating that the foreign key column does not exist since it is looking for the wrong table.

The solution I have implemented on my side is another variation of the given solution using custom attributes to define the description and also the extra need to define the foreign key and join table for m:n relationships.

I believe there are more elegant answers out there but till its published, I am implementing the code below.

Below is the custom attributes that I have created:

public class CustomTblDesc : Attribute
{
    private string desc = "";

    public CustomTblDesc(string description)
    {
        desc = description;
    }

    public string Description { get { return desc; } }
}

public class CustomColDesc : Attribute
{
    private string desc = "";
    private string table = "";
    private string propName = "";

    public CustomColDesc(string description)
    {
        desc = description;
    }

    public CustomColDesc(string description, string tableName)
    {
        desc = description;
        table = tableName;
    }

    public CustomColDesc(string description, string tableName, string linkedTablePropertyName)
    {
        desc = description;
        table = tableName;
        propName = linkedTablePropertyName;
    }

    public string Description { get { return desc; } }
    public string LinkedTable { get { return table; } }
    public string LinkedTablePropertyName { get { return propName; }}
}

Below is the code that I have modified from the solution of this question:

private void SetTableDescriptions(Type tableType) { string tableName = tableType.Name;

    // -- Set table description

    CustomTblDesc tblDesc = null;

    var custDescs = tableType.GetCustomAttributes(typeof(CustomTblDesc), false);
    if (custDescs != null && custDescs.Length > 0)
    {
        tblDesc = custDescs[0] as CustomTblDesc;
        SetTableDescription(tableName, tblDesc.Description);
    }

    // -- Set column description

    foreach (var prop in tableType.GetProperties(System.Reflection.BindingFlags.DeclaredOnly | System.Reflection.BindingFlags.Public | System.Reflection.BindingFlags.Instance))
    {
        if (prop.PropertyType.IsClass && prop.PropertyType != typeof(string)) 
            continue;
        else
        {
            CustomColDesc colDesc = null;

            custDescs = prop.GetCustomAttributes(typeof(CustomColDesc), false);
            if (custDescs != null && custDescs.Length > 0)
            {
                colDesc = custDescs[0] as CustomColDesc;
             
                if (string.IsNullOrEmpty(colDesc.LinkedTable))
                    SetColumnDescription(tableName, prop.Name, colDesc.Description);
                else
                    SetColumnDescription(colDesc.LinkedTable, colDesc.LinkedTablePropertyName, colDesc.Description);
            }
        }
    }
}

How to add MS_DESCRIPTION property to column in a M:N join table?

2 Answers2