I'm running EF 4.2 CF and want to create indexes on certain columns in my POCO objects.
As an example lets say we have this employee class:
public class Employee
{
public int EmployeeID { get; set; }
public string EmployeeCode { get; set; }
public string FirstName { get; set; }
public string LastName { get; set; }
public DateTime HireDate { get; set; }
}
We often do searches for employees by their EmployeeCode and since there are a lot of employees it would be nice to have that indexed for performance reasons.
Can we do this with fluent api somehow? or perhaps data annotations?
I know it is possible to execute sql commands something like this:
context.Database.ExecuteSqlCommand("CREATE INDEX IX_NAME ON ...");
I would very much like to avoid raw SQL like that.
i know this does not exist but looking for something along those lines:
class EmployeeConfiguration : EntityTypeConfiguration<Employee>
{
internal EmployeeConfiguration()
{
this.HasIndex(e => e.EmployeeCode)
.HasIndex(e => e.FirstName)
.HasIndex(e => e.LastName);
}
}
or maybe using System.ComponentModel.DataAnnotations
the POCO could look like this (again i know this does not exist):
public class Employee
{
public int EmployeeID { get; set; }
[Indexed]
public string EmployeeCode { get; set; }
[Indexed]
public string FirstName { get; set; }
[Indexed]
public string LastName { get; set; }
public DateTime HireDate { get; set; }
}
Anyone have any ideas on how to do this, or if there are any plans to implement a way to do this, the code first way?
UPDATE: As mentioned in the answer by Robba, this feature is implemented in EF version 6.1
After Migrations was introduced in EF 4.3 you can now add indexes when modifying or creating a table. Here is an excerpt from the EF 4.3 Code-Based Migrations Walkthrough from the ADO.NET team blog
namespace MigrationsCodeDemo.Migrations
{
using System.Data.Entity.Migrations;
public partial class AddPostClass : DbMigration
{
public override void Up()
{
CreateTable(
"Posts",
c => new
{
PostId = c.Int(nullable: false, identity: true),
Title = c.String(maxLength: 200),
Content = c.String(),
BlogId = c.Int(nullable: false),
})
.PrimaryKey(t => t.PostId)
.ForeignKey("Blogs", t => t.BlogId, cascadeDelete: true)
.Index(t => t.BlogId)
.Index(p => p.Title, unique: true);
AddColumn("Blogs", "Rating", c => c.Int(nullable: false, defaultValue: 3));
}
public override void Down()
{
DropIndex("Posts", new[] { "BlogId" });
DropForeignKey("Posts", "BlogId", "Blogs");
DropColumn("Blogs", "Rating");
DropTable("Posts");
}
}
}
This is a nice strongly typed way to add the indexes, which was what i was looking for when i first posted the question.