MVC, Model, Index DataAnnotation issue.

[BrailleSchool]

Hi, guys,

I have a problem that I have not been able to figure out.
In some of my MSSQL columns, I am requiring that the information inserted is unique. Such as ITIN or SSN, FEIN etc.
The values are string, so they are automatically nullable based on my research.
I am using the [Index] DataAnnotation on these columns to ensure that the values inserted are unique.

However, my problem is that because I have the [Index] DataAnnotation in use on the model. If I register an account in the MVC Web App for the first time and don't specify a value for ITIN, SSN or FEIN, the first registration succeeds. If I register subsequent accounts, I get a run-time error advising can't insert a duplicate entry for NULL.

What I would like to do, if possible, is only allow unique values if something is inputted by the end user except for NULLS. Any suggestions would be appreciated.

    [Column("ITIN")]
    [DataType(DataType.Text)]
    [Display(Name = "ITIN")]
    [MinLength(0), MaxLength(11), StringLength(11, ErrorMessage = "The {0} must be no more than {2} characters in length and in the format of ###-##-####. This is optional.", MinimumLength = 0)] // XXX-XX-XXXX
    [Index("UQ_UserInfo_ITIN", IsClustered = true, IsUnique = true)]
    [DisplayFormat(ConvertEmptyStringToNull = true)]
    [Required(AllowEmptyStrings = true,
              ErrorMessage = "The {0} is required. Please enter in a valid {0}."),
              ConcurrencyCheck]
    public string ITIN { get; set; }

[davidrobin]

As you have mentioned, by virtue of the fact the Index attribute has IsUnique = true and because NULL is a valid value in this context, then only one row can contain a value of NULL for that column in the database.
So you have a few options as I see it.

1) Remove the IsUnique from the column and on the Post action write code to check if the value already exists. If is doesn't exist continue with the save. If it does exist add a ModelError "Value already exists" and do not save the record.
2) Again remove the IsUnique from the Index attribute. Write your own unique ValidationAttribute, this could accept a parameter to include NULL values . Here are a couple of examples.
http://stackoverflow.com/questions/2...d-dataannotati
http://stackoverflow.com/questions/3...ata-annotation
The second option would be good if you could write it in a way so it could be reused on other columns.