Linq Tutorial (Part 4): Dynamic Linq Queries

Description and code samples
Table of Contents
Summary

There are cases where the flexibility to dynamically construct queries on the fly is required.

In case of  business intelligence UI within an application that allows an end-user to use drop-downs to build and express their own custom queries/views on top of data.

These queries must be built at run-time and several options are available.

Operations
Multiple "Where" extension methods and the iQueryable<T> interface

Each of the Where method to use is mapped to a search parameter available to the end-user.

How to do it

In order to create a Filter method the following must be followed:

  1. Create LINQ to SQL Data Context (in case of Linq to SQL being used);
  2. Declare an IQueryable<T> interface and initialize it with the table properties in the database; in this case, we name it Customers.
  3. Create and add the Where clause dynamically by filtering every step of the way starting from search parameters on the UI form

Sample 1:

public static List<Customer> GetFilteredData(
				int? CustomerID,
				       string FirstName, string LastName,
				       string CustomerType, bool Active)
{
    DataClasses1DataContext db = new DataClasses1DataContext();
    IQueryable<Customer> oDataQuery = db.Customers;

    //Filter by Integer
    if (CustomerID != null)
    {
        oDataQuery = oDataQuery.Where(a => a.CustomerID == CustomerID);
    }

    //Filter by containing words
    if (FirstName.Trim().Length > 0)
    {
        oDataQuery = oDataQuery.Where(a => a.FirstName.Contains(FirstName));
    }

    //Filter by containing words
    if (LastName.Trim().Length > 0)
    {
        oDataQuery = oDataQuery.Where(a => a.LastName.Contains(LastName));
    }

    //Filter by a Foreign Key Relationship
    if (CustomerType.Trim().Length > 0)
    {
        oDataQuery = oDataQuery.Where(a => a.CustomerTypeID == (
          					from b in db.CustomerTypes
						where b.CustomerType1 == CustomerType
						select b.CustomerTypeID).FirstOrDefault());
    }

    //Filter by boolean
    oDataQuery = oDataQuery.Where(a => a.Active == Active);

    return oDataQuery.ToList();
}

The HasValue() or IsNullOrEmpty() can be used instead on each step in the code above.

Dynamic Linq Library

The DynamicQuery library against any LINQ data provider (including LINQ to SQL, LINQ to Objects, LINQ to XML, LINQ to Entities, LINQ to SharePoint, LINQ to TerraServer, etc) can be used:

Instead of using language operators or type-safe lambda extension methods to construct the LINQ queries, the dynamic query library provides to use string based extension methods

Remarks

The Dynamic Library can be dowloaded here

How to do it

Refer to this article for details of how to use this library.

Using expression trees
How to do it

Refer to this article for dynamic Linq using expression trees.