29328

Recursively (?) compose LINQ predicates into a single predicate

(EDIT: I have asked the wrong question. The real problem I'm having is over at Compose LINQ-to-SQL predicates into a single predicate - but this one got some good answers so I've left it up!)

Given the following search text:

"keyword1 keyword2 keyword3 ... keywordN"

I want to end up with the following SQL:

SELECT [columns] FROM Customer WHERE (Customer.Forenames LIKE '%keyword1%' OR Customer.Surname LIKE '%keyword1%') AND (Customer.Forenames LIKE '%keyword2%' OR Customer.Surname LIKE '%keyword2%') AND (Customer.Forenames LIKE '%keyword3%' OR Customer.Surname LIKE '%keyword3%') AND ... AND (Customer.Forenames LIKE '%keywordN%' OR Customer.Surname LIKE '%keywordN%')

Effectively, we're splitting the search text on spaces, trimming each token, constructing a multi-part OR clause based on each token, and then AND'ing the clauses together.

I'm doing this in Linq-to-SQL, and I have no idea how to dynamically compose a predicate based on an arbitrarily-long list of subpredicates. For a known number of clauses, it's easy to compose the predicates manually:

dataContext.Customers.Where( (Customer.Forenames.Contains("keyword1") || Customer.Surname.Contains("keyword1") && (Customer.Forenames.Contains("keyword2") || Customer.Surname.Contains("keyword2") && (Customer.Forenames.Contains("keyword3") || Customer.Surname.Contains("keyword3") );

but I want to handle an arbitrary list of search terms. I got as far as

Func<Customer, bool> predicate = /* predicate */; foreach(var token in tokens) { predicate = (customer => predicate(customer) && (customer.Forenames.Contains(token) || customer.Surname.Contains(token)); }

That produces a StackOverflowException - presumably because the predicate() on the RHS of the assignment isn't actually evaluated until runtime, at which point it ends up calling itself... or something.

In short, I need a technique that, given two predicates, will return a single predicate composing the two source predicates with a supplied operator, but restricted to the operators explicitly supported by Linq-to-SQL. Any ideas?

Answer1:

I would suggest another technique

you can do:

var query = dataContext.Customers;

and then, inside a cycle do

foreach(string keyword in keywordlist) { query = query.Where(Customer.Forenames.Contains(keyword) || Customer.Surname.Contains(keyword)); }

Answer2:

If you want a more succinct and declarative way of writing this, you could also use Aggregate extension method instead of foreach loop and mutable variable:

var query = keywordlist.Aggregate(dataContext.Customers, (q, keyword) => q.Where(Customer.Forenames.Contains(keyword) || Customer.Surname.Contains(keyword));

This takes dataContext.Customers as the initial state and then updates this state (query) for every keyword in the list using the given aggregation function (which just calls Where as Gnomo suggests.

Recommend

  • Custom filter with white space as thousands separator is not working
  • Why TText Trimming does not work when WordWrap is enabled in Firemonkey?
  • How can I convert date to a readable form in Angular and also concatenate two dates?
  • Change AngularJS ngTrim Behavior
  • Prolog - Little exercise on facts
  • Convert Oracle legacy outer join to Ansi SQL
  • What is the behavior when there are more initializers than array size?
  • ASP.NET MVC + jqGrid without AJAX
  • Java On AND'ing a short with an short, it is upgraded to int and returns weird values
  • Is it possible to definitively identify whether a DML command was issued from a stored procedure?
  • How to asynchronously apply function via Spark to subsets of dataframe?
  • Asking for undo/redo events in html/javascript
  • Using Regex to split XML string before and after match
  • Geo Fix not working in Android SDK 2.2
  • Implement Gauss-Jordan elimination in Haskell
  • Streaming data from a NVarchar(Max) column using c#
  • Updating and removing unique join relationships in CakePHP
  • command line of process by name
  • SQL Server re-calculate or not?
  • Does the MySQL IN clause execute the subquery multiple times?
  • Many to Many in Linq using Dapper
  • Multiple Left Join LINQ-to-entities
  • Exception creating JSON with LINQ
  • Validate jQuery plugin, field not required
  • Which open source license has no forking [closed]
  • Linq Merge lists
  • Reading a file into a multidimensional array
  • preg_replace Double Spaces to tab (\\t) at the beginning of a line
  • Use of this Javascript
  • C++ Partial template specialization - design simplification
  • Read text file and split every line in MSBuild
  • What is Eclipse's Declaration View used for?
  • C# - Serializing and deserializing static member
  • Java applet as stand-alone Windows application?
  • Jquery - Jquery Wysiwyg return html as a string
  • How to get next/previous record number?
  • SVN: Merging two branches together
  • Python: how to group similar lists together in a list of lists?
  • Error creating VM instance in Google Compute Engine
  • Append folder name and increment by 1 using batch script