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
a source to share
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.
a source to share