Better Searching Using LINQ

I'm going to come right out and say it, I love LINQ to SQL. I love it, I love it, I love it. Not only does it save us time by eliminating plumbing code, but it gives us new tools to write better code.

One thing that has always bothered me was I never found an elegent way to do multi-field searching. Take for instance the following class describing a customer in an application (written in pseudocode):

Public Class Customer
Property ID as string
Property FirstName as string
Property LastName as string
End Class

Somewhere you are going to have a screen that allows you to search for customers. You will probably have three textboxes where a user can put in values and a Search button that will execute a function that returns a list of customers that meet those criteria. Your function may look something like:

Public Function GetCustomers(byval _id as string,byval _firstName as string, byval _lastName as string) as List(Of Customer)

dim custQuery as string
if _id <> null AND_firstName <> null AND _lastName <> null Then
custQuery = "SELECT * FROM customer WHERE id = " & _id & " AND firstName = "....
elseif _id <> null AND _firstName <> null THEN
custQuery = "SELECT ...."
elseif
........
end if

return custQuery.execute()
End Function

I've also seen this type of logic written into a Stored Procedure to remove some of the ugliness from the application code itself. There are probably other more elegent ways to write the code but it all comes down to a bunch of If statements and checking for nulls, which is difficult to read and almost impossible to maintain. LINQ gives us a better way to do the above search.

Public Class CustomerSearch

Private dc as CustomerDataContext
Private mCustomerQuery as IQueryable(Of Customer)

Public Sub New()
dc = New CustomerDataContext
mCustomerQuery = From cust in dc.Customer
End Sub

Public Write-Only Property ID as String
Set(ByVal value as String)
If value.Length > 0 Then
mCustomerQuery = From cust in mCustomerQuery _
Where cust.ID = value
End If
End Set
End Property

Public Write-Only Property FirstName as String
Set(ByVal value as String)
If Value.Length >0 Then
mCustomerQuery = From cust in mCustomerQuery _
Where cust.FirstName = value
End If
End Set
End Property

Public Function ExecuteQuery() as List(Of Customer)
Return mCustomerQuery.ToList()
End Function
End Class

With the following class you could then do your search from your form using the following syntax:


Public Class frmCustomerSearch
Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
Handles btnSearch.Click

Dim mCustomers as List(Of Customer)
Dim custSearch as New CustomerSearch

'this is assuming a textbox called "txtID"
custSearch.ID = me.txtID.Text

'assuming a textbox called "txtFirstName"
custSearch.FirstName = me.txtFirstName.Text

mCustomers = custSearch.ExecuteQuery()

End Sub
End Class

This code is much more elegant, easier to maintain and easier to add functionality to later. As written it is not perfect but it does demonstrate a way we can use LINQ to write better code.