Set Based Operations in Ado.net Data Services

Set Based Operations in Ado.net Data Services

  • Comments 10
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("http://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 :
Leave a Comment
  • Please add 8 and 3 and type the answer here:
  • Post
  • PingBack from http://net.blogfeedsworld.com/?p=22343

  • While the 'LINQ Ninja' moniker is something that Phani completely made up on the spot and I actively

  • 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.

  • Hi Joe,

    That sounds like a neat idea.

    I shall try your suggestion and update the code snippet later

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

  • 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

  • As an extension to explained in the last blog post dealing with Set based filter operations in our client

  • Very cool extension method, thanks for sharing!

    Noticed a small typo:

    ParameterExpression param = propertyExpression.Paramters.Single();

    should read:

    ParameterExpression param = propertyExpression.Parameters.Single();

  • Fixed, thanks for correcting this Scott!

  • 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);

           }

Page 1 of 1 (10 items)