Databases
StackOverflow2013
Postsdbo
POC#, Linq2SQL: Creating a predicate to find elements within a number of ranges
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POC#, Linq2SQL: Creating a predicate to find elements within a number of ranges
    primarykey
    data
    text
    <p>Lets say I have something called Stuff in my database, with a property called Id. From the user I get a sequence of selected Range objects (or rather I create them from their input) with the Ids they want. A stripped down version of that struct looks like this:</p> <pre><code>public struct Range&lt;T&gt; : IEquatable&lt;Range&lt;T&gt;&gt;, IEqualityComparer&lt;Range&lt;T&gt;&gt; { public T A; public T B; public Range(T a, T b) { A = a; B = b; } ... } </code></pre> <p>So one could for example have gotten:</p> <pre><code>var selectedRange = new List&lt;Range&lt;int&gt;&gt; { new Range(1, 4), new Range(7,11), }; </code></pre> <p>I then want to use that to create a predicate to select only things which have a value between those. For example, using the <a href="http://www.albahari.com/nutshell/predicatebuilder.aspx" rel="noreferrer">PredicateBuilder</a>, I can for example do that this way:</p> <pre><code>var predicate = PredicateBuilder.False&lt;Stuff&gt;(); foreach (Range&lt;int&gt; r in selectedRange) { int a = r.A; int b = r.B; predicate = predicate.Or(ø =&gt; ø.Id &gt;= a &amp;&amp; ø.Id &lt;= b); } </code></pre> <p>and then:</p> <pre><code>var stuff = datacontext.Stuffs.Where(predicate).ToList(); </code></pre> <p>Which works! What I would like to do now, is to create a generic extension method to create those predicates for me. Kind of like this:</p> <pre><code>public static Expression&lt;Func&lt;T,bool&gt;&gt; ToPredicate&lt;T&gt;(this IEnumerable&lt;Range&lt;int&gt;&gt; range, Func&lt;T, int&gt; selector) { Expression&lt;Func&lt;T, bool&gt;&gt; p = PredicateBuilder.False&lt;T&gt;(); foreach (Range&lt;int&gt; r in range) { int a = r.A; int b = r.B; p = p.Or(ø =&gt; selector(ø) &gt;= a &amp;&amp; selector(ø) &lt;= b); } return p; } </code></pre> <p>Problem here, is that it crashes with a NotSupportedException because of the selector(ø) call: <code>Method 'System.Object DynamicInvoke(System.Object[])' has no supported translation to SQL.</code></p> <p>I guess that is understandable. But is there any way to get around this? What I would like to end up with is so that I could just do:</p> <pre><code>var stuff = datacontext.Stuffs.Where(selectedRange.ToPredicate&lt;Stuff&gt;(ø =&gt; ø.Id)); </code></pre> <p>Or even better, create something that returns an IQueryable so that I could just do:</p> <pre><code>var stuff = datacontext.Stuffs.WhereWithin&lt;Stuff&gt;(selectedRange, ø =&gt; ø.Id); // Possibly without having to specify Stuff as type there... </code></pre> <p>So, any ideas? I would really like to get this working, cause if not I will get A LOT of those foreach blocks of code, creating predicates...</p> <hr> <p><strong>Note 1:</strong> Of course, would be nice if I could expand to more than int, like DateTime and such, but not sure how that ends up with using the >= and &lt;= operators... Does CompareTo work with linq-to-sql? If not there is no problem creating two. One for int and one for DateTime, since that is mostly the types this will be used for.</p> <p><strong>Note 2:</strong> It is going to be used for reporting, where the user is going to be able to narrow down what comes out, based on different things. Like, I want this report for those people and those dates.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USSvish
    1. USSvish
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POC#, Linq2SQL: Creating a predicate to find elements within a number of ranges
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POC#, Linq2SQL: Creating a predicate to find elements within a number of ranges
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POC#, Linq2SQL: Creating a predicate to find elements within a number of ranges
      1. This table or related slice is empty.
      1. VTUpMod
    1. This table or related slice is empty.
 

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