Code First and Indexes

Yeah yeah, Code First is great (supposedly).

Except that it seems to leave out a key piece of the data puzzle: indexes.

I ran into this today on an old side project I worked on last year. Someone else had developed a module in the application based on Code First.

There were about 8 classes that were hierarchical in nature: one main table with a few related "child" tables, some of which also had child tables. Code First created the database schema on any customer sites where we installed this new module.

In the service layer (Entity Framework with RIA called from a Silverlight app), the developer added a complicated LINQ query to get records "published" within a certain date/time range (the table has a Published datetime column). Worked great when there were only a few dozen records in the database.

Fast forward a few months, there are now several thousand records in the main table, and even more in the details tables...

The date range query no longer works: we get command timeouts. Oh sure, I could change the code in the service layer to add

ObjectContext.CommandTimeout = 300;

but that would only mask the real problem (with the query itself) and would require us to redeploy the service layer assemblies to all of the sites where this is deployed. And, by the, there doesn't seem to be a way to set the EF CommandTimeout via the web.config file (at least for SQL Server contexts).

The actual solution? Add an index to the Published column on the main table.

The LINQ query goes from taking more than a minute to run to around 3 seconds. 

As far as I can tell, there's no way to instruct Code First to create an index on a property in your model.

Several possible ways around this are given here (the best being the addition of the new Attribute class):

http://stackoverflow.com/questions/8262590/entity-framework-code-first-fluent-api-adding-indexes-to-columns

And it does appear that this is on Microsoft's radar, based on this entry on CodePlex relative to Fluent API:

http://entityframework.codeplex.com/workitem/list/basic?keywords=DevDiv%20%5BId=87553%5D



UPDATE: According to a comment left by John Davidson on my original Google+ post, indexes are available in Entity Framework version 4.3+