How to dynamic add filters to a LINQ query against an Odata Source in C#

Question

I have a query like the following

var query = (from x in NetworkDevices
where   
x.Name == "blabla1" ||
x.Name == "blabla2" 
select x );

and i'm running it against an Odata endpoint, so it effectively gets translated into the following URL

https://targetserver/endpoint.svc/NetworkDevices()?$filter=Name eq 'blabla1' or Name eq 'blabla2'

So i want to dynamically add lots of those where filters... In C# i can just keep adding it to my query, but that isn't dynamic. I want to do it at runtime. If i was calling this from Javascript, well i can easily just update the URL as well.

My Question, is in C# how do i dynamically add these filters to the where clause.

in plain old LINQ (like linq 2 objects) i could do something like this.

var machines = new string[] { "blabla1" , "blabla2" } ;
res1.Where ( x => machines.Contains(x.Name) ).ToArray()

and that would work, but that doesn't work against the Odata Endpoint as i get an error like this.

The method 'Contains' is not supported

So i presume the only way would be to dynamically , edit the expression tree or something to add those filters. Does anybody know how to do that?

You have the right approach, you just happen to be unable to use `Contains` with OData. FWIW, I tried working with OData awhile back and found it to be extremely limiting and frustrating when combined with LINQ. — Yuck, Apr 03 '13 at 01:15
possible duplicate of [OData "where ID in list" query](http://stackoverflow.com/questions/7745231/odata-where-id-in-list-query) — hazzik, Apr 03 '13 at 01:15
@hazzik +1 nice find. From the accepted answer *OData doesn't support IN statements* -- this is one of the reasons that I think OData is a horrible protocol / data access technology for anything even marginally more involved than basic CRUD operations. — Yuck, Apr 03 '13 at 01:17
I ended up writing a custom LINQ provider that would translate the Contains into a field = X or field = Y or field = Z expression, which can be translated into OData. — Rich, Apr 03 '13 at 03:14
@Rich , what sort of effort was it to write such a linq provider? — klumsy, Apr 03 '13 at 15:20
Took a while to figure out. Took most of my template from here: http://msdn.microsoft.com/en-us/library/bb546158.aspx. The key piece was the ExpressionVistor, since that translates the Contains into the Expression.Or chain. Unfortunately, I can't find the code. — Rich, Apr 03 '13 at 16:35

score 6 · Accepted Answer · answered Apr 03 '13 at 18:11

Following some other links , i found a couple of options. One would be to dynamically create the expression tree which became a quagmire rather quickly, the other is to build the $filter manually. However and add it with .AddQueryOption() . However if there are already other where clauses in the query this breaks since the resulting URL now has two $filter entries.. So what i do is take my original query, and then grab the URL and the querystring and grab the $filter , and then if it exists add my own dynamic stuff and run a new query. here is a demo (running in linqpad)

//Grab original query as a DataServiceQuery
DataServiceQuery<NetworkDevice> originalquery = (DataServiceQuery<NetworkDevice>) 
    (from x in NetworkDevices
    where   
    x.Type == "switch"
    select x);
//Get the HTTP QueryString
var querystr = (originalquery).RequestUri.Query;
var filter = System.Web.HttpUtility.ParseQueryString(querystr)["$filter"];

/* Create our own dynamic filter equivilant to 
    x.Name == "x" ||
    x.Name == "y" 
*/
string[] names = { "device1", "device2" };
StringBuilder sb = new StringBuilder();
sb.Append("(");
foreach (string s in names)
{
    sb.Append(String.Format("Name eq '{0}'",s));
    sb.Append(" or ");
}
sb.Remove(sb.Length - 4, 4);
sb.Append(")");
var dynamicfilter = sb.ToString();
// If there was an original filter we'll add the dynamic one with AND , otherwise we'll just use the dynamicone
var newfilter = dynamicfilter;
if ( filter != null && filter.Trim() != string.Empty )
{
newfilter = filter + " and " + newfilter;
}
newfilter.Dump();


var finalquery = 
    (from x in NetworkDevices.AddQueryOption("$filter",newfilter)
    select x).Take(50);

finalquery.Dump();

score 0 · Answer 2 · answered Apr 04 '13 at 03:57

Here's an example of an ExpressionVistor which I used to convert a Contains into an OrElse:

    public class WhereContainsTreeModifier : ExpressionVisitor
    {
        private Expression TranslateContains(LambdaExpression lambda)
        {
            var methodCall = lambda.Body as MethodCallExpression;
            var member = methodCall.Object as MemberExpression;
            var objectMember = Expression.Convert(member, typeof(object));
            var getterLambda = Expression.Lambda<Func<object>>(objectMember);
            var getter = getterLambda.Compile();

            var list = (IEnumerable)getter();

            Expression result = null;
            foreach (object item in list)
            {
                var equal = Expression.Equal(methodCall.Arguments[0], Expression.Constant(item));
                if (result == null)
                    result = equal;
                else
                    result = Expression.OrElse(result, equal);
            }

            result = Expression.Lambda(lambda.Type, result, lambda.Parameters);
            return result;
        }

        protected override Expression VisitLambda<T>(Expression<T> node)
        {
            if ((node.Body as MethodCallExpression).Method.Name == "Contains")
                return TranslateContains(node);
            else
                return node;
        }
    }

Your code perfectly works with sample IQueryable data but i having error "must be reducible node" while trying to use it for WCF data service. Exception is throwed by Visit() method inside Fix, and here is a call stack: at Expression.VisitChildren(ExpressionVisitor visitor) at Expressions.ExpressionVisitor.VisitArguments(IArgumentProvider nodes) at Expressions.ExpressionVisitor.VisitMethodCall(MethodCallExpression node) at LinqVisitor.Fix[T](IQueryable`1 WCFQuery) — Danila Polevshchikov, Apr 12 '13 at 13:50
I get the same thing ("must be reducible node" exception) when using the ExpressionVisitor class on any Data Service Query (odata version 5.4). Even if the expression visitor is doing nothing e.g. class MyVisitor : ExpressionVisitor {} with no additional body. — The Senator, May 02 '13 at 12:14
As mentioned by the other comments above, this code does not work on any recent version of OData. — kzu, Jun 15 '21 at 17:49

How to dynamic add filters to a LINQ query against an Odata Source in C#

2 Answers2

Linked