CODEWITHSUNDEEP
entity-frameworkindexing
WhiskerBiscuit
WhiskerBiscuit

Reputation: 5157

How to ensure uniqueness for an integer and a string in EF?

I have an entity called Foobar and I want to add a constraint that ensures there can be no 2 duplicate tuples for ColA (int) and ColB (string), so if the data were added as follows, I would get a DB error on adding line 3.

ID   A  B
1    1 Happy
2    1 Sad
3    1 Happy

To accomplish this I tried adding Index IX_ColAColB. However I'm getting an error message

Message=Column 'ColB' in table 'dbo.Foobar' is of a type that is invalid for use as a key column in an index.

If I remove both index lines, the error goes away, but then I can't enforce uniquness on those two rows. I have experimented by changing ColB to integer, and then it works. Isn't there a way to ensure a string is unique in combination with a number?

public class Foobar
{
    [Key]
    [DatabaseGenerated(DatabaseGeneratedOption.Identity)]
    public int FoobarID { get; set; }

    [Index("IX_ColAColB", 1, IsUnique = true)]
    public int ColA { get; set; }
    [Index("IX_ColAColB", 2, IsUnique = true)]
    public string ColB { get; set; }
    ...
    ...

It turns out that ColB was nullable, but I still ge the same error if I add the [Required] annotation

Upvotes: 1

Views: 60

Answers (1)

ocuenca
ocuenca

Reputation: 39346

The max length for an index column is 900 bytes (check this link). To fix this try changing the max length to 20.

 [MaxLength(20),Index("IX_ColAColB", 2, IsUnique = true)]
 public string ColB { get; set; }

Upvotes: 1

Related Questions