Databases
StackOverflow2013
Postsdbo
POAre Linq-To-Sql Dynamic-Where-Clauses Even Possible in Framework 3.5?
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POAre Linq-To-Sql Dynamic-Where-Clauses Even Possible in Framework 3.5?
    primarykey
    data
    text
    <p><strong>UPDATE: It Is Now Working</strong><br/> I was able to finally get it completed. A working-example is detailed in an answer below (which I will be able to mark-off in 2 days).</p> <hr> <h2><strong>Everything Below Here Was Part of the Original Question</strong></h2> <p>For the last 3 days, I have been trying to build a dynamic-where-clause on a <a href="http://msdn.microsoft.com/en-us/library/bb399400.aspx" rel="nofollow noreferrer"><strong>DBML DataContext</strong></a> using code samples from <a href="https://stackoverflow.com/questions/8140851/creating-a-dynamic-where-clause-as-a-linq-expression/8140907#8140907"><strong>questions posted here</strong></a> and from <a href="http://pastebin.com/feb8cc1e" rel="nofollow noreferrer"><strong>other sources</strong></a> as well...<strong><em>none have worked</em></strong>!</p> <p>For the reasons below, I am beginning to wonder if this is even POSSIBLE using under Framework 3.5:</p> <ol> <li><a href="http://www.albahari.com/nutshell/predicatebuilder.aspx" rel="nofollow noreferrer"><strong>Predicate Builder</strong></a> notes Framework 4.0 on their site.</li> <li>Some <a href="https://stackoverflow.com/questions/3444457/create-fully-dynamic-where-clause-with-expression-tree-and-execute-on-iqueryable"><strong>answers here</strong></a> talk about an equivolent <code>Invoke</code> versions in 4.0 (so I have <em>some</em> hope here).</li> <li>...I could go on but you get the idea.</li> </ol> <p>I am really at a loss and seem to be "grabbing at strings"...and I need some sound advice on how to approach this.</p> <p><strong>Original Version Had <a href="https://stackoverflow.com/questions/8140851/creating-a-dynamic-where-clause-as-a-linq-expression/8140907#8140907">SOME Success</a> But Only When:</strong><br/> The ONLY time I had a 'inkling' of success the data came-up (all 6178 rows of it) but no <code>WHERE CLAUSE</code> was applied. This was evidenced by the <strong>lack</strong> of any <code>WHERE CLAUSE</code> applied into the <code>SQL</code> found in the <code>dataContext.GetCommand(query).CommandText</code>.</p> <p><strong>Other Version #1 Fails:</strong><br/> And generates this error: "Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL."</p> <pre><code>// VERSION 1: public static class PredicateBuilder { public static Expression&lt;Func&lt;T, bool&gt;&gt; True&lt;T&gt;() { return f =&gt; true; } public static Expression&lt;Func&lt;T, bool&gt;&gt; False&lt;T&gt;() { return f =&gt; false; } public static Expression&lt;Func&lt;T, bool&gt;&gt; Or&lt;T&gt;(this Expression&lt;Func&lt;T, bool&gt;&gt; expr1, Expression&lt;Func&lt;T, bool&gt;&gt; expr2) { var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast&lt;Expression&gt;()); return Expression.Lambda&lt;Func&lt;T, bool&gt;&gt;(Expression.OrElse(expr1.Body, invokedExpr), expr1.Parameters); } public static Expression&lt;Func&lt;T, bool&gt;&gt; And&lt;T&gt;(this Expression&lt;Func&lt;T, bool&gt;&gt; expr1, Expression&lt;Func&lt;T, bool&gt;&gt; expr2) { var invokedExpr = Expression.Invoke(expr2, expr1.Parameters.Cast&lt;Expression&gt;()); return Expression.Lambda&lt;Func&lt;T, bool&gt;&gt;(Expression.AndAlso(expr1.Body, invokedExpr), expr1.Parameters); } public static Expression&lt;Func&lt;T, bool&gt;&gt; StringLike&lt;T&gt;(Expression&lt;Func&lt;T, string&gt;&gt; selector, string pattern) { var predicate = PredicateBuilder.True&lt;T&gt;(); var parts = pattern.Split('%'); if (parts.Length == 1) // not '%' sign { predicate = predicate.And(s =&gt; selector.Compile()(s) == pattern); } else { for (int i = 0; i &lt; parts.Length; i++) { string p = parts[i]; if (p.Length &gt; 0) { if (i == 0) { predicate = predicate.And(s =&gt; selector.Compile()(s).StartsWith(p)); } else if (i == parts.Length - 1) { predicate = predicate.And(s =&gt; selector.Compile()(s).EndsWith(p)); } else { predicate = predicate.And(s =&gt; selector.Compile()(s).Contains(p)); } } } } return predicate; } } // VERSION 1: public List&lt;QuickFindResult&gt; QueryDocuments(string searchText, string customerSiteId, List&lt;int&gt; filterIds) { var where = PredicateBuilder.True&lt;vw_QuickFindResult&gt;(); var searches = new List&lt;String&gt;(searchText.Split(' ')); searches.ForEach(productName =&gt; { string like = productName.Replace('"', '%') .Replace('*', '%'); where = PredicateBuilder.StringLike&lt;vw_QuickFindResult&gt;(x =&gt; x.DocumentName, like); }); var results = DocumentCollectionService.ListQuickFind(where, null); // Do other stuff here... return results; } // VERSION 1: public static List&lt;vw_QuickFindResult&gt; ListQuickFind(Expression&lt;Func&lt;vw_QuickFindResult, bool&gt;&gt; where, Expression&lt;Func&lt;vw_QuickFindResult, bool&gt;&gt; orderBy) { var connectionString = GetConnectionString(ES_DOCUMENTS_CONNECTION_NAME); List&lt;vw_QuickFindResult&gt; results = null; using (HostingEnvironment.Impersonate()) { using (var dataContext = new ES_DocumentsDataContext(connectionString)) { IQueryable&lt;vw_QuickFindResult&gt; query = dataContext.vw_QuickFindResults; query = query.Where(where); results = query.ToList(); } } return results; } </code></pre> <p><strong>Other Version #2 Fails:</strong><br/> And generates this error: "Method 'Boolean Like(System.String, System.String)' cannot be used on the client; it is only for translation to SQL."</p> <pre><code>// VERSION 2: public List&lt;QuickFindResult&gt; QueryDocuments(string searchText, string customerSiteId, List&lt;int&gt; filterIds) { Func&lt;vw_QuickFindResult, bool&gt; where = null; Func&lt;string, Func&lt;vw_QuickFindResult, bool&gt;&gt; buildKeywordPredicate = like =&gt; x =&gt; SqlMethods.Like(x.DocumentName, like); Func&lt;Func&lt;vw_QuickFindResult, bool&gt;, Func&lt;vw_QuickFindResult, bool&gt;, Func&lt;vw_QuickFindResult, bool&gt;&gt; buildOrPredicate = (pred1, pred2) =&gt; x =&gt; pred1(x) || pred2(x); // Build LIKE Clause for the WHERE var searches = new List&lt;String&gt;(searchText.Split(' ')); searches.ForEach(productName =&gt; { string like = productName.Replace('"', '%') .Replace('*', '%'); where = (where == null) ? buildKeywordPredicate(like) : buildOrPredicate(where, buildKeywordPredicate(like)); }); var results = DocumentCollectionService.ListQuickFind(where, null); // Do other stuff here... return results; } // VERSION 2: public static List&lt;vw_QuickFindResult&gt; ListQuickFind(Expression&lt;Func&lt;vw_QuickFindResult, bool&gt;&gt; where, Expression&lt;Func&lt;vw_QuickFindResult, bool&gt;&gt; orderBy) { var connectionString = GetConnectionString(ES_DOCUMENTS_CONNECTION_NAME); List&lt;vw_QuickFindResult&gt; results = null; using (HostingEnvironment.Impersonate()) { using (var dataContext = new ES_DocumentsDataContext(connectionString)) { var query = dataContext.vw_QuickFindResults.AsEnumerable(); query = query.Where(where); results = query.ToList(); } } return results; } </code></pre>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USPrisoner ZERO
    plurals
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POAre Linq-To-Sql Dynamic-Where-Clauses Even Possible in Framework 3.5?
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POAre Linq-To-Sql Dynamic-Where-Clauses Even Possible in Framework 3.5?
      1. USEnigma State
      1. VTFavorite
    3. VO
      singulars
      1. POAre Linq-To-Sql Dynamic-Where-Clauses Even Possible in Framework 3.5?
      1. This table or related slice is empty.
      1. VTUpMod
    1. COI have updated the question with a working sample
      singulars
      1. POAre Linq-To-Sql Dynamic-Where-Clauses Even Possible in Framework 3.5?
      1. USPrisoner ZERO
    2. COIt's good you found the answer yourself. Probably the best approach would have been to have added an answer yourself with all the corrections included in it so the question is clear.
      singulars
      1. POAre Linq-To-Sql Dynamic-Where-Clauses Even Possible in Framework 3.5?
      1. USJTew
    3. COGreat question. Glad you got it working. I started working with predicate builder a while back and remember the growing pains. When you said... "I don't know why" was that referencing why you needed to use .False<> instead of .True<>? If so... Those methods start off your predicate with a base value of either true or false. If you start with True and then build your predicate with OR it will always evaluate back to true because TRUE OR anything=True. also if you start with False and try to AND to your predicate it will always eval to false. Ignore me if I misunderstood your confusion :)
      singulars
      1. POAre Linq-To-Sql Dynamic-Where-Clauses Even Possible in Framework 3.5?
      1. USBZink
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

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