Dynamic Linq Property Converting to Sql

Question

I am trying to understand dynamic linq and expression trees. Very basically trying to do an equals supplying the column and value as strings. Here is what I have so far

 private IQueryable<tblTest> filterTest(string column, string value)
    {
        TestDataContext db = new TestDataContext();

        // The IQueryable data to query.
        IQueryable<tblTest> queryableData = db.tblTests.AsQueryable();

        // Compose the expression tree that represents the parameter to the predicate.
        ParameterExpression pe = Expression.Parameter(typeof(tblTest), "item");


        Expression left = Expression.Property(pe, column);
        Expression right = Expression.Constant(value);
        Expression e1 = Expression.Equal(left, right);

        MethodCallExpression whereCallExpression = Expression.Call(
            typeof(Queryable),
            "Where",
            new Type[] { queryableData.ElementType },
            queryableData.Expression,
            Expression.Lambda<Func<tblTest, bool>>(e1, new ParameterExpression[] { pe }));

        // Create an executable query from the expression tree.
        IQueryable<tblTest> results = queryableData.Provider.CreateQuery<tblTest>(whereCallExpression);

        return results;
    }

That works fine for columns in the DB. But fails for properties in my code eg

public partial class tblTest
{
    public string name_test
    {  get { return name; }  }
}

Giving an error cannot be that it cannot be converted into SQL. I have tried rewriting the property as a Expression<Func but with no luck, how can I convert simple properties so they can be used with linq in this dynamic way?

Many Thanks

Answer 1

To use non-table properties, you'll need to first materialize the query and use LINQ to objects. I don't think you can query against both SQL and non-SQL properties at the same time for the reason that you state: non-SQL properties have no SQL translation. I suspect that if you do a ToList() before calling filterTest(), you'll find that your code works just fine for both types of properties. Unfortunately, this probably isn't what you want and, if your non-SQL property is derived from various SQL columns, you will need a way to generate an expression that matches the property definition instead.