Software Experts
Call Us: +40 770 613 713 | EU
12th Jan 2015 | by: cetus

INTRODUCTION

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:

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:

The resulted IQueryable object can itself have queries applied on it using lambda expressions:

The data is not retrieved until a specific call for it is made.

or

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,

is equivalent to

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:

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:

Adding filters and sorting expressions in the UI:

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.

is not equivalent to

CommunicationException

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:

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:

NotSupportedException

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:

Share This Post

About the Author: cetus

Leave a Reply

Your email address will not be published.