The main technical issue that we have to solve: how to request filtered data from a Business Service Layer(BSL) that uses WCF.
Building dynamic filters for queries was raised as an issue to allow a better filtering in the web UI for entities such as customers, events and so on (e.g. displaying events taking place between two dates or for one subscription or both at the same time). Of course the process of filtering needed to be done while still keeping the data access methods in Business Service Layer(BSL) so we had to go through first establishing the filters in the UI which would then have to be sent to the BSL and somehow applied to the data. The simplest solution we found is to use a list of lambda expressions as filters and send those to the BSL where the equivalent expression tree would be applied on a general SQL query. This also minimizes the code to be written on both sides and allows us to treat all entities the same way. This page describes the mechanism we have built to support filtering using the aforementioned concept.
LINQ
LINQ to SQL (Language Integrated Query – http://msdn.microsoft.com/library/bb425822.aspx#linqtosql_topic1) provides a run-time infrastructure for managing relational data as objects while still keeping the ability to query. What this means is it allows us to use the C# language to directly interact with our data tables as if they were a normal data collection defined in our application using syntax similar to SQL.
Example 1:
In MSSQL we would have the following query to retrieve all customers on the site with id = 1, ordered by their email:
SELECT * FROM Customers where SiteId = 1 order by Email
Linq to SQL allows us to do the same thing in C# without going through all the steps of assembling the query as a string, executing it and then retrieving the data rows:
IQueryable<Customer> customers =
from c in context.Customer
orderby c.Email ascending
where c.SiteId == 1
select c;
The resulted IQueryable object can itself have queries applied on it using lambda expressions:
var centennial = customers.Where(c => c.Id > 100); centennial = customers.OrderBy(c => c.Id);
The data is not retrieved until a specific call for it is made.
List<Customer> actualData = customers.ToList();
or
var enumerator = customers.GetEnumerator();
while (enumerator.MoveNext())
{
Customer customer = enumerator.Current;
}
Enumeration will force the expression tree associated with the customers IQueryable to be executed. This way we can apply filters to the data gradually building the expression tree and only then retrieve the data. Expressions trees are generated in C# from lambda expressions. In the code above,
var centennial = customers.Where(c => c.Id > 100);
is equivalent to
Expression<Func<Customer, bool>> lambda = customer => customer.Id > 100; var centennial = customers.Where(lambda);
UI FILTERS
In order to support filters in the UI for various entities (customers, subscriptions, events, …) lambda expressions are used to hold the “where” and “order by” clauses of the query. The filters will be applied to simple queries in BSL then returning the results:
IQueryable<AnEntity> query = from e in context.AnEntity select e; //get all entities query = query.Where(lambda_1); //filter them query = query.Where(lambda_2); ... var orderedQuery = query.OrderBy(sortingLambda_1); //then sort them orderedQuery = orderedQuery.ThenBy(sortingLambda_2); ... return orderedQuery.ToList(); //return the result
IMPLEMENTATION
The Interlinq library (http://interlinq.codeplex.com) was used for the SerializableExpression feature which allows us to box lambda expressions and send them through WCF. The generic class DynamicFilterClass<T> is used to hold both filtering and ordering expressions. A filter is in fact a SerializableExpression instance boxing a lambda expression (Expression<Func<T, bool>>). Given that the odering may be done by keys of different types the SortExpression class was written containing the sorting expression as a SerializableExpression boxing a lambda expression ( (Expression<Func<Customer, Tkey>> where Tkey is the type of the sorting key), the sorting direction (of type SortDirection) and the key type (of type KeyType) ). The key type is needed in order to be able to use the correct type on applying the sort expressions.
A complete example of using the DynamicFilter class is given below for Customers.
The BSL method:
public List<Customer> GetFilteredCustomers(DynamicFilterSort.DynamicFilterSort<Customer> filter)
{
using (var context = Db.Create())
{
var customers = from c in context.Customer
select c;
customers = filter.ApplyFiltersAndSorting(customers);
return customers.ToList();
}
}
Adding filters and sorting expressions in the UI:
void GetFilteredCustomersExample()
{
DynamicFilterSort<Customer> filter = new DynamicFilterSort<Customer>();
//we are getting all the customers that have registered in the last 200 days
// AND
//on the site with id = 28 (AND not OR)
filter.AddFilter(c => c.SiteId == 28);
filter.AddFilter(c => c.RegistrationDate > System.DateTime.Now.Subtract(System.TimeSpan.FromDays(200)));
//sort by first name
SortExpression<Customer> se = new SortExpression<BusinessServiceDataContract.Customer>(SortDirection.Descending);
se.SetExpression<string>((Expression<Func<Customer, string>>)(c => c.FirstName));
//sort by registration date
SortExpression<Customer> se2 = new SortExpression<BusinessServiceDataContract.Customer>();
se2.Direction = SortDirection.Descending;
se2.SetExpression<DateTime>((Expression<Func<Customer, DateTime>>)(c => c.RegistrationDate));
//the order of the sort expression collection counts
//first order by name
filter.AddSortExpression(se);
//then by registration date
filter.AddSortExpression(se2);
//sort by first name descending
SortExpression<Customer> se = new SortExpression<BusinessServiceDataContract.Customer>(SortDirection.Descending);
se.SetExpression<string>((Expression<Func<Customer, string>>)(c => c.FirstName));
//sort by registration date descending
SortExpression<Customer> se2 = new SortExpression<BusinessServiceDataContract.Customer>();
se2.Direction = SortDirection.Descending;
se2.SetExpression<DateTime>((Expression<Func<Customer, DateTime>>)(c => c.RegistrationDate));
//the order of the sort expression collection counts
//first order by name
filter.AddSortExpression(se);
//then by registration date
filter.AddSortExpression(se2);
System.Collections.Generic.List<Customer> customers = _customersService.GetFilteredCustomers(filter);
}
AND/OR
Added filters will be executed in the order they are added. If an OR operation is wanted a single filter must be created stating so.
//OR - get all the customers with the string "Mar" in EITHER the last name, first name OR the email
filter.AddFilter(c => c.LastName.Contains("Mar") || c.FirstName.Contains("Mar") || c.Email.Contains("Mar"));
is not equivalent to
//AND - get all the customers with the string "Mar" in the last name, first name AND email
filter.AddFilter(c => c.LastName.Contains("Mar"));
filter.AddFilter(c => c.FirstName.Contains("Mar"));
filter.AddFilter(c => c.Email.Contains("Mar"));
COMMUNICATION EXCEPTION
The communication exception may be raised on trying to serialize the data types used in the provided lambda expression as in the next bit of code:
filter.AddFilter(t => t.Status == TransactionStatus.Successful);
The TransactionStatus enum raises this exception. The solution would normally be adding the KnownTypeAttribute to the DynamicFilter class but in this case this would create a circular dependency.
The solution in this case is conversion to int:
filter.AddFilter(t => (int)t.Status == (int)TransactionStatus.Successful);
NOT SUPPORTED EXCEPTION
This class of exceptions is raised for cases in which a filter expression contains an operation that is not translatable to SQL. When this happens equivalent methods must be used.
SPECIFIC PROBLEMS
In the case when entities are required with their id (or any other field) in a list of given ids there are problems in serializing/deserializing the expression because of the List<T> type. The solution is converting the list to a basic type as in the example below:
List<int> products = new List<int>() { 460, 461, 462, 463 };
string s = String.Join(",", products);
productFilter.AddFilter(p => new List<string>(s.Split(',')).Contains(p.Id.ToString()));