While using Entity Framework and code-first migrations, Entity Framework (EF) creates the indexes for you - but what if you need to create a custom one, explicitly based on a certain field/column? Then you'll have to tell EF which one to use as a key column. Usually, it's easy - you just add the following annotation to the columns you'll be using:
[Index("OfficialIds", 1)]
public int AreaId { get; set; }
[Index("OfficialIds", 2)]
public string EstateId { get; set; }
(example stripped of extra code and other columns for clarity)
And after adding the migration (Add-Migration... commandlet) you get something like this:
CreateIndex("dbo.Areas", new[] { "AreaId", "EstateId" }, name: "OfficialIds");
Okay, nice. Don't have to create the indexes myself either, so that's neat with EF.
But what if, when running Update-Database, you get an error like:
Column XX in table dbo.YY is of a type that is invalid for use as a key column in an index.
There's a quick and simple solution.
Solution: limit the length of your VARCHARs to something suitable for an actual key column!
This exception comes from the fact that indexes in EF have a maximum length of 900 chars. If you are like me, you'll have been a little lazy and you have created your string-typed model variables without specifying a maximum length for them, and this causes the length of the index values to be way too long.
In my case, adding these attributes to the column's definition in the model file fixed the issue:
[Index("OfficialIds", 1)]
public int AreaId { get; set; }
[Column(TypeName = "VARCHAR")]
[StringLength(50)]
[Index("OfficialIds", 2)]
public string EstateId { get; set; }
Luckily, that was easy :) Hope this helps!
Comments