Rocks Thoughts

LINQ TO SQL Dynamic Queries

 
Public Function SearchUsers(ByVal firstName As String, ByVal lastName As String) As IEnumerable(Of Users)
    Dim db As New MyDataContext
    Dim query = From a in db.Users

    If Not String.IsNullOrEmpty(firstName)
        query = query & where a.FirstName.Contains(firstName)
    End If
    
    return query
End Function
        

I started to search online for the answer. The first attempt at being able to build a dynamic linq query came from Scott Guthrie's blog about the Dynamic LINQ Query Library. It seemed promising but there were two things that I didn't like about it. First, one of LINQ's advantages is it's ability to create type safe queries. The Dynamic Library allows you to write queries that are not type safe and not checked at compile time. Here is what my LINQ query would look like:

Dim db as New MyDataContext
Dim queryString As String

If Not String.IsNullOrEmpty(firstName)
    queryString = queryString & "FirstName = '" & firstName & "'"
End If

Dim query = db.Users.Where(queryString)

         

This query would not be checked during the compile. The other problem is I could not get the module from Microsoft to work. Everytime I added it to my solution I would get a TON of compile time errors.

So I moved on looking for my next way to do this.

I then I came across the LinqKit Predicate Builder. What was interesting was the first code example on that page did exactly what I wanted. It allowed me to build a dynamic linq query using the "Where" method of the "IQueryable(Of T)" interface. Here is what my code looked like after I saw that example:

Public Function SearchUsers(ByVal firstName As String, ByVal lastName As String) As IEnumerable(Of User)
    Dim db As New MyDataContext
    Dim query As IQueryable(Of User) = db.Users

    If Not String.IsNullOrEmpty(firstName) Then
        query = query.Where(Function(u As User) u.FirstName.Contains(firstName))
    End If

    If Not String.IsNullOrEmpty(lastName) Then
        query = query.Where(Function(u As User) u.LastName.Contains(lastName))
    End If

    Dim result As IEnumerable(Of User) = query.Select(Function(u As User) u)
    
    Return result
End Function
         

Now there is a slight problem with this method. The query built will have "AND" statements in the WHERE clause. Here is what the query will look like using the method above:

        SELECT 
            [t0].[UserId], 
            [t0].[FirstName], 
            [t0].[LastName], 
            [t0].[Address], 
            [t0].[Address2], 
            [t0].[City], 
            [t0].[State], 
            [t0].[PostalCode], 
            [t0].[EmailAddress] 
        FROM [dbo].[Users] 
            AS [t0] WHERE ([t0].[LastName] LIKE %rock%) AND ([t0].[FirstName] LIKE %chris%)
        

What if I wanted "OR" statements in my where clause? My next blog will be about building a query where you can have "Or" statements by using the C# LinqKit.

Update: LINQ TO SQL Dynamic Queries #2 (Or statements)

Update #2: Jim Wooley has an alternative way as well

Update #3: LINQ TO SQL Dynamic Queries #3(Or & And statements)

posted @ Thursday, January 24, 2008 11:47 PM