Dynamically Generate Linq Queries

Dynamically generate LINQ queries

Here is a solution with expression trees:

var param = Expression.Parameter(typeof(SomeObject), "p");
var exp = Expression.Lambda<Func<SomeObject, bool>>(
    Expression.Equal(
        Expression.Property(param, "Name"),
        Expression.Constant("Bob")
    ),
    param
);
var query = someObj.Where(exp);

I know it's much more complex, but this may be useful in times.

Dynamically building LINQ queries from string

Here is my implementation of converting your query into a Func. Since I wasn't sure what type was in your collection, I made an interface to represent objects that had an attributes Dictionary<string, string> and processed that.

Basically I added a method to QueryClass to convert it to an Expression. It uses a helper dictionary string->lambda that builds the appropriate comparison Expression for each operator.
Then I added a class to convert the List<object> into a Func<IItem,bool> suitable for a LINQ Where filter.

public interface IItem {
    Dictionary<string, string> attributes { get; set; }
}

class QueryClass {
    public string FieldName { get; set; }
    public string Operator { get; set; }
    public object Value { get; set; }

    public QueryClass(string pInput) {
        var returned = RegexHelpers.SplitKeyValue(pInput); //just returns a string like "Make = Honda" into three parts
        if (returned != null) {
            FieldName = returned.Item1;
            Operator = returned.Item2;
            Value = returned.Item3;
        }
    }

    static MethodInfo getItemMI = typeof(Dictionary<string, string>).GetMethod("get_Item");
    static Dictionary<string, Func<Expression, Expression, Expression>> opTypes = new Dictionary<string, Func<Expression, Expression, Expression>> {
        { "==", (Expression lhs, Expression rhs) => Expression.MakeBinary(ExpressionType.Equal, lhs, rhs) },
        { ">=", (Expression lhs, Expression rhs) => Expression.MakeBinary(ExpressionType.GreaterThanOrEqual, Expression.Call(lhs, typeof(String).GetMethod("CompareTo", new[] { typeof(string) }), rhs), Expression.Constant(0)) },
        { "CONTAINS",  (Expression lhs, Expression rhs) => Expression.Call(lhs, typeof(String).GetMethod("Contains"), rhs) }
    };
    static MemberInfo attribMI = typeof(IItem).GetMember("attributes")[0];

    public Expression AsExpression(ParameterExpression p) {
        var dictField = Expression.MakeMemberAccess(p, attribMI);
        var lhs = Expression.Call(dictField, getItemMI, Expression.Constant(FieldName));
        var rhs = Expression.Constant(Value);

        if (opTypes.TryGetValue(Operator, out var exprMakerFn))
            return exprMakerFn(lhs, rhs);
        else
            throw new InvalidExpressionException($"Unrecognized operator {Operator}");
    }
}

public class LinqBuilder {
    static Type TItems = typeof(IItem);

    static Expression BuildOneLINQ(object term, ParameterExpression parm) {
        switch (term) {
            case QueryClass qc: // d => d[qc.FieldName] qc.Operator qc.Value
                return qc.AsExpression(parm);
            case List<object> subQuery:
                return BuildLINQ(subQuery, parm);
            default:
                throw new Exception();
        }
    }

    static Expression BuildLINQ(List<object> query, ParameterExpression parm) {
        Expression body = null;
        for (int queryIndex = 0; queryIndex < query.Count; ++queryIndex) {
            var term = query[queryIndex];
            switch (term) {
                case string op:
                    var rhs = BuildOneLINQ(query[++queryIndex], parm);
                    var eop = (op == "AND") ? ExpressionType.AndAlso : ExpressionType.OrElse;
                    body = Expression.MakeBinary(eop, body, rhs);
                    break;
                default:
                    body = BuildOneLINQ(term, parm);
                    break;
            }
        }

        return body;
    }

    public static Func<IItem, bool> BuildLINQ(List<object> query) {
        var parm = Expression.Parameter(TItems, "i");
        return Expression.Lambda<Func<IItem, bool>>(BuildLINQ(query, parm), parm).Compile();
    }
}

Once you have this, you can pass in a List<object> expression and then filter your collection. Given a query q and a collection of IItems cs, you can do:

var ans = cs.Where(LinqBuilder.BuildLINQ(q));

How to build a dynamic FROM clause for a LINQ query?

You can use Expression Trees to build dynamic LINQ queries. Here is an example: http://msdn.microsoft.com/en-us/library/bb882637.aspx

Another approach is to use Dynamic LINQ library:
http://weblogs.asp.net/scottgu/archive/2008/01/07/dynamic-linq-part-1-using-the-linq-dynamic-query-library.aspx

Both approaches are illustrated here:
http://www.codeproject.com/Articles/231706/Dynamic-query-with-Linq

Predicate Builder from this example uses Expression Tree approach.

In general, Dynamic LINQ is easier to implement but Expression Tree is more type-safe.

Dynamic Linq Where statement with Relation

After a lot of research and trying things, this is an easy and friendly way using the same Dynamic Linq library:

List<Publication> results = new List<Publication>();

// Just an example, previously calculated dynamically
string filterQuery = "(Id = 1 and Number = 2)";
string filterQueryChildren = "Products.Any(Id == 1)"

IQueryable<Publication> query = db.Publications.Include(i => i.Product).Where(filterQueryChildren);

query = query.Where(filterQuery);

results = query.OrderBy(orderQuery).ToList();

Build LINQ queries dynamically - force using sp_executesql instead of raw query

In order to let EF use a parameter instead of constant value, you need to introduce a closure (similar to what C# compiler does for compile time expressions).

One way is to create anonymous type and bind it's property:

var closure = new { value };
var body = Expression.Equal(Expression.Property(param, property),
    Expression.Property(Expression.Constant(closure), "value"));

Another way is to actually use the C# compiler to create a closure expression and bind it's body:

Expression<Func<TValue>> closure = () => value;
var body = Expression.Equal(Expression.Property(param, property),
    closure.Body);

How to dynamically build up a query in linq

You can use PredicateBuilder

Dim pred = PredicateBuilder.True(Of Profile)()
If Not String.IsNullOrEmpty(nameValue) Then
    pred = pred.And(Function(x) x.name = nameValue)
End If
If Not String.IsNullOrEmpty(locationValue) Then
    pred = pred.And(Function(x) x.location = locationValue)
End If

Dim query = profiles.Where(pred)

(Note: this solution assumes profiles is an IQueryable<Profile>, e.g. an Entity Framework DbSet or ObjectSet; if it's just a normal collection, use profiles.AsQueryable() instead of profiles)

---

Related Topics