Linq To SQL throws has no supported translation to SQL Exception when using generic Insert method

Question

I have a method to insert entity to tables genericly using type T. But I want to add a feature to check if the entity to be added exists in the table based on a matcher property (e.g Name). When I execute the code it throws 'has no supported translation to SQL' on the check line. My code part is below. How can I fix this problem?

    public static InsertStatus Add<T>(T ent, string matcherProp) where T : class
    {
        System.Data.Linq.Table<T> t = otdc.GetTable<T>();

        //Exception on this line
        if (t.Any(item => object.Equals(GetPropValue(item, matcherProp), GetPropValue(ent, matcherProp))))
            return InsertStatus.AlreadyExists;

        try
        {
            t.InsertOnSubmit(ent);
            otdc.SubmitChanges();
            return InsertStatus.Successfull;
        }
        catch
        {
            return InsertStatus.UnknownError;
        }
    }
    public static object GetPropValue(object src, string propName)
    {
        return src.GetType().GetProperty(propName).GetValue(src, null);
    }

Answer 1

You will need to create an expresion tree at runtime. Fortunately this isn't very hard in your case; it will be something like:

var p = Expression.Parameter(typeof(T), "p");
var val = GetPropValue(ent, matcherProp);
var test = Expression.Lambda<Func<T, bool>>(
    Expression.Equal(
        Expression.PropertyOrField(p, matcherProp),
        Expression.Constant(val)
    ), p);

if (t.Any(test))
    return InsertStatus.AlreadyExists;

What this does is construct the logical tree for:

p => p.{matcherProp} == {val}

where matcherProp is the name of the member to test, and val is the existing value as a constant.

Note that you might get issues if val is null, unless you can also supply the type of the property (.PropertyType on the PropertyInfo) - and supply it to Expression.Constant.

---

Edit: another way to do this is to supply ent as the constant:

var p = Expression.Parameter(typeof(T), "p");
var test = Expression.Lambda<Func<T, bool>>(
    Expression.Equal(
        Expression.PropertyOrField(p, matcherProp),
        Expression.PropertyOrField(Expression.Constant(ent), matcherProp),
    ), p);

This is then more akin to:

p => p.{matcherProp} == ent.{matcherProp}

where ent in the lambda behaves much like a "captured variable".