# Tuesday, January 17, 2012

Generating an index in the database using Entity Framework Code First

If you’re using Entity Framework Code First, then you may want to create an index on some of your table. The way to do this is to call “Database.ExecuteSqlCommand” in the “Seed” method of your database initializer. The sample below shows how it is done (thanks to Rolf for pointing me in the right direction):

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.ComponentModel.DataAnnotations;
 
namespace CodeFirstPlayground
{
  class Program
  {
    static void Main( string[] args )
    {
      Database.SetInitializer<CodeFirstSampleModel>( new CodeFirstSampleDbInitializer() );
 
      using ( var model = new CodeFirstSampleModel() )
      {
        var query = from c in model.Customers
                    where c.Name != null
                    select c;
 
        foreach ( var item in query )
        {
          Console.WriteLine( item.Name );
        }
      }
 
      Console.ReadLine();
 
    }
  }
 
  public class CodeFirstSampleModel : DbContext
  {
    public CodeFirstSampleModel()
      : base( "CodeFirstSampleDB" )
    {
      Customers = this.Set<Customer>();
    }
 
    public DbSet<Customer> Customers { get; set; }
 
  }
 
  public class CodeFirstSampleDbInitializer : DropCreateDatabaseAlways<CodeFirstSampleModel>
  {
    protected override void Seed( CodeFirstSampleModel context )
    {
      context.Database.ExecuteSqlCommand( "CREATE INDEX IX_Customer_Name ON Customers (Name) " );
      
      Customer c = new Customer()
      {
        Name = "Mark",
        LastOrder = DateTime.Now
      };
 
      context.Customers.Add( c );
 
      base.Seed( context );
    }
  }
 
 
  public class Customer
  {
    [Key]
    public int Id { get; set; }
    [Required]
    [MaxLength( 50, ErrorMessage = "Customer name must be 50 characters or less." )]
    public string Name { get; set; }
    
    public DateTime? LastOrder { get; set; }
  }
}
#    Comments [3] |