Dynamic built Linq to SQL Query

Question

Hi, i want to build a generic search window using linq to sql.

This is what i was trying to do:

class SearchWindow<T> : Form : Where T: class
{
    public SearchWindow(Func<T, string> codeSelector, 
                        Func<T, string> nameSelector)
    {
        var db = new DataContext();
        var table = db.GetTable<T>();
        var query = from item in table where 
                        codeSelector(item).Contains(someText) &&
                        nameSelector(item).Contains(someOtherText)
                    select item;
    }
}

And i was trying to use it like:

var searchWindow = new SearchWindow<SomeTable>(x => x.CodeColumn, 
                                               y => y.NameColumn).Show();

Bud saddly that doesn't work, i read about expression trees so i tried to do that with them, and i got:

public SearchWindow(codeColumn, nameColumn) 
{
    Table<T> table = db.GetTable<T>();
    var instanceParameter = Expression.Parameter(typeof(T), "instance");
    var methodInfo = typeof(string).GetMethod("Contains", 
                                              new Type[] { typeof(string) });
    var codigoExpression = Expression.Call(Expression.Property(instanceParameter, 
                                               codeColumn), 
                                           methodInfo, 
                                           Expression.Constant("someText", 
                                               typeof(string)));
    var nombreExpression = Expression.Call(Expression.Property(instanceParameter, 
                                               nameColumn), 
                                           methodInfo, 
                                           Expression.Constant("someOtherText", 
                                               typeof(string)));
    var predicate = Expression.Lambda<Func<T, bool>>(
         Expression.And(codigoExpression, nombreExpression), instanceParameter);
    var query = table.Where(predicate);
}

And to use it i need to do:

new SearchWindow<SomeTable>("codeColumn", "nameColumn");

But i don't like the approach to need to enter the column names as a string, is there any way to do it in a fashion similar to my first approach (in order to have intellisense and strong typing)?

Thank you for your help.

Answer 1

Untested, but something like:

    static IQueryable<T> Search<T>(
        IQueryable<T> source,
        Expression<Func<T, string>> codeSelector, 
        Expression<Func<T, string>> nameSelector,
        string code, string name)
    {

        var row = Expression.Parameter(typeof(T), "row");
        var body = Expression.AndAlso(
            Expression.Call(
                Expression.Invoke(codeSelector, row),
                "Contains", null,
                Expression.Constant(code, typeof(string))),
            Expression.Call(
                Expression.Invoke(nameSelector, row),
                "Contains", null,
                Expression.Constant(name, typeof(string))));
        var lambda = Expression.Lambda<Func<T, bool>>(body, row);
        return source.Where(lambda);
    }

You pass in your table (GetTable<T>) as the source, and lambdas to indicate the columns (x => x.CodeColumn / y => y.NameColumn etc).

---

Update; tested on LINQ-to-Objects, I'm hopeful it'll work on LINQ-to-SQL as well:

        var data = new[] {
            new { Code = "abc", Name = "def"},
            new { Code = "bcd", Name = "efg"},
            new { Code = "ghi", Name = "jkl"}
        }.AsQueryable();

        var filtered = Search(data, x => x.Code, x => x.Name, "b", "f");
        var arr = filtered.ToArray();

Answer 2

Use PredicateBuilder- it'll do the heavy lifting for you.