LINQ to Objects: Query with multiple LIKE conditions (OR or AND) possible?

Question

We have a form that allows a user to filter a list with by typing in a textbox. We need to filter the list based on two fields. The FundName and the CompanyName. When the form loads I put the initial LINQ to SQL results into a list so that for every subsequent action (filter) the database is not hit. This speeds things up significantly (from roughly 400 milliseconds to under 6 milliseconds per filter action, like typing a character) and reduces DB chatter.

The problem is that when we decided to make the filter criteria search over two fields the LINQ to Object query does not work. I can have a query that has multiple criteria just not multiple LIKE (or .Contains or .Endswith etc. methods that evaluate to SQL LIKE statements). The query does work against the LINQtoSQL just not against the object. Here is some sample code:

Dim db As New BenchmarkLINQtoSQLDataContext()

Dim fundList = From FundShort In db.FundShorts _
Select FundShort _
Order By FundShort.IRBB

Dim linqFundShortList As New List(Of FundShort)
linqFundShortList = fundList.ToList()

Dim filterText = "aka"


'This works
Dim successQuery = From fs In fundList _
        Where fs.FundName.ToLower.Contains(filterText) _
        Or fs.CompanyName.ToLower.Contains(filterText) _
        Select fs

FundBindingSource.DataSource = successQuery

'This also works 
Dim successQuery2 = From fs In linqFundShortList _
         Where fs.FundName.ToLower.Contains(filterText) _
         Select fs

FundBindingSource.DataSource = successQuery2

'This does not
Dim failQuery = From fs In linqFundShortList _
         Where (fs.FundName.ToLower.Contains(filterText) _
         Or fs.CompanyName.ToLower.Contains(filterText)) _
         Select fs

FundBindingSource.DataSource = failQuery

The exception is "Object reference not set to an instance of an object." - the stack trace is:

   at Benchmark.BPRMS.FundsMain._Closure$__1._Lambda$__3(FundShort fs) in
     C:\Projects\BPRMS\Trunk\BPRMS\Forms\FundsMain.vb:line 72
   at System.Linq.Enumerable.WhereSelectListIterator`2.MoveNext()
   at System.Linq.SystemCore_EnumerableDebugView`1.get_Items()

Any and all help and suggestions would be greatly appreciated.

Solution

If you're getting a NullReferenceException, it sounds like either FundName or CompanyName is null (Nothing). Try:

Dim failQuery = From fs In linqFundShortList _
     Where (((Not fs.FundName Is Nothing) AndAlso fs.FundName.ToLower.Contains(Me.filterText))_
     OrElse ((Not fs.CompanyName Is Nothing) AndAlso fs.CompanyName.ToLower.Contains(Me.filterText))) _
     Select fs

(The syntax may be a bit off, but hopefully you get the gist.)

Comments

[user3555501]

That was it. Weird though that I could build the query with a single like against either field (CompanyName or FundName) without having to check for nulls. THANKS!

[Jon Skeet]

Did each of them realy work in LINQ to Objects without complaining? That would indeed be odd. I'd expect it to succeed in LINQ to SQL because SQL doesn't really have the concept of a NullReferenceException.