By tradition it was always quite a challenge to create custom filtering on objects and dynamically query databases. Or to say least a lot of work. LINQ makes this type of querying in code much easier. In this article I wish to elaborate on the "how-to" and advantages of providing a dynamic and type-safe 'engine' for developers on which you can query any random property.

We are going to demonstrate this by using an expression tree, available from the System.Linq.Expressions namespace, to easily achieve dynamic LINQ queries for usage in your code.

Codesample

The snippet below is a fully functional example on how to create a function that returns a filtered list of results, based on a dynamic lambda query:

/// <summary>

/// 'Person' class for demonstration purposes.

/// </summary>

public class Person

{

    public string Name { get; set; }

    public string FamilyRole { get; set; }

    public DateTime BirthDate { get; set; }

}

class Program

{

    static void Main(string[] args)

    {

        // Retrieve a list of Persons in the collection which are under age (< 18):

        List<Person> personsUnderAge = Retrieve(p => p.BirthDate.Year > (DateTime.Now.Year - 18));

        foreach (Person p in personsUnderAge)

        {

            // Spit out the results:

            Console.WriteLine("{0} is under age ({1} years)", p.Name, DateTime.Now.Year - p.BirthDate.Year);

        };

        Console.ReadLine();

    }

    public static List<Person> Retrieve(Expression<Func<Person, bool>> expression)

    {

        // Container with just some random Persons on which we can query:

        IQueryable<Person> returnValue = new List<Person>

        {

            new Person{ Name = "Harry", FamilyRole = "Father", BirthDate = new DateTime(1968,2,1)},

            new Person{ Name = "Carry", FamilyRole = "Mother", BirthDate = new DateTime(1974, 1, 28)},

            new Person{ Name = "Barry", FamilyRole = "Child", BirthDate = new DateTime(1995, 6, 18)},

            new Person{ Name = "Larry", FamilyRole = "Chile", BirthDate = new DateTime(1997, 3, 28)}

        }.AsQueryable();

        // Parse the supplied expression and return the filtered result:

        return returnValue.Where(expression).ToList();

    }

}

The magic happens in the Retrieve(Expression<Func<Person, bool>> expression) function. Due to this parameter definition we can process a lambda query and use it to query the results. We can now call this function anywhere - and provide a where clause - using just the following line:

List<Person> personsUnderAge = Retrieve(p => p.BirthDate.Year > (DateTime.Now.Year - 18));

Now you have achieved allowing a lambda query to be a parameter for a function or method. Excellent! And now you can start think of the potential it has. In my case, the origin was creating a dynamic end-user GUI for letting end-users query results together themselves, much like that of Dynamics CRM:

By using this approach you have some major advantages:

• Create criteria's such as where clauses by filtering on *any* parameter (property) from the source
• Full intellisense support when typing the function in code
• Compiler starts yelling if the query doesn't comply (when you change a property, for example)
• Less codelines required by reducing the amount of function/method overloads you need to create
  (i.e. RetrieveAllCustomers, RetrieveAllUnderAgeCustomers, RetrieveAllCustomersWithBeards etc.)

No security breach, no worries

This is all nice and well when connecting to a custom collection like in our example, but you may wonder how this could compromise security when using dynamic LINQ queries on a database. First, realize that security risks are always present when connecting to a database. Although LINQ to SQL include new ways to work with databases, it does not provide additional security mechanisms.

But look deeper and you know additional security isn't provided simply because it isn't required. Officially, MSDN states the following on the subject of SQL injection:

"... SQL injection has been a significant risk for traditional SQL queries formed by concatenating user input. LINQ to SQL avoids such injection by using SqlParameter in queries. User input is turned into parameter values. This approach prevents malicious commands from being used from customer input."

Which, in other words, means that LINQ to SQL by default is type-safe. Therefor when you call the .Where() method on a queryable collection, you are by default protected from these nasty side-effects.

Limitations with serializing

The disadvantage is that these expression trees by default cannot be serialized/deserialized.

It is therefor not possible to use such a functional parameter on a front layer such as a webservice or a WCF service.

But again no worries! At least not if you are comfortable with putting in a little extra work and discard some of the above mentioned advantages. In this case you can also use the Dynamic Query Library. Scott Guthrie published a great article on the usage of this code sample, which can be downloaded from the MSDN Visual Studio 2008 Code Samples page. Without going into too much depth, you can use this library to enter LINQ lambda queries as a native String, such as this example by Scott shows:

var query = db.Customers.Where("City = @0 and Orders.Count >= @1", "London", 10)

    .OrderBy("CompanyName")

    .Select("new(CompanyName as Name, Phone)");

Using the Dynamic Query Library is a nice alternative for creating dynamic LINQ queries, but - as mentioned - has a downside: the lack of Intellisense and compiler support. You basically just parse strings and let the runtime compiler figure out if it works. However, it is a workable solution for transporting LINQ through a service.