Entity Framework Dynamic Where Clause from List<

问题:

After scourging the internet I have not been able to find a solution that i can get to work. I am attempting to use dynamic linq to enable the user to build a custom query from a windows form. To hold each where clause I have built an object called WhereClause:

public class WhereClause
{
    public int id { get; set; }
    public string ColumnName { get; set; }
    public string Operator { get; set; }
    public string OperatorSymbol { get; set; }
    public object Value { get; set; }
    public string AndOr { get; set; }
    public string AndOrSymbol { get; set; }
}

Most is self explanatory, but the distinction between Operator and Operator Symbol is that the Operator will store "Equals" or "Greater Than" to where the Operator Symbol will be "==" and ">" respectively. The AndOR works in a similar fashion.

Then as the user builds the where clauses a new WhereClause object is created and added to a List<WhereClause>

When it comes time to running the query I am using entity framework and linq to build the query.

I would like to do something like:

List<WhereClause> wheres; //populated via parameter in the encapsulating method.

DataContext db = new DataContext();
var query = from d in db.Database select d;

string whereClause = "";

foreach(WhereClause where in wheres){
    whereClause = whereClause + String.Format(" {0} {1} {2} {3}", where.ColumnName, where.OperatorSymbol, where.Value, where.AndOrSymbol);

    query.Where(whereClause);

}

Ive tried every which way to build the where clauses including using the where(predicate, params) and formating the predicate using "@1" but havent found the right way to do so.

Here is what the query builder looks like for context..

So now I turn it to you guys for help..

回答1:

You need to translate your WhereClause object into Expression, then you can use it as where query. Here an example:

Type yourType = typeof(YourGeneric);
ParameterExpression pe = Expression.Parameter(yourType , "x");
Expression left = Expression.Property(pe, yourType.GetProperty(whereClause.ColumnName));
Expression right = Expression.Constant(whereClause.Value, typeof(int));
Expression result = Expression.Equal(left, right);

This is an example to compare an int property. You need some if (or switch) to understand property type and what type of comparation you need to do.

if (whereClause.Operator == "Greater Than") {
    result = Expression.GreaterThan(left, right);
}

When you finished use Expression in this way:

context.Set<YourGeneric>().Where(result);

I reccomend to use generics (if possible) to simplify code and work. You can also concatenate more expressions:

Expression result4 = Expression.AndAlso(result1, result2);
Expression finalRes = Expression.Or(result3, result4);