Implementing Dynamic Searching Using LINQ

Published 29 August 07 03:51 PM

A common requirement in forms-over-data applications is that users be able to search by any combination of fields to construct a dynamic query at run time. For example, the search feature in this application allows the user to find all records that meet criteria defined on multiple columns:

LINQ makes it easy to write powerful queries like this over various data sources; for instance, we can use the following query to find all Orders shipped to a given country within a user-specified timeframe:

Dim query = From order In db.Orders _

Where order.ShipCountry = txtCountry.Text _

And order.ShippedDate >= dtpStartDate.Value _

And order.ShippedDate <= dtpEndDate.Value

This is easy at compile-time, but what if we want to check the date the order was entered instead of the date it was shipped? In this case we’d have to write a separate query using order.OrderDate. Doing this dynamically at runtime isn’t all that difficult if you’re just building up a SQL string, but how would we do this with LINQ? Doesn’t LINQ require me to specify the criteria at compile time in order to construct the query? Fortunately the answer’s no, LINQ supports constructing dynamic queries at runtime through the Expression Tree API and the Expression Compiler.

In Visual Studio 2008, any valid VB expression can be represented as an expression tree. What we need to do is create an Expression Tree to represent the user’s criteria, and then pass it to the LINQ to SQL runtime to do the SQL translation. So for the first part of our Where clause above it’d look something like this:

Dim p = Expression.Parameter(GetType(Order), "")

Dim order = GetType(Order).GetProperty("ShipCountry")

Dim expr = Expression.Equal(Expression.PropertyOrField(p, order.Name), Expression.Constant("Germany"))

Dim predicate = Expression.Lambda(Of Func(Of Order, Boolean))(expr, New ParameterExpression() {p})

>Yikes, that’s a mouthful…and we’ve only done 1/3 of the Where clause so far! I definitely don’t want to write 12 lines just to construct a Where clause at runtime. What I’d like to be able to do is write an extension method called CreateCondition that would allow me to construct the expression tree in one easy line, something like this:

Dim condition1 = db.Orders.CreateCondition("ShipCountry", Compare.Equal, "Germany")

And then for ShippedDate we could make these two conditions:

Dim startDate? = #1/1/1997#

Dim endDate? = #1/31/1997#

Dim condition2 = db.Orders.CreateCondition("ShippedDate", Compare.GreaterThanOrEqual, startDate)

Dim condition3 = db.Orders.CreateCondition("ShippedDate", Compare.LessThanOrEqual, endDate)

(Note: There’s a similar sample in Beta2 called DynamicQueries which does a lot more than what we’re looking at here; think of this as an easier-to-write subset of DynamicQueries).

Notice that we passed in a String the first time, and a Nullable Date literal for the next two; we can do this because the CreateCondition method is generic and infers the type based on the parameter passed in. We now need to combine the conditions into one big condition:

Dim c = Condition.Combine(condition1, Compare.And, condition2, condition3)

Or we could take advantage of Operator Overloading and do it this way (equivalent to the line above):

Dim c = condition1 And condition2 And condition3

Ok so now that we’ve constructed our Condition object, let’s use it to filter the data:

'Filter out all Orders that don't match the Condition

'Note that the query isn't executed yet to due to deferred execution

Dim filteredQuery = db.Orders.Where(c)

'We can now perform other operations (such as Order By) on filteredQuery

Dim query = From row In filteredQuery _

Order By row.OrderDate, row.OrderID _

Select row

'Executes the query and displays the results in DataGridView1

DataGridView1.DataSource = query

So far so good, we’re using LINQ over a dynamically-constructed condition and everything’s strongly-typed. We’re still only hitting the database once since deferred execution ensures the query doesn’t run until we actually enumerate the results (through databinding). The Condition API has made it a lot easier to construct and compile the expression trees, but we’d still have to write a fair bit of code to construct these conditions based on user input. That’s where the ConditionBuilder control comes in:

This allows the user to dynamically specify the criteria at runtime:

Notice that for Date fields we automatically get a DateTimePicker instead of a TextBox, and Booleans would result in a CheckBox.

Ok so we’ve covered what the user experience is at runtime, but how do we actually create the Condition API? There’s a fair bit of documentation in the code below so I won’t go through all of it, but here’s the basics:

1. There are three main classes: Condition, Condition(Of T), and Condition(Of T, S)

a. Condition is an abstract class that is used to construct the generic versions. By structuring it this way we get the benefits of generic type parameter inference – i.e. we don’t have to worry about passing the generic type parameters to the method; the factory method figures it out for us.

b. Condition(Of T) is used to join multiple conditions together. T is the element type (i.e. Order in the example above).

c. Condition(Of T, S) is the simplest type; it represents an “object.propery <comparison> value” expression. The type parameter S will be inferred to be the type of the value passed in (i.e. String, Date, Boolean etc…).

2. For local execution of a query we compile the LambdaExpression to a delegate so that it can be executed in-memory. The user can invoke this delegate by calling the Matches method.

'Compile the lambda expression into a delegate

del = DirectCast(LambdaExpr.Compile(), Func(Of T, Boolean))

3. The extension methods at the bottom are defined on IQueryable(Of T) for remote execution, and IEnumerable(Of T) for local execution.

The complete solution is attached in the file DynamicCondition.zip below, note that you’ll need to update the ConnectionString in app.config to point to your version of Northwind.

Imports System.ComponentModel

Imports System.Linq.Expressions

Imports System.Runtime.CompilerServices

Public Module DynamicQuery

Public MustInherit Class Condition

'Used to ensure we get the same instance of a particular ParameterExpression

'across multiple queries

Private Shared ParamTable As New Dictionary(Of String, ParameterExpression)

'The expression tree which will be passed to the LINQ to SQL runtime

Protected Friend LambdaExpr As LambdaExpression

'Enumerates all the different comparisons which can be performed

Public Enum Compare

[Or] = ExpressionType.Or

[And] = ExpressionType.And

[Xor] = ExpressionType.ExclusiveOr

[Not] = ExpressionType.Not

Equal = ExpressionType.Equal

[Like] = ExpressionType.TypeIs + 1

NotEqual = ExpressionType.NotEqual

[OrElse] = ExpressionType.OrElse

[AndAlso] = ExpressionType.AndAlso

LessThan = ExpressionType.LessThan

GreaterThan = ExpressionType.GreaterThan

LessThanOrEqual = ExpressionType.LessThanOrEqual

GreaterThanOrEqual = ExpressionType.GreaterThanOrEqual

End Enum

'Constructs a Condition with T as the element type and S as the value's type

Public Shared Function Create(Of T, S)(ByVal dataSource As IEnumerable(Of T), _

ByVal propertyName As String, _

ByVal condType As Compare, _

ByVal value As S) As Condition(Of T, S)

Return New Condition(Of T, S)(propertyName, condType, value)

End Function

'Constructs a Condition with T as the element type and valueType as the value's type

'This is useful for situations where you won't know the value's type until runtime.

Public Shared Function Create(Of T)(ByVal dataSource As IEnumerable(Of T), _

ByVal propertyName As String, _

ByVal condType As Compare, _

ByVal value As Object, _

ByVal valueType As Type) As Condition(Of T)

Return New Condition(Of T)(propertyName, condType, value, valueType)

End Function

''' <summary>

''' Creates a Condition which combines two other Conditions

''' </summary>

''' <typeparam name="T">The type the condition will execute against</typeparam>

''' <param name="cond1">The first Condition</param>

''' <param name="condType">The operator to use on the conditions</param>

''' <param name="cond2">The second Condition</param>

''' <returns>A new Condition which combines two Conditions into one according to the specified operator</returns>

