In EF Code First is there a data annotation for description field?

Question

While designing a table in MS-SQL you have a choice to add a description for each column you add to the table. Is it possible in EF Code First to do same with help of Data Annotations?

Answer 1

Before you are using my soultion please read a little bit about:

Extended Properties:
https://technet.microsoft.com/en-us/library/ms190243(v=sql.105).aspx

Attributes:
http://www.dotnetperls.com/attribute

My solution will allow you to do a shadow description for any property like that:

[Description("My Column description!")]
public string RegionCity { get; set; }

Notes:

• Copy paste the soultion and everything should works (donnot forget the app.config connection string)!
• You can use the sql command IF NOT EXISTS to check if the extended property already exist or not.

• You can build your own generic logic or C# extension to read the descirption from any property this should be easy a just have added a an example with string.

  using System;
  using System.Linq;
  
  namespace EntityFrameworkDemo
  {
   using System.Data.Entity;
  
  public class Program
  {
      public static void Main()
      {
          Database.SetInitializer(new DropCreateDatabaseAlways<MyDbContext>());
  
  
          using (var myDbContext = new MyDbContext("DefaultConnection"))
          {
              // Maybe you do not need this line!
              myDbContext.Database.Initialize(true);
  
              // Adding one time or use the IF NOT EXISTS SQL Command!
              var c = new ColumnsDescription();
              c.AddColumnsDescriptions(myDbContext);
  
              var region = new Region { RegionCity = "Test 1", RegionSeconcdCity = "Test2" };
              myDbContext.Regions.Add(region);
              myDbContext.SaveChanges();
          }
  
  
          // Reading the extended properties
          using (var myDbContext = new MyDbContext("DefaultConnection"))
          {
              var ep = "select value from fn_listextendedproperty('MS_Description','schema','dbo','table', 'Regions', 'column', 'RegionCity');";
  
              // For example you can read your extend property like following or you make a generic reader
              var properties = myDbContext.Database.SqlQuery<string>(ep).First();
  
              Console.WriteLine(properties);
          }
      }
  
      [AttributeUsage(AttributeTargets.Property)]
      public class DescriptionAttribute : Attribute
      {
          string value;
  
          public DescriptionAttribute(string id)
          {
              this.value = id;
          }
  
          public string Value
          {
              get { return this.value; }
          }
      }
  
      public class ColumnsDescription
      {
          public void AddColumnsDescriptions(DbContext mydbContext)
          {
              // Fetch all the DbContext class public properties which contains your attributes
              var dbContextProperies = typeof(DbContext).GetProperties().Select(pi => pi.Name).ToList();
  
              // Loop each DbSets of type T
              foreach (var item in typeof(MyDbContext).GetProperties()
                  .Where(p => dbContextProperies.IndexOf(p.Name) < 0)
                  .Select(p => p))
              {
                  if (!item.PropertyType.GetGenericArguments().Any())
                  {
                      continue;
                  }
  
                  // Fetch the type of "T"
                  var entityModelType = item.PropertyType.GetGenericArguments()[0];
                  var descriptionInfos = from prop in entityModelType.GetProperties()
                                         where prop.GetCustomAttributes(typeof(DescriptionAttribute), true).Any()
                                         select new { ColumnName = prop.Name, Attributes = prop.CustomAttributes };
  
                  foreach (var descriptionInfo in descriptionInfos)
                  {
                      // Sql to create the description column and adding 
                      var addDiscriptionColumnSql =
                          @"sp_addextendedproperty  @name = N'MS_Description', @value = '"
                          + descriptionInfo.Attributes.First().ConstructorArguments.First()
                          + @"', @level0type = N'Schema', @level0name = dbo,  @level1type = N'Table',  @level1name = "
                          + entityModelType.Name + "s" + ", @level2type = N'Column', @level2name ="
                          + descriptionInfo.ColumnName;
  
                      var sqlCommandResult = mydbContext.Database.ExecuteSqlCommand(addDiscriptionColumnSql);
                  }
              }
          }
      }
  
      public class Region
      {
          public int Id { get; set; }
  
          [Description("My Column description!")]
          public string RegionCity { get; set; }
  
          [Description("My Second Column description!")]
          public string RegionSeconcdCity { get; set; }
  
      }
  
      public class MyDbContext : DbContext
      {
          public DbSet<Region> Regions { get; set; }
  
          public MyDbContext(string connectionString)
              : base("name=" + connectionString)
          {
          }
      }
  }
  }