Validate unique keys with asp.net mvc and linq to sql?

Question

I have a sql server table with 2 fields, ID (primary key) and Name (unique key). I'm using linq to sql to produce model objects for asp.net MVC from this table.

To perform the model validation I've implemented IDateErrorInfo in a partial class

public partial class Company : IDataErrorInfo
{
    private Dictionary<string, string> _errors = new Dictionary<string,string>();

    partial void OnNameChanging(string value)
    {
        if (value.Trim().Length == 0)
        {
            _errors.Add("Name", "Name is required");
            return;
        }
    }
}

This performs as expected with the Model.IsValid property and Html.ValidationSummary helper.

However, this code is just checking that the newly created Company has a Name that is not blank. I also need to check if the Name has been used by another Company in the table.

I could just call the AddCompany method on my repository and catch the SQLException, but this feels dirty.

[AcceptVerbs(HttpVerbs.Post)]
public ActionResult Create([Bind(Exclude="ID")] Company companyToCreate)
{
if (!ModelState.IsValid)
{
    return View();
}
//Add to the Database
try
{
    _companyRepos.AddCompany(companyToCreate);
    return RedirectToAction("Index");
}
catch(SQLException ex)
{
    return View("do something to show the error inserting");
}
}

Ideally I want the OnNameChanging method in the partial class to perform the unique key check before I try to add the Company.

Any ideas on how I should be doing this? The only thought I've has so far is to create a fresh database connection in the partial class and query the table.

Thanks

Answer 1

However you slice it, you're going to obviously have to hit the database to get a list of names already in use. Therefore, I would suggest adding a method in your repository that basically just returns an IList or IEnumberale that simply contains all the distinct names in that table in the DB. Then, in your validating method, simply use that method on the repository to get all the unique names, and implement your check there.

Answer 2

One possibility is for the AddCompany method to return a boolean indicating success or failure of the operation.

However, it is customary to catch this type of error before you attempt to add the record. Put a

bool Exists(string companyName)

method in your Company Repository, and use this to catch the error before attempting to add the record.

The reason this is preferable is you know exactly why the failure occurred. To do that using a try/catch, you would either have to throw a custom exception, or capture an error code.

Answer 3

You could also add a unique constraint check on the column in the table.

Observation: this is pretty trivial in Ruby on Rails, it's too bad it's not so trivial in ASP.NET MVC