Today's Post is co-Authored by Linq Ninja and my Colleague
MarceloLinq 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 :
Extension Method to facilitate Set Based Methods in Linq to Astoria
namespace ALinqExtensions
{
using System.Linq.Expressions;
public static class SetBasedExtensions {
public static DataServiceQuery<T> IsIn<T>(this DataServiceQuery<T> query, IEnumerable Set, string Property) {
//The Filter Predicate that contains the Filter criteria
Expression filterPredicate = null;
//The parameter expression containing the Entity Type
ParameterExpression param = Expression.Parameter(typeof(T), "l");
//Get Key Property
//The Left Hand Side of the Filter Expression
Expression left = Expression.Property(param, Property);
//typeof(T).GetProperty(FieldName));
//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 filterLambdaExpression = Expression.Lambda<Func<T, bool>>(filterPredicate, param);
return (DataServiceQuery<T>)query.Where<T>(filterLambdaExpression);
}
}
Sample usage
private void ContactsInCityIVisit() {//Create the Northwind Client Context
northwindContext nwContext = new northwindContext(
new Uri("http://ServiceEndPoint/Northwind.svc")
);
//The set in which we have to search for a match
List<string> citiesIWillVisit = new List<string>() {
"London",
"Berlin",
"Prague"
};
var customersAround = nwContext.Customers.IsIn<Customers>(citiesIWillVisit, "City");
foreach (Customers localCustomer in customersAround) {
System.Console.WriteLine(localCustomer.ContactName);
}
System.Console.Read();
