LinqSQL tip #2– linqDatasource binding funkyness

Binding controls via a datasource is nice and easy, however when you try and change the way the data is displayed in a dropdown for example, Linq can be a pain.

Example 1 – Add text variable flag to show what entry is a primary category and which are secondary categories

<asp:LinqDataSource ID="LDSRentalDefinition"
runat="server" ContextTypeName="DB"
Select="new (ID, (iif(isPrimary==true,' ','+') + Name)  as TextValue)"
TableName="Categories" OrderBy="iif(isPrimary==true,' ','+'),Name" >

Weird thing is in the SELECT, IF statements don’t work, but old fashioned IIF statements do.

Still gets me that some C# code works inside the LinqDataSource on a VB page, but some VB works as well.
( || does work where | doesn’t )

Its a nice visual thing for the users as they can see straight away that any child category has a + in front of it in the dropdownlist

Annoyingly I was only allowed a single character inside the ‘’, page would crash otherwise.

Example 2 – add multiple data fields to a single item in the listbox

This is an easier one, but still very powerful to make your UI make more sense to the user

Select="new (ID, (Name + ' ' + Description) as TextValue”

List box items are displayed as “Bob boss”, “John cleaner” rather than just a first name.

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.