entity-framework-logo

Fixing the error: “Column XX in table dbo.YY is of a type that is invalid for use as a key column in an index.”

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 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 for something suitable for an actual key column!

This exception comes from the fact that indexes in EF have the 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 the 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!

The following two tabs change content below.

Antti K. Koskela

Solutions Architect / Escalations Engineer at Koskila / Norppandalotti Software / Valo Solutions
Antti Koskela is a proud digital native nomadic millenial full stack developer (is that enough funny buzzwords? That's definitely enough funny buzzwords!), who works as a Solutions Architect for Valo Intranet, the product that will make you fall in love with your intranet. Working with the global partner network, he's responsible for the success of Valo deployments happening all around the world. He's been a developer from 2004 (starting with PHP and Java), and he's been bending and twisting SharePoint into different shapes since MOSS. Nowadays he's not only working on SharePoint, but also on .NET projects, Azure, Office 365 and a lot of other stuff. This is his personal professional (e.g. professional, but definitely personal) blog.

2 thoughts on “Fixing the error: “Column XX in table dbo.YY is of a type that is invalid for use as a key column in an index.”

Let me know your thoughts!