Set Based Operations in Ado.net Data Services

Today's Post is co-Authored by Linq Ninja and my Colleague Marcelo
Linq to Astoria does not support Set based queries , which means that this query is invalid

 List<string> citiesIWillVisit = new List<string>(){
                        "London",
                        "Berlin",
                        "Prague"
                        };
var customersAround = nwContext.Customers.Where<Customers>(cx => citiesIWillVisit.Contains(cx.City));

The above example would use the IN operator to search for a property in
a given set of values.You can achieve the same query using
Dynamic Linq Queries to filter over a set of values by creating Dynamic Linq Queries
at runtime based on your logic and Set .Lets take the same example as before .
Of all the Customers in the Northwind store , lets say that you want to visit customers living in cities
thatare on your itinerary.

  List<object> citiesIWillVisit = new List<object>(){
                        "London",
                        "Berlin",
                        "Prague"
                        };

The Customer Entity contains a property called "City" on which we want to filter.
if you already know the cities and they wont change , a normal Filter query would look like this

             //Create the Northwind Client Context
            northwindContext nwContext = new northwindContext(
                new Uri("https://ServiceEndPoint/Northwind.svc")
                );

            var customersAround = from cx in nwContext.Customers
                                  where cx.City == "London"
                                  || cx.City == "Berlin"
                                  || cx.City == "Prague"
                                  select cx;

            foreach (Customers coHabitant in customersAround) {
                System.Console.WriteLine(coHabitant.ContactName);
            }

Now , what if the cities you visit is dynamic ? In such a case you cannot write
queries for all possible combinations and expect it to work.
Its just too wasteful to do that , there is a better way to do this , Dynamic Filter Expressions.
Once you have a dynamic filter expression, you can call the "Where" method on
the appropriate DataServiceQuery<T> object to get the results. Lets look at how to build a dynamic filter expression.
An Expression has the following basic components.

 nwContext.Customers.Where<Customers>(cx => cx.City == "London")

 Parameter : "cx"

Left Hand Side Of Expression : cx.City
Constant : "London"

If you were to build the same using Dynamic Linq Queries , you would write

 //The parameter expression containing the Entity Type
//cx
ParameterExpression param = Expression.Parameter(typeof(Customers), "cx");
//The Left Hand Side of the Filter Expression
//cx=> cx.City
MemberExpression left = Expression.Property(param, "City");
//The constant to compare against 
ConstantExpression constant = Expression.Constant("London")
//Build the Filter 
//cx=> cx.City == "London"
Expression.Equal( left , constant )
//Build the LambdaExpression to pass to the Where Method
//.Where(cx=> cx.City == "London")
var lambda = Expression.Lambda<Func<Customers, bool>>(filterExpression, param);
//Execute the Filter 
var customersAround = nwContext.Customers.Where<Customers>(lambda);

Now , in the case we just described , the expression just compares the value of city to a constant "City". If we had
to create a query comparing the value of City to more constant values , we would have had to create expressions with OR .

 Expression filterPredicate = null;
    foreach (var id in set) {
        //Build a comparison expression which equates the Id of the Entity with this value in the IDs list
        // ex : e.Id == 1
        Expression comparison = Expression.Equal(left, Expression.Constant(id));
        //Add this to the complete Filter Expression
       // e.Id == 1 or e.Id == 3
        filterPredicate = (filterPredicate == null) ? comparison : Expression.Or(filterPredicate, comparison);
 }

This is what the complete Function looks like ..

 
static Expression<Func<Customers, bool>> ContainedInSet(IEnumerable Set, string Property, Type EntityType)
        {
            //The Filter Predicate that contains the Filter criteria
            Expression filterPredicate = null;
            //The parameter expression containing the Entity Type
            ParameterExpression param = Expression.Parameter(EntityType, "l");
            //The Left Hand Side of the Filter Expression
            Expression left = Expression.Property(param, Property);

            //Build a Dynamic Linq Query for finding an entity whose ID is in the list
            foreach (var id in Set) {
                //Build a comparision expression which equats the Id of the ENtity with this value in the IDs list
                // ex : e.Id == 1
                Expression comparison = Expression.Equal(left, Expression.Constant(id));
                //Add this to the complete Filter Expression
                // e.Id == 1 or e.Id == 3
                filterPredicate = (filterPredicate == null) ? comparison : Expression.Or(filterPredicate, comparison);
            }
            //Convert the Filter Expression into a Lambda expression of type Func<Lists,bool>
            // which means that this lambda expression takes an instance of type EntityType and returns a Bool
            var lambdaFilterExpression = Expression.Lambda<Func<Customers, bool>>(filterPredicate, param);
            return lambdaFilterExpression;
        }

