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=22343Anonymous
July 17, 2008
While the 'LINQ Ninja' moniker is something that Phani completely made up on the spot and I activelyAnonymous
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 laterAnonymous
May 03, 2009
Telerik has a range of controls that work with ADO.NET Data Services as the data source . Take a lookAnonymous
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, IanAnonymous
May 26, 2009
As an extension to explained in the last blog post dealing with Set based filter operations in our clientAnonymous
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); }