Dynamic built Linq to SQL Query

Question

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.

I think the missing pieces are "Expression.Invoke", and "Expression.AndAlso"; I've tried to show in an example. — Marc Gravell
– Marc Gravell, Commented Dec 18, 2009 at 20:06

Marc Gravell · Accepted Answer · 2009-12-18 20:05:27Z

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();

nitzmahone · Accepted Answer · 2009-12-18 20:04:51Z

0

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

answered Dec 18, 2009 at 20:04

nitzmahone

14k2 gold badges38 silver badges39 bronze badges

Collectives™ on Stack Overflow

Dynamic built Linq to SQL Query

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related