Adding An Index On A Column In EF6 (Entity Framework 6.1+)

Seekers,

This was way harder to find than it should have been. It seems that the EntityFramework project changed an attribute name from “HasAnnotation” to “HasColumnAnnotation“.I know this only because of this obscure API review mention at the bottom of the page.

All the examples out there at the time of this writing have HasAnnotation examples, and of course they all fail with no idea why. Wonderful. Add to this, if you want to create an index on a colum, no matter what anyone else tells you, decorating the POCO property with “<Index>” doesn’t work. Intellisense won’t even let you.

A bit more digging with the new name, and I found the gold…

I have 2 columns in a table that are Long types, and need to be indexed. Since searches will use either or even both, I decided to try creating a single index on both columns!

Crazy!

Maybe, but certainly not hard if you have this in your entities class (the one that inherits DBContext):

note: The Long columns are just the Long version of the StartIpAddress and EndIpAddress columns, which define an IP range.

Really easy to understand. Now to create the scaffold that we’ll apply to the DB…

Remember, in true Code First (CF) fashion, we have not done anything in the database, and here we’re not even editing the POCO class.  So what does the scaffold code look like to create this index on both columns?

Not bad. It’s the code we’d do if we were doing things with DB First (the hard way, as we call it)…

Let’s commit to DB:

And that makes our table look like this change from the “Script index as Create To“:

Don’t you just hate it when things are hard to find (the right way to do), and simple to implement? Me, too!

pat
:)

Leave a Reply

Your email address will not be published. Required fields are marked *