Databases
StackOverflow2013
Postsdbo
POHow to preserve Linq2SQL OR between conditions?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POHow to preserve Linq2SQL OR between conditions?
    text
    copied!<p>Lets say we need to select two sets from a table: "Things"</p> <pre><code>var GradeA = db.Things.Where(t=&gt; condition1); var GradeB = db.Things.Where(t=&gt; !condition1 &amp;&amp; condition2); var DesiredList = GradeA.union(GradeB); </code></pre> <p>alternatively, we need to write a single statement to avoid <code>union</code> cost:</p> <pre><code>var DesiredList = db.Things.Where(t=&gt; condtion1 || (!condition1 &amp;&amp; condition2)); </code></pre> <p>the problem is query optimizer seems to trim the expression to <strong><em>condition2</em></strong> only.</p> <p>How to preserve the priority between <strong><em>condition1</em></strong> and <strong><em>condition2</em></strong></p> <p>a real life example workaround is :</p> <pre><code>/// &lt;summary&gt; /// Gets only first BookTag for each tag word, chooses the one of this user (if exists). /// &lt;/summary&gt; /// &lt;param name="book"&gt;&lt;/param&gt; /// &lt;returns&gt;&lt;/returns&gt; public static IQueryable&lt;BookTag&gt; UniqueByTags(this IQueryable&lt;BookTag&gt; bookTags, User user) { return bookTags.GroupBy(BT =&gt; BT.TagId) .Select(g =&gt; g.Any(bt =&gt; bt.UserId == user.Id) ? new BookTag() { User = user, Tag = g.First().Tag, Book = bookTags.First().Book } : new BookTag() {User = g.First().User, Tag = g.First().Tag, Book = bookTags.First().Book} ); } </code></pre> <h2>Edit:</h2> <p>Example is getting Auto-Complete list:</p> <ul> <li>input: <code>str</code></li> <li>output: things that start with <code>str</code> and things that contain <code>str</code> (no duplicates)</li> </ul> <hr> <p>Another Example: Selecting <code>ThingTags</code> which have 3 properties:</p> <ul> <li><code>ThingID</code></li> <li><code>UserID</code></li> <li><code>TagID</code></li> </ul> <p>we want to select only one <code>ThingTag</code> for each <code>TagID</code> under condition that we select the one with <code>UserID</code> equals parameter if <strong>exists</strong>, otherwise select first <code>ThingTag</code> for that <code>TagID</code>.</p> <p>Still with me ? hope so :)</p> <hr>
 

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