A general validation attribute for checking uniqueness in a linq to sql data context

Question

I've been programming asp.net for, oh, a couple of days now. Here's a question I can't even begin to figure out for myself.

I hope it's obvious from the code what I want to accomplish, and I have, but it's not pretty. Furthermore I'd like to use it on whatever table, whatever field, i.e. check the uniqueness of a value against a table and field I specify, passing it all into the attribute constructor.

public class UniqueEmailAttribute : ValidationAttribute
{
    public UniqueEmailAttribute()
    {
    }

    public override Boolean IsValid(Object value)
    {
        //not pretty. todo: do away with this.
        var db = new CoinDataContext();
        int c = db.Emails.Count(e => e.Email1 == value.ToString());
        return (Boolean) (c == 0);
    }
}

Answer 1

I'm not into LINQ, but it seems you're trying to enforce uniqueness client side. That's just not possible. Uniqueness constraints must be enforced in the database. What do you think happens if a concurrent transaction commits an email address just after that check was done?

Even if you're checking just to provide a "Sorry, that address is already used"-message, there is still a possibility that another transaction inserts the same address.

Answer 2

First, let's look at rewriting the attribute...

public override bool IsValid(object value)
{
    var db = new CoinDataContext();

    //Return whether none of the email contains the specified value
    return db.Emails.Count(e => e.Email1 == value.ToString()) == 0;
}

Also, there was no need to cast (c == 0) as a boolean, as the result of that operation is already a bool. And the type bool is an alias for Boolean in the same way that int is an alias for Int32. Either is acceptable. I prefer the lower case version myself.

As Alex has already suggested in his answer, this wouldn't be a sure way of determining whether the email address was unique when it goes into the database. Only that it's unique at the time of checking.

Finally, and a bit off-tangent... I have written some linq extensions such as the following class. Using it would allow me to rewrite the return on the attribute to db.Emails.None(e => e.Email1 == value.ToString());. This makes it a little bit more readable.

Update There isn't a way of determining the uniqueness of a value in the database without going to the database and comparing the rows against the values written. You still need to create an instance to the database. What I would do though is look at seperating these concerns into areas such as a service layer and a data layer (separate projects from the MVC website project). Your data layer would exclusively handle anything to do with the database. If you'd like I can write some examples of how you'd separate the CoinDataContext from the attribute itself?

Addressing another of your concerns, here we remove the need for the query inside the attribute, but you still need a call to the database, and specifying which table you want to use.

Because this is an attribute however, I'm not 100% sure if you can use linq lambda expressions in the attribute it, so your attribute has to remain generalised in this fashion.

Data layer project

This layer would contain different classes relating to different tables. The class below is dedicated to the email table.

Email Mapper class

public static class EmailMapper
{
  public static void IsValid(Func<string, bool> query)
  {
    var db = new CoinDataContext();
    return db.Emails.Count(query) == 0;
  }
}

Service layer project

This layer is responsible for general validation of objects, but also is used for going to other layers such as external APIs.

EmailService class

public static class EmailService
{
  public static IsValid(string address)
  {
    bool isValid = false;

    //...Check email is valid first with regex. Not done.
    isValid = RegexHelper.IsEmailAddressValid(address);

    //Go to the database and determine it's valid ONLY if the regex passes.
    return isValid ? EmailMapper.IsValid(x=> x.Email == address) : false;
  }
}

Attribute class in web project

public override Boolean IsValid(Object value)
{
    return EmailService.IsValid(value.ToString());
}

Answer 3

This just in from asp.net forums by Brad Wilson. So pleased with it. No error handling!

using System;
using System.ComponentModel.DataAnnotations;
using System.Data.Linq;
using System.Linq;
using System.Linq.Expressions;
using System.Reflection;

public class UniqueAttribute : ValidationAttribute {
    public UniqueAttribute(Type dataContextType, Type entityType, string propertyName) {
        DataContextType = dataContextType;
        EntityType = entityType;
        PropertyName = propertyName;
    }

    public Type DataContextType { get; private set; }

    public Type EntityType { get; private set; }

    public string PropertyName { get; private set; }

    public override bool IsValid(object value) {
        // Construct the data context
        ConstructorInfo constructor = DataContextType.GetConstructor(new Type[0]);
        DataContext dataContext = (DataContext)constructor.Invoke(new object[0]);

        // Get the table
        ITable table = dataContext.GetTable(EntityType);

        // Get the property
        PropertyInfo propertyInfo = EntityType.GetProperty(PropertyName);

        // Our ultimate goal is an expression of:
        //   "entity => entity.PropertyName == value"

        // Expression: "value"
        object convertedValue = Convert.ChangeType(value, propertyInfo.PropertyType);
        ConstantExpression rhs = Expression.Constant(convertedValue);

        // Expression: "entity"
        ParameterExpression parameter = Expression.Parameter(EntityType, "entity");

        // Expression: "entity.PropertyName"
        MemberExpression property = Expression.MakeMemberAccess(parameter, propertyInfo);

        // Expression: "entity.PropertyName == value"
        BinaryExpression equal = Expression.Equal(property, rhs);

        // Expression: "entity => entity.PropertyName == value"
        LambdaExpression lambda = Expression.Lambda(equal, parameter);

        // Instantiate the count method with the right TSource (our entity type)
        MethodInfo countMethod = QueryableCountMethod.MakeGenericMethod(EntityType);

        // Execute Count() and say "you're valid if you have none matching"
        int count = (int)countMethod.Invoke(null, new object[] { table, lambda });
        return count == 0;
    }

    // Gets Queryable.Count<TSource>(IQueryable<TSource>, Expression<Func<TSource, bool>>)
    private static MethodInfo QueryableCountMethod = typeof(Queryable).GetMethods().First(m => m.Name == "Count" && m.GetParameters().Length == 2);
}

Answer 4

Could you please tell me how to implement unique property validation on edited entity? In this case the unique attribute implementation will fail because it doesn't take into account the entity being edited.

It is possible to rewrite this attribute to use it on the entity level, but then the error will belong to entity level, not the property.