In my last post about LINQ to SQL Dynamic queries I showed you how to create dynamic sql statements with a catch; you would only get AND operators in the WHERE statement of the query. Thanks to LINQ Kits' Predicate Builder, you can now have OR operators as well. However, one of the problems with LINQ Kit is it was created for C# users. Not that there's anything wrong with that. That allows me to write a post explaining how to use it in VB. Thanks LINQ Kit!
The easiest way to show you how to use it is by going back to the example in the first dynamic query post.
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
Like I said before, this example creates this query:
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%)
Now we want to change the method above to create OR operators. Here is the code:
' need these imports:
Imports LinqKit.Linq
Imports LinqKit
Imports LinqKit.Extensions
Imports System.Linq.Expressions
Imports System.Data.Linq
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
Dim predicate as Expression(Of Func(Of User, Boolean)) = PredicateBuilder.False(Of User)()
If Not String.IsNullOrEmpty(firstName) Then
predicate = predicate.Or(Function(u As User) u.FirstName.Contains(firstName))
End If
If Not String.IsNullOrEmpty(lastName) Then
predicate = predicate.Or(Function(u As User) u.LastName.Contains(lastName))
End If
Dim result As IEnumerable(Of User) = db.Users.Where(predicate)
Return result
End Function
So what does this code do? Let's go over a few lines from the method above.
Dim predicate as Expression(Of Func(Of User, Boolean)) = PredicateBuilder.False(Of User)()
This is setting up an Expression(Of TDelegate) type from the System.Linq.Expression class. The definition of which is "Represents a strongly typed lambda expression as a data structure in the form of an expression tree. This class cannot be inherited."
O.K., let me TRY to explain that in English. You're creating an Expression that will require a function (or lambda expression) which will be defined as something similar to:
Function MyLamdbaExpression(u As User) As Boolean.
The function (lambda expression) will accept a type of User as the single parameter and return a Boolean value. Hence the "Function(u as User) return u.FirstName.Contains(firstName)" lambda expression. There are a few resources about lambda expressions and generic delegates that I list at the end of the post if you need more help understanding this topic.
The LINQ Kit authors also explain that the true and false methods on the PredicateBuilder class do nothing special: they are convenient shortcuts for creating an Expression(Of Func(Of T, Boolean)) that initially evaluates to true of false. When you're building a predicate by repeatedly stacking and/or conditions, it's useful to have a starting point of either true or false (respectively).
predicate = predicate.Or(Function(u As User) u.FirstName.Contains(firstName))
For the purpose of LINQ TO SQL this is adding an OR operator to the query. If only one call is made to the predicate.Or function, LINQ to SQL will create just the WHERE statement. Any subsequent calls will append an "OR" operator to the "WHERE" clause with the appropriate qualifier statement (e.g. where firstname like '%chris%' OR lastname like '%rock%').
Dim result As IEnumerable(Of User) = db.Users.Where(predicate)
This is almost self-explanatory and to keep it simple we are basically passing our expression to the Where method of System.Linq.Queryable type. This will cause LINQ to SQL to build our query with the Or operators. We should come out with a query that looks like this:
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%) OR [t0].[FirstName] LIKE %chris%)
So there you go. You can create dynamic queries with LINQ that will create AND operators and OR operators.
Update: LINQ TO SQL Dynamic Queries (And's & Or's)
Here are the resources I promised you.
Technorati Tags: LINQ,LINQ TO SQL,.NET 3.5
posted @ Wednesday, February 27, 2008 11:25 AM