Dynamic LINQ Query

I’ve been thinking for a couple days now how to create a dynamic LINQ query.  There is a prety good sample in C# here: http://blog.bvsoftware.com/post/2008/02/How-to-create-a-Dynamic-LINQ-Query-Programmatically.aspx

I went a little further with this.  One because I’m writing in vb.net so I had to convert this anyway, and two, because I’m passing my search through a webservice.  I didn’t want to have to create a variable for every possible search criteria, but at the same time I wanted flexibility.

The slimmed down function in my webservice is as follows:
Public Function LidDataSet(ByVal Search As DataTable, ByVal bFullText As Boolean) As DataSet
Dim matches As IQueryable(Of Persons) = From p In dbA.Persons Select p
For i As Integer = 0 To Search.Rows().Count - 1
 Dim field As String = Search.Rows(i).Item("Field").ToString()
 Dim value As String = Search.Rows(i).Item("Value").ToString()
 If bFullText Then
  If field = "Name" Then _
   matches = matches.Where(Function(p As Persons) p.Name.Contains(value))
  If field = "Email" Then _
   matches = matches.Where(Function(p As Persons) p.Email.Contains(value))
 Else
  If field = "Name" Then
   If InStr(value, "%") Then
    If value.IndexOf("%") > 0 Then
     matches = matches.Where(Function(p As Persons) p.Name.StartsWith(value.Substring(0, value.IndexOf("%"))))
    Else
     matches = matches.Where(Function(p As Persons) p.Name.EndsWith(value.Substring(value.IndexOf("%") + 1, value.Length() - 1)))
    End If

   Else
    matches = matches.Where(Function(p As Persons) p.Name = value)
   End If
  End If
 If field = "RegEmail" Then
  If InStr(value, "%") Then
   If value.IndexOf("%") > 0 Then
    matches = matches.Where(Function(p As Persons) p.Email.StartsWith(value.Substring(0, value.IndexOf("%"))))
   Else
    matches = matches.Where(Function(p As Persons) p.Email.EndsWith(value.Substring(value.IndexOf("%") + 1, value.Length() - 1)))
   End If
  Else
   matches = matches.Where(Function(p As Persons) p.Email = value)
  End If
 End If
Next
Dim linqQuery = From li In matches Select li
Return dwTools.ConvertLinqToDataSet(linqQuery)
End Function
 
For more information on the ConvertLinqToDataSet(linqQuery) function see my previous post: http://helpmonkey.wordpress.com/2008/06/30/pass-back-a-da…inq-webservicepass-back-a-dataset-from-a-linq-webservice/

I think the code should do a better job explaining this than I can put into words, but basically you have to pass in a datatable of the fields and values you want to search.  Since I couldn’t figure out how to build a string to use in my linq query i put an if statement to decide what field to search.  The limitations of this are pretty obvious, but it suits my needs. 

One major limit of this approach that I feel that I should mention is that this is only building an “AND query.”  That’s probably not the technical term, but what I mean is that if you search for more than one term, you are searching with the AND term.  So if you search for field: Name, value: “john” and field:Email, value: “john%”  you will be building a query that will return matches on Name = “john” AND Email.startswith(“john%”).