MVC 2 Unique index validation

Question

Is there a way to validate a property that should be unique on a model? For example, a user can create and edit a "Product", but they shouldn't be able to create a Product with an existing ProductCode, nor should they be able to edit a Product and change the ProductCode to a code that already exists.

I have tried using a custom attribute.

public class Unique : ValidationAttribute
{
    public override bool IsValid(object value)
    {
        var products = Repository.Store.Products.Where(x => x.ProductCode == value);
        return (products.Count() == 0);
    }
}

All that I can cover with this solution is not allowing the user to insert/update a Product when the code already exists in the DB. This does not allow the user to edit an existing product because when they submit, it will see that the code already exists(it will be the code for the product they are trying to edit.) and returns false.

Is there no way of dealing with a unique index in MVC 2, I have searched for hours, even found other topics on stackoverflow, but nothing with a solid solution.

Maybe I'm misunderstanding your question, but is ProductCode a primary key? If not, why? — delete
– delete, Commented Dec 15, 2010 at 13:28

Peter Ruderman · Accepted Answer · 2010-12-15 22:25:06Z

3

Just let the insert or update fail and then return an appropriate error message to the user. Checking up front is problematic anyway since there's always a chance that another user will modify the database immediately after your check.

Here's an example of how you can insert an object and determine whether or not it failed due to a unique constraint:

INSERT INTO MyTable (column1, column2, column3) 
    SELECT @param1, @param2, @param3
    WHERE NOT EXISTS (SELECT * FROM table WHERE id = @param4)

If the object already exists, this will modify 0 rows. If it does not, then it will modify 1 row. If anything else goes wrong, you'll get an exception. This is also quite efficient (at least in SQL server). It results in an index seek followed by an index update, just as you would hope.

edited Dec 15, 2010 at 22:25

answered Dec 15, 2010 at 13:41

Peter Ruderman

12.6k1 gold badge41 silver badges59 bronze badges

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

4 Comments

Craigt Over a year ago

I considered this for a solution, but the problem is that there is no "proper" way of determining whether or not the update failed due to the unique index, or something else. Only way I could see was to first ensure that both the exception and inner exceptions match the expected types, and then to do a string match on the inner exception message to be sure. But this could cause issues down the road. I'm not too concerned about multiple updates at the same time. It can throw an exception and they can try again... its very unlikely that this will happen, there are not many users or updates

Pradeep Over a year ago

I would second this approach. Let DB do some work. I am sure that SQL would be throwing distinctive exception code for Unique key violation. Use this code and then inform your user.

Peter Ruderman Over a year ago

Catching SQL exceptions in .NET is problematic, but this isn't the only way to determine if the insert failed due to a unique constraint. I'll ammend my answer with an example.

Craigt Over a year ago

Thanks for the answer, I have managed to implement it in a similar way. (Using linq instead of a query.)

ozz · Accepted Answer · 2010-12-15 13:34:52Z

0

I struggled with MVC a little in a related area.

Part of the answer gleaned to my question was that you should "probably" have a seperate model for Insert and Update of an object.

That way you could have your custom attribute just on the insert model.

Otherwise, just take care of this as a normal code check in your insert method, not in a custom attribute.

answered Dec 15, 2010 at 13:34

ozz

5,4362 gold badges52 silver badges73 bronze badges

1 Comment

Craigt Over a year ago

I thought about two separate models as well, but there is still no way of checking whether the product code has changed or not when using a attribute.

ozz · Accepted Answer · 2010-12-15 13:57:49Z

0

Ok I see....

Can you do a "does not equal" check on the exists for some unique ID on the object - that way you check for the existence of the product code BUT not on the current product.

answered Dec 15, 2010 at 13:57

ozz

5,4362 gold badges52 silver badges73 bronze badges

Collectives™ on Stack Overflow

MVC 2 Unique index validation

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

4 Comments

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related