This post was most recently updated on September 30th, 2022.
< 1 min read.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!
He's been a developer from 2004 (starting with PHP and Java), and he's been working on .NET projects, Azure, Office 365, SharePoint and a lot of other stuff. He's also Microsoft MVP for Azure.
This is his personal professional (e.g. professional, but definitely personal) blog.
- “Performing cleanup” – Excel is stuck with an old, conflicted file and will never recover. - November 12, 2024
- How to add multiple app URIs for your Entra app registration? - November 5, 2024
- How to access Environment Secrets with GitHub Actions? - October 29, 2024
