Dynamically extending LINQ queryies without building expression trees by ThinqLinq

Dynamically extending LINQ queryies without building expression trees

As more content is added to this site, I wanted to add a searching function. The implementation here is relatively simple. We will parse the input string into the various words represented and perform a TSQL LIKE filter in the WHERE clause. Doing this on a single string is relatively simple. We just use the String.Contains method in the WHERE clause as follows:

Dim query = From p in dc.PostItems _
                    Where p.Description.Contains(InputValue) _
                    Select p

This LINQ query translates into a TSQL statement similar to the following::

SELECT * FROM PostItem WHERE Description LIKE @P0

Where @P0 = '%' + InputValue + '%'

However, we don't want to limit to our search when multiple words are entered. We would like to add multiple filters linked together with the AND logical expression as follows:

SELECT * from PostItem
WHERE Description LIKE @P0 AND
               Description LIKE @P1

Of course, we would need to be able to add as many of these as there are words in the search string. Although we could take the time to build up the necessary expression trees manually, doing so would be overkill and potentially less maintainable. Because LINQ is composable, there is an easier way. In this case, we can generate the baseline query which returns an IQueryable data structure. Once we have that, we can continue adding additional expressions  by just adding to the query. Using this method, we can create as many parameters we want (up to the server's limit on parameters)

Dim words() As String = SearchString.Split(" "c)
Dim query = dc.PostItems.AsQueryable
For Each word In words
   Dim thisWord As String = word
   query = query.Where(Function(post) (post.Description.Contains(thisWord)))
Next

Naturally, once we have this query, we could add projection, sorting, paging, and any of the other LINQ expressions. Realize here, we are not creating n-level of sub selects. Each of the LINQ where clauses we add are combined into a single TSQL WHERE clause with the various elements ANDed together. Thus if our search string were "Thinq Linq IQueryable search", the resulting TSQL would be,

SELECT [t0].[Id], [t0].[Title], [t0].[Description], [t0].[Author], [t0].[PublicationDate], [t0].[TimeStamp]
FROM [dbo].[PostItems] AS [t0]
WHERE ([t0].[Description] LIKE @p0) AND
             ([t0].[Description] LIKE @p1) AND
             ([t0].[Description] LIKE @p2) AND
             ([t0].[Description] LIKE @p3)

Nicely parameterized and consise, just like we want it. We could easily extend this example using a regular expression to parse the input string rather than using the string.Split. Notice in this example how we can use the OfType method to strongly type the results. If we didn't use it, the resulting matchItem would be of type Object and we would need to unduly cast the resulting type repeatedly.

Dim matches = System.Text.RegularExpressions.Regex.Matches(SearchString, "\w*")
Dim query = dc.PostItems.AsQueryable
For Each matchItem In matches.OfType(Of System.Text.RegularExpressions.Match)
   
Dim thisMatch As String = matchItem.Value
   
query = query.Where(Function(post) (post.Description.Contains(thisMatch)))
Next

Give the search functionality a try on this site and see how it works. At this point each word is a separate parameter. We do not combine words if they are enclosed in quotes like many search engines do. That is an exercise for another time.

Posted on - Comment
Categories: LINQ - VB -
comments powered by Disqus