Adding index to a table

Question

I have a table Person: id, name

I often have queries like:

select * from Person where name Like "%abc%".

I have 2 questions:

1. How do I implement this query using code-first 5 (CTP5)
2. How do I add an index on the name column to make data retrieval faster based on name like in the query?

Undermost

Answer 1

Like operator can be performed with Contains function:

var query = from p in context.Persons
            where p.Name.Contains("abc")
            select p;

Index must be added by SQL - there is no special construction in EF to create index. You can execute this SQL from DB initialization.

First you must implement custom initializer:

public class MyInitializer : CreateDatabaseIfNotExists<MyContext>
{
  protected override void Seed(MyContext context)
  {
    context.Database.SqlCommand("CREATE INDEX IX_Person_Name ON Person (Name)");
  }
}

Then you must register new initializer:

DbDatabase.SetInitializer<MyContext>(new MyInitializer());

Oilcan

I will expect to use Seed() for seeding data and I think the sql command should be performed in DbMigration.Up(). I try to access dbContext in the Up() method but no luck, but I found there is DbMigration.Sql(). Seems like in my Up(), I can call this.Sql("CREATE INDEX IX_Person_Name ON Person (Name)"). am I in a right track? – Eph

@CallMeLaNN: Yes. This question was answered long time prior migrations were released. When using migrations you should place the SQL command to Up method in your migration. – Oilcan

Where to call SetInitializer? – Schoonmaker

Answer 2

in EF 6 you can create indexes like this

   Property(t => t.TotalValue)
            .HasColumnAnnotation(
                IndexAnnotation.AnnotationName,
                new IndexAnnotation(new IndexAttribute("IX_inventory_import_total_value", 1))
            );

Warnerwarning

Does it suport multiple columnt? – Kestrel