1

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; }
}
Community
  • 1
  • 1
kez
  • 2,273
  • 9
  • 64
  • 123
  • You may want to rethink that scheme. Why would lastname be unique there can be multiple authors with the same last name – Ronan Sep 13 '16 at 18:26
  • It's an **author** - not an "auther" .... – marc_s Sep 13 '16 at 18:28
  • @Ronan Actually this came from one of my interview test, they asked me to define `Last_Name` property with unique key constrains – kez Sep 13 '16 at 18:29
  • Find another company where to work ;) – InBetween Sep 13 '16 at 18:29
  • @InBetween ha ha :D – kez Sep 13 '16 at 18:30
  • I believe in an interview you should question the interviewer about decisions that are wrong, it looks better on you that you actually think about design and are not just another code monkey – Ronan Sep 13 '16 at 18:32
  • @Ronan yp youre correct , this one was a practical test, they had VS 2013 , so above data annotation not working with older EF version so had to skip unique key part :( – kez Sep 13 '16 at 18:38

1 Answers1

1

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.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
  • Do i need to add the `[Key]` Data annotation above `Last_Name` property ? – kez Sep 13 '16 at 18:33
  • @kez: **no** - the `ID` already is your (primary) key... – marc_s Sep 13 '16 at 18:33
  • what the approach you suggesting for older EF versions ? – kez Sep 13 '16 at 18:39
  • @kez: older versions of EF didn't typically do code-first, and didn't have this issue. If you have and use code-first with older versions - use the same approach, should work just fine too – marc_s Sep 13 '16 at 18:40
  • is it , I think I might missed `using System.ComponentModel.DataAnnotations.Schema;` namespace :( – kez Sep 13 '16 at 18:44