Query Abstraction with LINQ

So recently I was playing with an abstraction for dynamic LINQ queries. The typical usage scenario arises when one needs to search one or more tables with “dynamic” filter patterns. Usually this is one of those intoxicating multiple search criteria (“Advanced Search”) screens.

To illustrate my point, let’s work with this basic schema, below:

So there is nothing fancy in the design here. We’re going to have the opportunity to query these two tables in a number of ways, as a short example, with any combination of the following:

By Surname/Given Name
Created after a specific date (e.g. CreatedDate > 20090401)
Whether the customer is active (e.g. IsActive = True | False)
By “Customer Type” (e.g. Commercial, Residential, Government, International)
By Postcode (if exists)

After converting this to a model we can query using LINQ (by using the ADO.Net Entity Framework or LINQ to SQL as an example), we can expose the model for querying, in the fashion (note: I’m using LINQ to SQL for this example):

using (DataClassesDataContext context = new DataClassesDataContext())
{
var customers = (from c in context.Customers
select c).ToList();

       var activeCustomers = (from c in context.Customers
                                          where c.IsActive == true
                                          select c).ToList();
}

If you had optional query clauses you could specify them like so:

using (DataClassesDataContext context = new DataClassesDataContext())
{
         var customers = (from c in context.Customers
                                   select c);

          if (searchActive)
          {
              customers = customers.AsQueryable().Where(x => x.IsActive == true);
          }

          //use the customers to trigger DB query..
}

However, it’s a rare day when such an approach is all that maintainable – not to mention, it’s likely you could have a number of different combinations of filter clauses. Introducing Query Abstraction!

Let’s create a class and call it CustomerQuery. The constructor shall take an argument – the DataContext. In the constructor we will construct a “base” query which will be the basis for all queries – you can choose to make this query as basic or restricted as common sense allows the shortest query would be something like “SELECT * FROM Customers” – note the equivalent in the example below.

    /// <summary>
    /// Our Abstracted Query Class
    /// </summary>
    public class CustomerQuery
    {
        #region Private Variables

        private IQueryable<Customer> _customerQuery;

        #endregion

        #region Constructor

        /// <summary>
        /// Pass a Data Context to use this Class to Query
        /// </summary>
        /// <param name="context"></param>
        public CustomerQuery(DataClassesDataContext context)
        {
            _customerQuery = (from c in context.Customers
                                         select c);

            //note, I’d recommend a minimum restriction on the base clause to prevent selecting all rows in the table
        }

       #endregion
    }

Now, to make use of this class, we can simply add a number of public functions, like so:

        /// <summary>
        /// Filter by Customer Type
        /// </summary>
        /// <param name="customerType"></param>
        public void FilterByCustomerType(CustomerType customerType)
        {
            _customerQuery = _customerQuery.AsQueryable().Where(x => x.CustomerType == customerType);
        }
        /// <summary>
        /// Filter only active or inactive Customers
        /// </summary>
        /// <param name="active"></param>
        public void FilterByActive(bool active)
        {
            _customerQuery = _customerQuery.AsQueryable().Where(x => x.IsActive == active);
        }

        /// <summary>
        /// Search by Postcode
        /// </summary>
        /// <param name="postcode"></param>
        public void FilterByPostCode(string postcode)
        {
            _customerQuery = _customerQuery.AsQueryable().Where(x => x.Postcode == postcode);
        }

Finally, to execute a search, simply add a function which causes the query to be executed, like so:

#region Execute

       /// <summary>
       /// Execute a Search
       /// </summary>
       /// <returns></returns>
       public ReadOnlyCollectio
n<Customer> Search()
       {
           return _customerQuery.ToList().AsReadOnly();
       }

       /// <summary>
       /// Returns the number of matching records
       /// </summary>
       /// <returns></returns>
       public int Count()
       {
           return _customerQuery.Count();
       }

#endregion

Putting it all together now, to execute a variable parameter search is as simple as the following code – note that a search will not be performed until either .Search() or .Count() are called:

CustomerQuery query = new CustomerQuery(context);
query.FilterByActive(true);
query.FilterByPostCode("90210");

var results = query.Search();
int count = query.Count();

Now specifying multiple search parameters is incredibly easy, and you can hide some of the query implementation away from the calling code. Very useful for removing some ugly UI-based logic from your code (like only adding where clauses based on filtering “not selected” magic numbers and the like.

Author

Rob Sanders

Leave a comment Cancel reply