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.