Dynamic Linq Property Conversion to Sql

I am trying to understand dynamic linq and expression trees. I try very hard to make equal values ​​by providing column and value as strings. That's 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;
    }

      

This works great for columns in the DB. But crashing for properties in my code like

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

      

The granting of the error cannot be that it cannot be converted to SQL. I tried to rewrite the property as an expression and lt; Func, but no luck, how can I convert simple properties so that they can be used with linq in this dynamic way?

Many thanks

+2


a source to share


1 answer


To use properties other than a table, you need to first query and use LINQ on the objects. I don't think you can query both SQL and non-SQL properties at the same time for the reason that you point out: non-SQL properties have no SQL translation. I suspect that if you do ToList()

before calling filterTest()

, you will find that your code is fine for both types of properties. Unfortunately this is probably not what you want, and if your non-SQL property is derived from different SQL columns, you will need a way to generate an expression that matches the property definition.



+2


a source







All Articles