10

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?
marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Ryan
  • 5,456
  • 25
  • 71
  • 129

2 Answers2

24

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());
Ladislav Mrnka
  • 360,892
  • 59
  • 660
  • 670
  • 5
    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? – CallMeLaNN Oct 19 '12 at 08:02
  • 6
    @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. – Ladislav Mrnka Oct 19 '12 at 08:07
  • Where to call `SetInitializer`? – Lei Yang Nov 19 '16 at 01:56
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))
            );
Sergey K
  • 4,071
  • 2
  • 23
  • 34