Databases
StackOverflow2013
Postsdbo
PODynamically-built LINQ query gives inconsistent results depending on how it's built
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PODynamically-built LINQ query gives inconsistent results depending on how it's built
    text
    copied!<p>I'm trying to build a generic query builder for accepting user-entered search terms. To get the where clauses I need, I must parse terms and independently determine which field needs to be included in the clause.</p> <p>Note that this example is <em>greatly</em> simplified to illustrate the point. I know that in this particular case the entire result could be expressed as a single Where() statement. This code <em>will</em> work in my problem space (where a single Where() statement would not), so the answer has to address what's actually happening under the hood here, not how to make it simpler.</p> <p>I start with a list of terms (here represented as a string[], but will ultimately be an IList of a more complex type that helps guide query builder):</p> <pre><code> string[] terms = new string[] { "hope", "bob", }; </code></pre> <p>This first example (below) gives the right result set (any record that has an employee with 'bob' in either of the three searched fields and also has 'hope' in either of the three fields matches). This does demonstrate that the proper query is built from the code when chaining Where() clauses:</p> <pre><code> var query0 = Sites.Where(s =&gt; s.SiteId &lt; 200); query0 = query0.Where(s =&gt; s.Employee.FirstName.Contains(terms[0]) || s.Employee.LastName.Contains(terms[0]) || s.Employee.Username.Contains(terms[0])); query0 = query0.Where(s =&gt; s.Employee.FirstName.Contains(terms[1]) || s.Employee.LastName.Contains(terms[1]) || s.Employee.Username.Contains(terms[1])); query0.Dump(); </code></pre> <p>(Note that I can't use this direct approach because I don't know how many terms there will be, and some of them will be on Employee while others will be on other fields of 'Sites', so at compile time I must be able to iterate and treat each term uniquely.)</p> <p>This next example (below) is what I <em>want</em> to do, but it doesn't honor the first term, and matches only on the last; any record that has 'bob' in any of the fields is included regardless of whether or not 'hope' appears in any field:</p> <pre><code> var query1 = Sites.Where(s =&gt; s.SiteId &lt; 200); foreach (string term in terms) { query1 = query1.Where(s =&gt; s.Employee.FirstName.Contains(term) || s.Employee.LastName.Contains(term) || s.Employee.Username.Contains(term)); } query1.Dump(); </code></pre> <p>This final example (below) gives an 'index out of bounds' error when it reaches the Dump() instruction:</p> <pre><code> var query2 = Sites.Where(s =&gt; s.SiteId &lt; 200); for (int i = 0; i &lt; terms.Length; ++i) { query2 = query2.Where(s =&gt; s.Employee.FirstName.Contains(terms[i]) || s.Employee.LastName.Contains(terms[i]) || s.Employee.Username.Contains(terms[i])); } query2.Dump(); </code></pre> <p>I think query2 is most telling example. It's almost as if LINQ is trying to bind variables to SQL parameters after the entire query has been built, and it's trying to use i==2 (which would be the value of i at the instant the loop exits) for <em>all</em> bindings. This would also be consistent with the result I am seeing with query1.</p> <p>Does anyone know how binding works and how I can construct my query?</p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload