LinqSQL tip #1– dynamic queries

I had to build a reporting page for a client the other day that pulls a load support ticket data from the DB and makes it look nice on the screen.

Simple I thought, once the first version was done, I get the emails asking for date filters, easy.

Then the filters for member of staff, easy.

Then for the categories, easy

And then the sub categories, but sometimes we will want to filter by  one and sometimes all of them, or maybe one or two….

This got me stuck there didn’t seem to be anyway to look at a list of checkboxes and add a ‘where’ statement to the Linq statement for each subcategory selected.

In SQL you would build the query up, using and/or statements to get the data back you need, in Linq this is farily tough to do on the fly in standard Linq code.

After a fair old time on the net I came across the LinqKit which has a Predicate builder!
This is an awesome piece of code, I could have tried to build something like this (I would never have got there in the time I had), but why try and reinvent the wheel when there is a handy .dll that does it already!

All I needed to do was create an array of the categories I need to filter by and throw them at the Predicate builder.

Function FilterSubCategories(ByVal iQuery As IQueryable(Of DB.Issue), ByVal  _ searchTerms As ArrayList) As IQueryable(Of Issue)

    Dim predicate = PredicateBuilder.[False](Of Issue)()
    For Each keyword As String In searchTerms
        Dim temp As String = keyword
        predicate = predicate.[Or](Function(p As Issue) p.SubCategoryID.Equals(temp))
    Next
    Return iQuery.Where(predicate)
End Function

The function simply returns a queryable set of data that I can either bind to a control of continue to work with.

There are more extension sin the LinqKit, if you are looking for an easy way to deal with dynamic Linq clauses, this is definately worth a look.