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<string> or IEnumerable<string> 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.