''' <remarks></remarks>

Public Shared Function Combine(Of T)(ByVal cond1 As Condition(Of T), _

ByVal condType As Compare, _

ByVal cond2 As Condition(Of T)) As Condition(Of T)

Return Condition(Of T).Combine(cond1, condType, cond2)

End Function

'Combines multiple conditions according to the specified operator

Public Shared Function Combine(Of T)(ByVal cond1 As Condition(Of T), _

ByVal condType As Compare, _

ByVal ParamArray conditions() As Condition(Of T)) As Condition(Of T)

Return Condition(Of T).Combine(cond1, condType, conditions)

End Function

'Combines two Expressions according to the specified operator (condType)

Protected Shared Function CombineExpression(ByVal left As Expression, _

ByVal condType As Compare, _

ByVal right As Expression) As Expression

'Join the Expressions based on the operator

Select Case condType

Case Compare.Or : Return Expression.Or(left, right)

Case Compare.And : Return Expression.And(left, right)

Case Compare.Xor : Return Expression.ExclusiveOr(left, right)

Case Compare.Equal : Return Expression.Equal(left, right)

Case Compare.OrElse : Return Expression.OrElse(left, right)

Case Compare.AndAlso : Return Expression.AndAlso(left, right)

Case Compare.NotEqual : Return Expression.NotEqual(left, right)

Case Compare.LessThan : Return Expression.LessThan(left, right)

Case Compare.GreaterThan : Return Expression.GreaterThan(left, right)

Case Compare.LessThanOrEqual : Return Expression.LessThanOrEqual(left, right)

Case Compare.GreaterThanOrEqual : Return Expression.GreaterThanOrEqual(left, right)

Case Compare.Like

'For the Like operator we encode a call to the LikeString method in the VB runtime

Dim m = GetType(CompilerServices.Operators).GetMethod("LikeString")

Return Expression.Call(m, left, right, Expression.Constant(CompareMethod.Binary))

Case Else

Throw New ArgumentException("Not a valid Condition Type", "condType", Nothing)

End Select

End Function

'Since both type parameters must be the same, we can turn what would normally

'be a Func(Of T, T, Boolean) into a Func(Of T, Boolean)

Protected Shared Function CombineFunc(Of T)(ByVal d1 As Func(Of T, Boolean), _

ByVal condType As Compare, _

ByVal d2 As Func(Of T, Boolean)) As Func(Of T, Boolean)

'Return a delegate which combines delegates d1 and d2

Select Case condType

Case Compare.Or : Return Function(x) d1(x) Or d2(x)

Case Compare.And : Return Function(x) d1(x) And d2(x)

Case Compare.Xor : Return Function(x) d1(x) Xor d2(x)

Case Compare.Equal : Return Function(x) d1(x) = d2(x)

Case Compare.OrElse : Return Function(x) d1(x) OrElse d2(x)

Case Compare.AndAlso : Return Function(x) d1(x) AndAlso d2(x)

Case Compare.NotEqual : Return Function(x) d1(x) <> d2(x)

Case Compare.LessThan : Return Function(x) d1(x) < d2(x)

Case Compare.GreaterThan : Return Function(x) d1(x) > d2(x)

Case Compare.LessThanOrEqual : Return Function(x) d1(x) <= d2(x)

Case Compare.GreaterThanOrEqual : Return Function(x) d1(x) >= d2(x)

Case Else

Throw New ArgumentException("Not a valid Condition Type", "condType")

End Select

End Function

'Guarantees that we get the same instance of a ParameterExpression for a given type t.

Protected Shared Function GetParamInstance(ByVal dataType As Type) As ParameterExpression

'Parameters are matched by reference, not by name, so we cache the instances in a Dictionary.

If Not ParamTable.ContainsKey(dataType.Name) Then

ParamTable.Add(dataType.Name, Expression.Parameter(dataType, dataType.Name))

End If

Return ParamTable.Item(dataType.Name)

End Function

End Class

Public Class Condition(Of T) : Inherits Condition

'Delegate that contains a compiled expression tree which can be run locally

Friend del As Func(Of T, Boolean)

Friend Sub New()

End Sub

Friend Sub New(ByVal propName As String, ByVal condType As Compare, ByVal value As Object, ByVal valueType As Type)

'Split the string to handle nested property access

Dim s = propName.Split("."c)

'Get the PropertyInfo instance for propName

Dim pInfo = GetType(T).GetProperty(s(0))

Dim paramExpr = GetParamInstance(GetType(T))

Dim callExpr = Expression.MakeMemberAccess(paramExpr, pInfo)

'For each member specified, construct the additional MemberAccessExpression

'For example, if the user says "myCustomer.Order.OrderID = 4" we need an

'additional MemberAccessExpression for "Order.OrderID = 4"

For i = 1 To UBound(s)

pInfo = pInfo.PropertyType.GetProperty(s(i))

callExpr = Expression.MakeMemberAccess(callExpr, pInfo)

'ConstantExpression representing the value on the left side of the operator

Dim valueExpr = Expression.Constant(value, valueType)

Dim b As Expression = CombineExpression(callExpr, condType, valueExpr)

LambdaExpr = Expression.Lambda(Of Func(Of T, Boolean))(b, New ParameterExpression() {paramExpr})

'Compile the lambda expression into a delegate

del = DirectCast(LambdaExpr.Compile(), Func(Of T, Boolean))

End Sub

'Combines two conditions according to the specified operator

Friend Overloads Shared Function Combine(ByVal cond1 As Condition(Of T), _

ByVal condType As Compare, _

ByVal cond2 As Condition(Of T)) As Condition(Of T)

Dim c As New Condition(Of T)

Dim b As Expression = CombineExpression(cond1.LambdaExpr.Body, _

condType, _

cond2.LambdaExpr.Body)

Dim paramExpr() = New ParameterExpression() {GetParamInstance(GetType(T))}

'Create the LambdaExpression and compile the delegate

c.LambdaExpr = Expression.Lambda(Of Func(Of T, Boolean))(b, paramExpr)

c.del = Condition.CombineFunc(cond1.del, condType, cond2.del)

Return c

End Function

'Combines multiple conditions according to the specified operator

Friend Overloads Shared Function Combine(ByVal cond1 As Condition(Of T), _

ByVal condType As Compare, _

ByVal ParamArray conditions() As Condition(Of T)) As Condition(Of T)

Dim finalCond = cond1

For Each c In conditions

finalCond = Condition.Combine(finalCond, condType, c)

Return finalCond

End Function

'Run query locally instead of remotely

Public Function Matches(ByVal row As T) As Boolean

Return del(row) 'passes the row into the delegate to see if it's a match

End Function

'Overloaded operators - allows syntax like "(condition1 Or condition2) And condition3"

Public Shared Operator And(ByVal c1 As Condition(Of T), ByVal c2 As Condition(Of T)) As Condition(Of T)

Return Condition.Combine(c1, Compare.And, c2)

End Operator

Public Shared Operator Or(ByVal c1 As Condition(Of T), ByVal c2 As Condition(Of T)) As Condition(Of T)

Return Condition.Combine(c1, Compare.Or, c2)

End Operator

Public Shared Operator Xor(ByVal c1 As Condition(Of T), ByVal c2 As Condition(Of T)) As Condition(Of T)

Return Condition.Combine(c1, Compare.Xor, c2)

End Operator

End Class

'Represents a condition like "object.Property = value"

'In this case object is of type T, and value is of type S

'Even though most of the logic for this is already in the base class,

'defining a second generic parameter means the user doesn't have to

'pass in a System.Type - it can just be inferred.

Public Class Condition(Of T, S) : Inherits Condition(Of T)

Friend Sub New(ByVal propName As String, ByVal condType As Compare, ByVal value As S)

MyBase.New(propName, condType, value, GetType(S))

End Sub

End Class

#End Region

#Region "Extension Methods"

'Filters an IQueryable(Of T) according to the specified condition

<Extension()> _

Public Function Where(Of T)(ByVal source As IQueryable(Of T), _

ByVal condition As Condition(Of T)) As IQueryable(Of T)

Dim callExpr = Expression.Call(GetType(Queryable), "Where", _

New Type() {source.ElementType}, source.Expression, _

Expression.Quote(condition.LambdaExpr))

Return CType(source.Provider.CreateQuery(callExpr), IQueryable(Of T))

End Function

'Filters an IEnumerable(Of T) according to the specified condition

<Extension()> _

Public Function Where(Of T)(ByVal source As IEnumerable(Of T), _

ByVal condition As Condition(Of T)) As IEnumerable(Of T)

Return source.Where(condition.del)

End Function

'Extension method that can be called off any type that implements IEnumerable(Of T),

'which constructs a Condition with T as the element type and S as the value's type

<Extension(), EditorBrowsable(EditorBrowsableState.Always)> _

Public Function CreateCondition(Of T, S)(ByVal dataSource As IEnumerable(Of T), _

ByVal propName As String, _

ByVal condType As condition.Compare, _

ByVal value As S) As Condition(Of T, S)

Return Condition.Create(dataSource, propName, condType, value)

End Function

#End Region

End Module

The complete sample (including the code for the ConditionBuilder control) is available in the attached .zip file below. While we’ve got the basics going pretty well, there’s definitely a lot of things you can extend the sample to do, such as:

1. Add support for Grouping conditions into the ConditionBuilder – right now you’d have to drop down to the API to construct something like (condition1 Or condition2) And condition3.

2. Add a .Select extension method to dynamically control which fields you bring back from the database. This will improve performance in situations where you don’t want to return all fields, but you’ll lose strong-typing of your results since the method will have to return IQueryable instead of IQueryable(Of T).

3. Add extension methods for OrderBy, Take, Skip, Distinct etc… Each of these methods can still return strongly-typed results since they don’t change the element type; if you pass in T they return T.

4. The Condition API supports nested property access (i.e. Where customer.Orders.Salesman.Name = “Bob”), but it’d take a bit more work to surface this in the ConditionBuilder control.

That’s everything, now all you have to do is download Beta2 and try it out!

Jonathan

by VBTeam

Filed under: LINQ/VB9, Jonathan Aneja, Orcas, VB2008, IQueryable

Attachment(s):DynamicCondition.zip

Comment Notification

If you would like to receive an email when updates are made to this post, please register here

Subscribe to this post's comments using RSS

Comments

# tomasp said on August 29, 2007 8:30 PM:

Hi,

there is a way for building queries dynamically without the need to build any expression trees manually and withot loosing the type safety (even though your sample is 'safer' than the DynamicQueries sample, there are still a few possible problems). I wrote about it here: http://tomasp.net/blog/dynamic-linq-queries.aspx. It is written in C#, but I guess that C# and VB have similar expressive power, so I would expect that same thing could be done in VB as well (but correct me if I'm wrong!).

# Beth Massi - Sharing the goodness that is VB said on September 6, 2007 3:47 PM:

So I decided to post a summary of all the content the VB team members, including myself, have created

# The Visual Basic Team said on September 11, 2007 10:10 PM:

Hi there! My name is Tim, and I'm the dev lead for the VB compiler team. Recently Amanda convinced me

# From the software development trenches said on September 14, 2007 2:20 AM:

Time for another weekly roundup of news that focuses on .NET, agile and general development related content

# The Visual Basic Team said on August 21, 2008 1:20 AM:

Last week I got the chance to visit the Toronto .NET User Group and give a talk on all the great new

# G yc {Son of VB.NET} said on August 28, 2008 3:10 PM:

原文链接：http://blogs.msdn.com/vbteam

# Bruno Santos said on March 12, 2009 12:13 PM:

Hi. Thanks Great. But It doesn't seem to work for the Like operator between Strings.

Can you fix that?

Regards

# Jonathan Aneja said on March 12, 2009 4:59 PM:

Hey Bruno,

I can't seem to repro a problem with the Like operator - either in the ConditionBuilder control or in the API itself.

Dim c = db.Orders.CreateCondition("CustomerID", Compare.Like, "A*")

Dim filteredQuery = db.Orders.Where(c)

...

Likewise in the control if I choose the Like operator from the dropdown and pass a valid string I get normal VB-Like-Operator semantics.

Could you post more info on the case that's not working for you?

Thanks,

Jonathan Aneja

Program Manager, VB Team

# Bruno Santos said on March 13, 2009 6:19 AM:

Hi Jonathan. First, Thank you for such a fast reply.

What I have is a web form where the user can fill it up and select in a cbox if he wants the condition for that field to be AND or OR.

For example the FirstName field. If I select OR, the query whould be:

Dim condition1 = db.Members.CreateCondition("FName", Compare.Like, fName)

Dim fdx = condition1 And condition2 _

'And condition3 And condition4 And _

'condition5 And condition6 And condition7 And condition8 And _

'condition9 And condition10

Dim filteredQuery = db.Members.Where(fdx)

Dim query = From row In filteredQuery _

Order By row.MemberGUID, row.BaseMemberID _

Select row

When the Query is executed it returns:

"LINQ to Entities does not recognize the method 'Boolean LikeString(System.String, System.String, Microsoft.VisualBasic.CompareMethod)' method, and this method cannot be translated into a store expression."

I've read that Linq doesn't have the Like operator yet. So What you think that I can do? Am I doing this the right way?

I'm now thinking in building the where clause as a String... and executed via Linq to Entities...

But I prefered to use you way.

# Jonathan Aneja said on March 13, 2009 5:04 PM:

ah, ok, there's two seperate issues here. LINQ and the DynamicCondition API both do support the Like operator, but in this case the particular LINQ *provider* doesn't support translating Like.

LINQ to SQL and LINQ to Objects support Like, but LINQ to Entities doesn't. I'll follow up with the Entities team and see if it's something they can add support for. You can try using Substring or some of the BCL string functions as I'm pretty sure they'll support those.

HTH,

Jonathan

# Bruno Santos said on March 16, 2009 6:17 AM:

Hi Jonathan. Just Another small question.

I have a Sql user function that calcs a persons age and I wanted to use that in a query.

I've heard that Model defined functions are not in the shipping Entity Framework (only in .NET 4.0)

http://stackoverflow.com/questions/609959/entity-framework-model-defined-functions

But I still whated to something like this:

SELECT * FROM Members as m

Where m.FName like @FName or

m.LName like @LName or

ufn_GetAge ( m.DOB, GETDATE() ) >= @AgeMin or

ufn_GetAge ( m.DOB, GETDATE() ) <= @AgeMax

I know that I cant do that with Link to Entities. But I was trying to do it with Entity to SQL like this:

Dim queryString As String = "SELECT VALUE m FROM Members as m

Where m.FName = @FName and

m.LName = @LName and

ufn_GetAge] ( m.DOB, GETDATE() ) >= @AgeMin and

[dbo].[ufn_GetAge] ( m.DOB, GETDATE() ) <= @AgeMax

Dim membersQuery As New ObjectQuery(Of Members)(queryString, db, MergeOption.NoTracking)

membersQuery.Parameters.Add(fNameParameter)

membersQuery.Parameters.Add(lNameParameter)

But That Doesnt seem to work.

Can you point me out on the right direction?

Thank you for your help.

# Koen said on March 17, 2009 2:17 PM:

Hi I'm trying to use the library, but i fail when I combine 2 Like clauses together with and And. When I combine them with an OR it works, what is a bit weird for me.

Can anybody help me with this?

Thanks for any help!

Here's my code:

If Not city.EndsWith("*") Then

city &= "*"

End If

If Not name.EndsWith("*") Then

name &= "*"

End If

Using db = New DataLayer.SIS2009Db.SIS2009DataContext()

Dim filter = Common.Condition.Create(db.Dealerships, "ds_City", Common.Condition.Compare.Like, city)

Dim filter2 = Common.Condition.Create(db.Dealerships, "ds_Name", Common.Condition.Compare.Like, name)

Dim endfilter = (filter And filter2)

Return db.Dealerships.Where(endfilter).ToList()

End Using

# Jonathan Aneja said on March 17, 2009 2:36 PM:

Your code looks right, are you sure the data in the database matches both conditions?

I just tried this on Northwind and it works fine:

Dim c = db.Orders.CreateCondition("CustomerID", Compare.Like, "A*")

Dim c2 = db.Orders.CreateCondition("ShipAddress", Compare.Like, "B*")

Dim filteredQuery = db.Orders.Where(c And c2)

Try using DataContext.Log or SQL Profiler to see what SQL string is being sent to the database.

HTH,

Jonathan

# Alex Mineev said on March 23, 2009 10:09 PM:

WRT ufn_GetAge: eSQL

GetDate() should be available under SqlServer namespace

you can import GetAge into ssdl like this:

</Function>

Once in ssdl, the function should be available in eSQL queries under the namespace defined in SSDL

# masscomp said on June 24, 2009 9:48 AM:

I tried this following query:

Dim condition1 = ds.Code.CreateCondition("OrgId", Compare.Equal, 1)

Dim condition2 = ds.Code.CreateCondition("CodeType", Compare.Equal, "SECCOLOR")

Dim c = condition1 And condition2

Dim filteredQuery = ds.Code.Where(c)

Dim query = From row In filteredQuery _

Order By row _

Select row

I get the following error in the CombineExpression function:

The binary operator Equal is not defined for the types 'System.Nullable`1[System.Int32]' and 'System.Int32'.

# Jonathan Aneja said on June 24, 2009 12:32 PM:

The problem is the expression tree needs both sides of the expression to be the same type. Try this instead:

Dim value? = 1

Dim condition1 = ds.Code.CreateCondition("OrgId", Compare.Equal, value)

Now you're comparing a Nullable integer with another Nullable Integer and everything should work fine.

HTH,

Jonathan

# masscomp said on June 24, 2009 4:31 PM:

Thanks, that worked for the integer and also works for boolean when using this dynamic query. Those nullable variables didn't work when I tried to create the query in code. ex.

dim intOrgId? as integer = 1

dim blnFalse? as boolean = false

dim query= from row in ds.Code _

where row.OrgId = intOrgId _

and row.CodeType = "SECCOLOR" _

and row.Inactive = blnFalse

Dave

# masscomp said on June 25, 2009 12:12 PM:

As stated above, 'like' doesn't work with linq - entities but after dynamically preparing the other conditions you can add conditions such as startswith, contains, endswith as follows:

Dim condition1 = ds.Cust.CreateCondition("OrgId", Compare.Equal, intOrgId)

Dim condition2 = ds.Cust.CreateCondition("CustTypeId", Compare.Equal, intCustType)

Dim c = condition1 And condition2

Dim filteredQuery = ds.Cust.Where(c)

Dim query = From row In filteredQuery _

Where row.SearchName.StartsWith(strSearchText) _

Order By row.SearchName

Dim CustQuery = CType(query, DataServiceQuery(Of Cust))

CustQuery.BeginExecute(AddressOf GetCustListByOrgCompleted, CustQuery)

Dave

Name (required)
Your URL (optional)
Comments (required)
Remember Me?

The Visual Basic Team

Implementing Dynamic Searching Using LINQ

Comment Notification

Comments

Leave a Comment

This Blog

Tags

Archives

Syndication