kez kez - 2 months ago 36
C# Question

Define an Unique Key in Entity Framework code-first approch

I'm trying to define a unique key constraint in Entity Framework.

Unique key for

Last_Name
property in this
Author
table.

I'm using Entity Framework version 6.0.


  1. This answer lead me to that using Data Annotations

  2. This answer showing to do this using SQL command in context class



Trying to do this is easiest way. So I'm preferring Data Annotations, but I'm getting this error:


System.Data.SqlClient.SqlException: Column 'Last_Name' in table
'dbo.Authors' is of a type that is invalid for use as a key column in
an index.


Code:

public class Author
{
public Author()
{
}

[Key]
public int Auth_Id { get; set; }

public string First_Name { get; set; }

[Index("IX_FirstAndSecond", 1, IsUnique = true)]
public string Last_Name { get; set; }

public string Biography { get; set; }
}

Answer

Most likely, the problem lies in the fact that by default, EF code-first will make all string fields into VARCHAR(MAX) in the database - which cannot be indexed (since it's more than 900 bytes in size).

You probably only need to define a string length (which would be a good idea for all your string fields!):

public class Author
{
    public Author()
    {
    }

    [Key]
    public int Auth_Id { get; set; }

    [StringLength(100)]
    public string First_Name { get; set; }  

    [Index("IX_FirstAndSecond", 1, IsUnique = true)]
    [StringLength(100)]
    public string Last_Name { get; set; }

    public string Biography { get; set; }
}

So now your First_Name and Last_Name columns should be VARCHAR(100) in the database table, and the index should be applied just fine.