Adding an index to a table

Question

I have a table Person : id, name

I often get queries like:

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

I have 2 questions:

• How to implement this request using code-first 5 (CTP5)
• How do I add an index to a name column to speed up data retrieval based on a name like a query?

Answer 1

A similar statement can be performed using the Contains function:

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

An index must be added by SQL - there is no special construct in EF to create an index. You can execute this SQL with DB initialization.

First you must implement a 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 a new initializer:

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

Answer 2

in EF 6 you can create such indexes

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