How do I append a 'where' clause using VB.NET and LINQ?

Question

I am pretty new to VB.NET and am having a bit of trouble here with something I thought should be simple.

Keeping it simple, let's say I have a Document table with "Name" that I want to search on (in reality there are several other tables, joins, etc. ..). I need to be able to build the query using a where clause based on string values passed in.

Example - the user may pass in "ABC", "ABC DEF", "ABC DEF GHI".

The final query would be (the syntax is not correct, I know):

Select * from Documents Where Name Like %ABC% AND Name Like %DEF% AND Name like %GHI% 

So, I thought I could do something like this.

Dim query = From document In _context.Documents  << loop based on number of strings passed in >> query = query.Where( ... what goes here?? ) 

For some reason, being brain-dead or something, I can't figure out how to make this work in VB.NET, or if I'm doing it correctly.

Answer 1

I believe this is how you would do it in VB (I'm a C# developer):

query = query.Where(Function(s) s = "ABC") 

See LINQ - Sample Queries for some examples.

Answer 2

I think the tricky part here is the unknown number of query parameters. You can use the underlying LINQ IQueryable(Of T) here to help.

I think the following would work (it's not compiled, just notepad code here):

Public Function GetDocuments(criteria as String)     Dim splitCriteria = SplitTheCriteria(criteria)      dim query = from document in _context.Documents      For Each item in splitCriteria         Dim localItem = item         query = AddCriteriaToQuery(query, localItem)     Next      dim matchingDocuments = query.ToList() End Function  Private Function AddCriteriaToQuery(query as IQueryable(Of Document), criteria as string) as IQueryable(Of Document)      return query.Where(Function(doc) doc.Name = criteria) End Function 

Since LINQ will delay-execute the query you can append where clauses onto your query in the loop and then call .ToList() at the end to execute the query.