I have a table Person: id, name
I often have queries like:
select * from Person where name Like "%abc%".
I have 2 questions:
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());
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?
Up method in your migration.
SetInitializer?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))
);
