extending SqlMethods.Like to support property name

Question

I'm trying to extend SqlMethods.Like method to support property name rather than property value, i wrote the following extension method :

        public static bool Like(this object obj, string propertyName, string pattern)
    {
        var properties = obj.GetType().GetProperties().Select(p => p.Name);

        if(!properties.Contains(propertyName))
            throw new Exception(string.Format("Object does not contain property:{0}", propertyName));

        return SqlMethods.Like(obj.GetType().GetProperty(propertyName).GetValue(obj, null).ToString(), pattern);
    }

however the method throws the following exception : Method 'Boolean Like(System.Object, System.String, System.String)' has no supported translation to SQL.

how can i write an extension method with transaction to SQL support ?

Answer 1

What you want to do does not seem to make sense in the contxt of what SqlMethods.Like actually does. When you pass in a property of a class you are essentially telling it to translate that into the equivelent field in the SQL query. e.g.

var result = from names in db.Names
             where SqlMethods.Like(names.FullName, '%Smith%')
             select names;

would translate to something like:

SELECT * 
FROM Names
WHERE Fullname LIKE '%Smith%'

(in practice it would be different using parameters and sp_executeSQL but coneptually that is what it would do).

If you want to pass in the name of a property what does that mean in terms of SQL, conceptually it makes no sense e.g.

SELECT * 
FROM Names
WHERE --what would go here-- LIKE '%Smith%'

As such you are not going to be able to create a Linq To SQL method that creates nonsense SQL.

What are you actually trying to do, the chance is that you are going about it completely the wrong way.

Edit:hmm from your comment i think i understand what you want to do, in essense you want to be able to specify the column you are doing a LIKE comparison with at run time. You cannot do it exactly. You could use a stored procedure that used dynamic SQL and took a string parameter for the column. You could then expose this as a method on your data context class.

Answer 2

I found this answer from RichardD that is exactly the correct answer. Reposting for clarity, but original is linked below.

using System;  
using System.Linq;  
using System.Linq.Expressions;  

public static class Extensions  
{  
    public static IQueryable<T> WhereLike<T>(this IQueryable<T> source, string propertyName, string pattern)  
    {  
        if (null == source) throw new ArgumentNullException("source");  
        if (string.IsNullOrEmpty(propertyName)) throw new ArgumentNullException("propertyName");  

        var a = Expression.Parameter(typeof(T), "a");  
        var prop = Expression.Property(a, propertyName);  
        var body = Expression.Call(typeof(SqlMethods), "Like", null, prop, Expression.Constant(pattern));  
        var fn = Expression.Lambda<Func<T, bool>>(body, a);  

        return source.Where(fn);  
    }  
}  
...  
.WhereLike("Description", "%a%b%c%"));  

The solution uses expression trees, but all advanced LinqToSql operations will require familiarity with that.

From: http://forums.asp.net/p/1488418/3503874.aspx