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 :