Implementing Dynamic Searching Using LINQ

re: Implementing Dynamic Searching Using LINQ

tomasp — Thu, 30 Aug 2007 03:30:18 GMT

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!).

Visual Basic Content Rollup for July and August

Beth Massi - Sharing the goodness that is VB — Thu, 06 Sep 2007 22:47:36 GMT

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

Lambda Expressions and Expression Trees

The Visual Basic Team — Wed, 12 Sep 2007 05:10:32 GMT

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

Izindaba #9

From the software development trenches — Fri, 14 Sep 2007 09:20:13 GMT

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

VB 2008 Language Deep Dive - Presentation Materials (Jonathan Aneja)

The Visual Basic Team — Thu, 21 Aug 2008 08:20:51 GMT

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

[翻译]使用LINQ实现动态搜索(Implementing Dynamic Searching Using LINQ)

G yc {Son of VB.NET} — Thu, 28 Aug 2008 22:10:56 GMT

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

re: Implementing Dynamic Searching Using LINQ

Bruno Santos — Thu, 12 Mar 2009 19:13:10 GMT

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

Can you fix that?

Regards

re: Implementing Dynamic Searching Using LINQ

Jonathan Aneja — Thu, 12 Mar 2009 23:59:05 GMT

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

re: Implementing Dynamic Searching Using LINQ

Bruno Santos — Fri, 13 Mar 2009 13:19:51 GMT

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.

re: Implementing Dynamic Searching Using LINQ

Jonathan Aneja — Sat, 14 Mar 2009 00:04:12 GMT

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

re: Implementing Dynamic Searching Using LINQ

Bruno Santos — Mon, 16 Mar 2009 13:17:12 GMT

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.

re: Implementing Dynamic Searching Using LINQ

Koen — Tue, 17 Mar 2009 21:17:47 GMT

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

re: Implementing Dynamic Searching Using LINQ

Jonathan Aneja — Tue, 17 Mar 2009 21:36:36 GMT

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

re: Implementing Dynamic Searching Using LINQ

Alex Mineev — Tue, 24 Mar 2009 05:09:01 GMT

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

re: Implementing Dynamic Searching Using LINQ

masscomp — Wed, 24 Jun 2009 16:48:17 GMT

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'.

re: Implementing Dynamic Searching Using LINQ

Jonathan Aneja — Wed, 24 Jun 2009 19:32:12 GMT

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

re: Implementing Dynamic Searching Using LINQ

masscomp — Wed, 24 Jun 2009 23:31:23 GMT

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

re: Implementing Dynamic Searching Using LINQ

masscomp — Thu, 25 Jun 2009 19:12:58 GMT

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

re: Implementing Dynamic Searching Using LINQ

usul — Fri, 10 Jul 2009 22:19:02 GMT

I would like to be able to modify this.

I have a dataset with two datatables

(One Loaded From A CSV file)

and

(the other loaded from a query)

what I would like to be able to do is

set the criteria (i.e dtCSV.Field1=dtQuery.Field7 AND (dtCSV.Field5 != dtQuery.Field4 or dtCSV.Field5 != dtQuery.Field9)

Returning dtQuery.Field1, dtCSV.Field5

Basically I am updating a description field in a database based on CSVs from various vendors.

I have everything worked out except the dynamic Linq (using the datasets and comparing the fields directly is too slow) I am hoping that I can get a smaller set containing just the records that need updates if any.

I am new to Linq and not sure how to modify this to include the joins.

thanks

usul

re: Implementing Dynamic Searching Using LINQ

VBTeam — Sat, 11 Jul 2009 04:14:05 GMT

Hey Usul,

It's definitely possible to do what you're looking for but it involves adding some functionality to the Condition API, so instead of trying to cram it in here I've written a separate blog entry that walks through how to do it:

http://blogs.msdn.com/vbteam/archive/2009/07/10/dynamic-searching-using-linq-dataset-joins.aspx

Hope that helps!

Jonathan

re: Implementing Dynamic Searching Using LINQ

AlexB — Wed, 11 Nov 2009 15:21:09 GMT

The code is missing 3 very important lines in case of you want to select rows "where" the field is "*" OR DBNULL.

In the "Protected Shared Function CombineExpression" function i change the code to look like that:

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")

If right.Type Is System.DBNull.Value.GetType Then

Return Expression.Equal(left, Expression.Constant(Nothing))

End If

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