How to apply a filter on LINQtoSQL results?

Question

With the ListBox control it is possible to feed it a DataSource, name a DisplayMember and a ValueMember and through some magic it will display a field from the DataSource and return a selected ValueMember. It can work wit a linq-to-sql result without even knowing anyting specific about the table it is feed with.

Isn't Reflection and Attributes doing some magic? How does it work! I have a need to do something similar but I do not know where to start. I'm a beginner for LINQtoSQL.

This is what I want to do. I have a source table that I want to filter. The source table can be anything but will be originating from some DataContext.

var MySourceTable =
    from MyRecord in Context.GetTable<MySourceTable>()
    select new
    {
        Value = MyRecord.ID,
        Display = MyRecord.Name,
        FilterValue = MyRecord.Value
    };

In my control I want to be able to filter MySourceTable on some given value. The control does not know what table is used (MySourceTable in the example above) and the control does only know the three names, ID, Name and Value of the fields in the record it should use.

The filter query should look like the example below.

var MyTable
    from Record in MySourceTable
    where FilterValue == GivenValue
    select new
    {
        Value = Record.ID,
        Display = Record.Name,
    };

Can somebody advise me on where to start?

Answer 1

I wrote a filter engine that takes in a Property and Value as a string, and is able to use that as a where clause.

IQueryable<T> FilterFunction<T>(IQueryable<T> query)
{
    ParameterExpression p = Expression.Parameter(typeof(T), "notused");

    Expression<Func<T, bool>> wherePredicate =
      Expression.Lambda<Func<T, bool>>(
          Expression.Equal(
            Expression.Call(Expression.Property(p, FilterProperty), "ToString", new Type[0]),
            Expression.Constant(FilterValue)), p);

    return query.Where(wherePredicate);
}

You should be able to pass in an Expression<Func<T, TResult>> built in a similar way into query.Select()

If I am understanding your question correctly, I believe this will work:

string DisplayProperty = "Name";
string ValueProperty = "ID";

IQueryable<Record> SelectRecordProperties<T>(IQueryable<T> query)
{
    ParameterExpression p = Expression.Parameter(typeof(T), "notused");

    MethodInfo ctorMethod = typeof(Record).GetMethod("Create");

    Expression<Func<T, Record>> selectPredicate =
      Expression.Lambda<Func<T, Record>>(
        Expression.Call(ctorMethod,
            Expression.PropertyOrField(p, DisplayProperty),
            Expression.PropertyOrField(p, ValueProperty)), p);

    return query.Select(selectPredicate);
}
class Record
{
    public static Record Create(string display, string value)
    {
        return new Record() { Display = display, Value = value };
    }
    public object Display { get; set; }
    public object Value { get; set; }
}

So for your full function you'd need to combine these two ideas so that your filtering works.

By the way, there are many possible ways to build the expression tree for this, there was some tool I've found at one point which would show you the expression tree I think, so you could manually write the linq query and see how .Net builds the expression, then modify this code to build it based on that to possibly get a more efficient expression tree.