Entity Framework - Unique constraint on a string column

Question

I'm a beginner with EF (6.2) and I'm trying to generate a database using the code first approach.

Some of my entities have a string property which should be unique, like the name of a user or the title of an article.

In order to ensure unicity, I've added an index which specifies that the column should be unique :

using System.Collections.Generic;
using System.ComponentModel.DataAnnotations;
using System.ComponentModel.DataAnnotations.Schema;

namespace Blog.Models
{
    public class User
    {
        [Key, Index, Required]
        public int Id { get; set; }

        [Index(IsUnique = true), Required]
        public string Name { get; set; }

        [Required]
        public string Password { get; set; }

        public ICollection<Comment> Comments { get; set; }
    }
}

However it complains because I'm trying to add an index on a string type column, while it accepts using an index on an integer column, such as the row ID.

I could use the Key attribute, but I'm already using it to define a primary key and I don't want EF to beleive that I want the name to be a component of a composite primary key nor to consider it as the actual primary key.

So my question is : Why can't I add an index on a column of string type and how can I ensure the unicity (uniqueness ?) of a given column ? Thank you.

Look at the data-type in the database. It's probably ntext, which is effectively an unbounded string, and can't be indexed. In sql server, at-least, there's a limit of ~900 bytes on an indexed field, and only certain types can be indexed. You'd probably want to go for an nvarchar(450) (or shorter, if you can afford). I believe there are attributes that can help here. [StringLength(450)] will do the trick. — spender
– spender, Commented Sep 7, 2018 at 12:31
Sorry, my bad, I googled with the wrong keywords. here is the solution : stackoverflow.com/questions/27687080/… — Virus721
– Virus721, Commented Sep 7, 2018 at 12:35
If Name is truly unique then it could be the primary key which would be a natural key and you wouldn't need the Id column unless it has some real life meaning (many people just slap Id columns on all their tables). — juharr
– juharr, Commented Sep 7, 2018 at 12:35
@juharr That seems legit, but I'm assuming it's faster to deal with raw integers than strings with it comes to the primary key. It also allows me to keep some kind of uniformity across all tables. — Virus721
– Virus721, Commented Sep 7, 2018 at 12:37

Vladel · Accepted Answer · 2018-09-07 13:23:27Z

2

As written in the Tutorial from MSDN, Indexes are possible on strings as they state in the following example code

public class User
    {
        public int UserId { get; set; }

        [Index(IsUnique = true)]
        [StringLength(200)]
        public string Username { get; set; }

        public string DisplayName { get; set; }
    }

but when it comes to Multiple-Column Indexes, they mention you need to write the order.

Probably there's a conflict arising because you use Index without any name on Id, and also an Index without any name on Name. Try defining a name for the Index of the Name. I am not sure if this works, but it's worth a try.

More info can be found here at the MSDN website.

answered Sep 7, 2018 at 13:23

Vladel

2401 gold badge5 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Virus721 Over a year ago

Thanks for your help. I already found the answer to this. See my comment below my question. I had to specify a length between 1 and 450 characters.

Collectives™ on Stack Overflow

Entity Framework - Unique constraint on a string column

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related