How can I use validation to enforce the uniqueness of a property in ASP.NET MVC 2?

Question

Imagine an object with a field that can't have a duplicate value in the database. My first instinct was to create a unique attribute that I could apply as a data annotation to a property. This unique attribute would hit the database and check if the value already exists. This would work when executing a create method, but would fail on an update. On an update, I would get a duplicate value error for every unique field of my entity whose value I don't want to change. What would be a good way, or an established practice, to accomplish this on ASP.NET MVC 2 in a way that fits nicely with the ModelState? Passing the id of my object to the attribute validator could work by checking if the duplicate value that is found is of the same entity that I am updating but I don't know how to get that data from inside of the validator.

Please forgive me if this is a stupid question or if it is phrased incoherently. It's almost 3 in the morning and I've been coding since the morning of yesterday.

Answer 1

For this kind of validation, I would let the database do what it already does so well. Make sure your database has the unique constraint and let it report back an error if you violate it. You can then add the error to the model errors (with a nice friendly bit of text, rather than just plonking the SQL error).

If you are determined to perform a check yourself, you can get around the UPDATE problem by excluding the current record...

SELECT COUNT(*)
FROM myTable
WHERE myTable.UniqueValue = 'ShouldBeUnique'
AND myTable.Id <> 5

In this example, you use the id of the record you are updating to avoid checking it, which means you just check other records to see if they contain the unique value.