Unique multiple column in EF6 codefirst

Question

I have a class Email that looks like :

public class Email
{
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int Id { get; set; }
    public string Subject { get; set; }
    public string Body { get; set; }
    public string From { get; set; }
    public DateTime SentOn { get; set; }
    public List<string> To { get; set; }
}

To ensure uniqueness I made a compound key on Subject, From and SentOn

This created the problem that when Subject excess 128 characters, validation fails. So I just put a [MaxLength] attribute on it. But now it can't be a key column

What should I do? Is there a way to ensure uniqueness without being a key?

I can send two different emails with the same subject. If `SentOn` has a millisecond precision then `From` and `SentOn` should be enough. — Gert Arnold, Oct 17 '14 at 22:04

score 3 · Answer 1 · answered Oct 18 '14 at 11:14

3

If You are using EF 6.1, you can use Multiple-Column Indexes feature:

public class Email
{
   [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
   public int Id { get; set; }
   [Index("IX_EmailUniqueness", 1, IsUnique = true)]
   public string Subject { get; set; }
   public string Body { get; set; }
   [Index("IX_EmailUniqueness", 2, IsUnique = true)]
   public string From { get; set; }
   [Index("IX_EmailUniqueness", 3, IsUnique = true)]
   public DateTime SentOn { get; set; }
   public List<string> To { get; set; }
}

answered Oct 18 '14 at 11:14

Masoud

8,020
12
62
123

1

Did that in my case as well but when I generated the Initial configuration (Add-Migration) the Up() method of the migration generated an index for only the first column: .Index(t => t.XXXXX, unique=true, name: "ÏX_XXXX") – Lord of Scripts Jun 10 '15 at 23:00
1

When I did this on a property/column that was a string, EF gives the following error: Column 'Email' in table 'dbo.Patients' is of a type that is invalid for use as a key column in an index. – TravisO Jun 01 '17 at 12:36

score 0 · Answer 2 · edited May 23 '17 at 12:17

Check out this SO post here, you can add an index to ensure uniqueness, as either an attribute or using EF FluentAPI Setting unique Constraint with fluent API?

Note you have to be using EF6.1 or later. Here is the MSDN article

EDIT:

After checking around here and msdn a bit, and it looks like there is a limit on PK's and index keys being 900 bytes or less so you will want to use your identity as a key, and ensure uniqueness in another way.

For an example, I tried manually creating the subject column as unique with a length of 4000 and I got this error:

Warning! The maximum key length is 900 bytes. The index 'UQ__Emails__A2B1D9048E9A2A16' has maximum length of 8000 bytes. For some combination of large values, the insert/update operation will fail. and if you were to do the clustered key option, you would get this warning on creation (and I made the length 4000 on each column) Warning! The maximum key length is 900 bytes. The index 'PK_dbo.Emails' has maximum length of 16012 bytes. For some combination of large values, the insert/update operation will fail. which really means almost all real-world entries will fail.

So while you can manually get around the 128 length limit, it's not recommended and you will most likely get errors and lost data. and EF will only let you have a key length of 128 - not sure what it would do if you go behind it and alter that.

it still complaints about not being able to be be key column :( — WindowsMaker, Oct 17 '14 at 20:32
one option to ensure uniqueness would be to get the hash of the properties you want to stay unique and and use that as a unique column - or (way less performant) check the collection before storing. — tophallen, Oct 17 '14 at 21:48

Unique multiple column in EF6 codefirst

2 Answers2

Linked