But , it's not fun unless its an Extension Method , so we have the complete sample as :

Comments

• Anonymous
  July 16, 2008
  PingBack from http://net.blogfeedsworld.com/?p=22343

• Anonymous
  July 17, 2008
  While the 'LINQ Ninja' moniker is something that Phani completely made up on the spot and I actively

• Anonymous
  October 01, 2008
  Shouldn't it be possible to pass in the property to be filtered as an expression rather than a string, so that you retain intellisense support and compile-time validation?  As in: nwContext.Customers.IsIn<Customers>(citiesIWillVisit, (c) => c.City); The extension method signature would become: public static IQueryable<T> IsIn<T>(this IQueryable<T> query, IEnumerable Set, Expression<Func<T, object>> Left) (Probably better to use IQueryable rather than DataServiceQuery, just in case you wanted to re-use this with another Linq provider that doesn't support Set-based 'In' clauses) And in the method you'd do: ParameterExpression param = Left.Parameters.Single(); to bind your outer filter parameter with the inner property selector.

• Anonymous
  October 02, 2008
  Hi Joe, That sounds like a neat idea. I shall try your suggestion and update the code snippet later

• Anonymous
  May 03, 2009
  Telerik has a range of controls that work with ADO.NET Data Services as the data source . Take a look

• Anonymous
  May 24, 2009
  Hi, Is there any way to extend the above method to use a Contains expression (ultimately translating to a 'substringof' query) instead of an Equals? I am able to generate the expression I want by manually building the query string - enumerate around the enumerable set or'ing together 'substringof('{0}', property)' - but can't seem to generate the expression that equates to this query. Cheers,  Ian

• Anonymous
  May 26, 2009
  As an extension to explained in the last blog post dealing with Set based filter operations in our client

• Anonymous
  August 07, 2009
  Very cool extension method, thanks for sharing! Noticed a small typo: ParameterExpression param = propertyExpression.Paramters.Single(); should read: ParameterExpression param = propertyExpression.Parameters.Single();

• Anonymous
  August 08, 2009
  Fixed, thanks for correcting this Scott!

• Anonymous
  November 29, 2010
  Long time since no comments. Just to add that this solution is not working with Guids, but i fixed that by adding generic set and converting expression to specific S type ;) public static IQueryable<T> IsIn<T, S>(this IQueryable<T> query, IEnumerable<S> Set, Expression<Func<T, Object>> propertyExpression)        {            //The Filter Predicate that contains the Filter criteria            Expression filterPredicate = null;            //The parameter expression containing the Entity Type            ParameterExpression param = propertyExpression.Parameters.Single();            //Get Key Property            //The Left Hand Side of the Filter Expression                        Expression left = propertyExpression.Body;            //Build a Dynamic Linq Query for finding an entity whose ID is in the list            foreach (var id in Set)            {                //Build a comparision expression which equats the Id of the ENtity with this value in the IDs list                // ex : e.Id == 1                Expression comparison = Expression.Equal(Expression.Convert(left, typeof(S)), Expression.Constant(id));                //Add this to the complete Filter Expression                // e.Id == 1 or e.Id == 3                filterPredicate = (filterPredicate == null) ? comparison : Expression.Or(filterPredicate, comparison);            }            //Convert the Filter Expression into a Lambda expression of type Func<Lists,bool>            // which means that this lambda expression takes an instance of type EntityType and returns a Bool            var filterLambdaExpression = Expression.Lambda<Func<T, bool>>(filterPredicate, param);            return query.Where(filterLambdaExpression);        }