As I blogged about this before, I continue working on my Entity Framework Code First extras project on CodePlex.  Today I added support for indexes to the functionality of the project.  I bumped up the version to 0.9.4.  I wanted an ability to declaratively express indexes on tables via attributes.  So, I added a new attribute to support that:

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
 
namespace EFUtil.Core.Indexes
{
           /// <summary>
           /// Used to specify an index for a column
           /// </summary>
           [AttributeUsage(AttributeTargets.Property, AllowMultiple = true)]
           public class IndexedAttribute : Attribute
           {
                      /// <summary>
                      /// Create new instance of the indexed attribute
                      /// </summary>
                      /// <param name="indexName">Name of the index</param>
                      /// <param name="ordinalPosition">Position of the column in an index</param>
                      /// <param name="columnName">Column name for the index</param>
                      /// <param name="direction">Direction of the column sorting in an index</param>
                      /// <param name="tableName">Table name for the index</param>
                      public IndexedAttribute(
                                 string indexName,
                                 int ordinalPosition = 0,
                                 string columnName = "",
                                 IndexDirection direction = IndexDirection.Ascending,
                                 string tableName = "")
                      {
                                 IndexName = indexName;
                                 OrdinalPoistion = ordinalPosition;
                                 TableName = tableName;
                                 ColumnName = columnName;
                                 Direction = direction;
                      }
 
                      /// <summary>
                      /// Position of the column in an index
                      /// </summary>
                      public int OrdinalPoistion { get; private set; }
 
                      /// <summary>
                      /// Direction of the column sorting in an index
                      /// </summary>
                      public IndexDirection Direction { get; private set; }
                      /// <summary>
                      /// Table name for the index
                      /// </summary>
                      public string TableName { get; private set; }
 
                      /// <summary>
                      /// Column name for the index
                      /// </summary>
                      public string ColumnName { get; private set; }
 
                      /// <summary>
                      /// Name of the index
                      /// </summary>
                      public string IndexName { get; private set; }
           }
}

This attribute allows you specify all index properties, such as name, direction (ascending or descending) and a column’s ordinal position in the index.  Here is sample usage in a table (class):

using System;
using EFUtil.Core.Defaults;
using EFUtil.Core.Indexes;
 
namespace EFUtil.TestApplication
{
           public class Product
           {
                      public int ProductId { get; set; }
                      [Indexed("Main", 0)]
                      public string ProductNumber { get; set; }
                      [Indexed("Main", 1)]
                      [Indexed("Second", direction: IndexDirection.Ascending)]
                      [Indexed("Third", direction: IndexDirection.Ascending)]
                      public string ProductName { get; set; }
                      public string Instructions { get; set; }
                      [Indexed("Third", 1, direction: IndexDirection.Descending)]
                      public bool IsActive { get; set; }
                      [Default("0")]
                      public decimal? Price { get; set; }
                      [Default("GetDate()")]
                      public DateTime? DateAdded { get; set; }
                      [Default("20")]
                      public int Count { get; set; }

           }
}
 

As you can see above, I declare a number of indexes, where index called Third has two columns – ProductName and IsActive in that exact order and different directions.  This combination will translate in the following:

CREATE NONCLUSTERED INDEX [Third] ON [dbo].[Products]

(

      [ProductName] ASC,

      [IsActive] DESC

)

I am trying with this implementation to get even further in eliminating a need to use scripting or separate database project to maintain the information about the database when using Code First.

Please let me know if you have any questions.

Thanks.