Databases
StackOverflow2013
Postsdbo
POLinq to Entities custom ordering via position mapping table
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POLinq to Entities custom ordering via position mapping table
    primarykey
    data
    text
    <p>I have a news table and I would like to implement custom ordering. I have done this before via a positional mapping table which has newsIds and a position.</p> <p>I then LEFT OUTER JOIN the position table ON news.newsId = position.itemId with a select case statement</p> <pre><code>CASE WHEN [position] IS NULL THEN 9999 ELSE [position] END </code></pre> <p>and order by position asc, articleDate desc.</p> <p>Now I am trying to do the same with Linq to Entities. I have set up my tables with a PK, FK relationship so that my News object has an Entity Collection of positions.</p> <p>Now comes the bit I can't work out. How to implement the LEFT OUTER JOIN.</p> <p>I have so far:</p> <pre><code> var query = SelectMany (n =&gt; n.Positions, (n, s) =&gt; new { n, s }) .OrderBy(x =&gt; x.s.position) .ThenByDescending(x =&gt; x.n.articleDate) .Select(x =&gt; x.n); </code></pre> <p>This kinda works. However this uses a INNER JOIN so not what I am after.</p> <p>I had another idea:</p> <pre><code> ret = ret.OrderBy(n =&gt; n.Positions.Select(s =&gt; s.position)); </code></pre> <p>However I get the error <strong><em>DbSortClause expressions must have a type that is order comparable.</em></strong></p> <p>I also tried </p> <pre><code>ret = ret.GroupJoin(tse.Positions, n =&gt; n.id, s =&gt; s.itemId, (n, s) =&gt; new { n, s }) .OrderBy(x =&gt; x.s.Select(z =&gt; z.position)) .ThenByDescending(x =&gt; x.n.articleDate) .Select(x =&gt; x.n); </code></pre> <p>but I get the same error!</p> <p>If anyone can help me out, it would be much appreciated! <br /><br /> </p> <p><strong>UPDATE:</strong><br> So after some playing about I have managed to get it working.</p> <pre><code>ret = ret.GroupJoin(entity.Positions, n =&gt; n.id, s =&gt; s.itemId, (n, s) =&gt; new { n, s }) .SelectMany(x =&gt; x.n.Positions.DefaultIfEmpty(), (n, s) =&gt; new { n, s }) .OrderBy(x =&gt; x.s.position) .ThenByDescending(x =&gt; x.n.n.articleDate) .Select(x =&gt; x.n.n); </code></pre> <p>However, this is still not completely right. I have no way of using only a specific positionid or articleType.</p> <p>If I have news id 1 and a review id but defined in the positions table, currently (I think) the linq query would select both?</p> <p>If I try and use a where clause, it's basically the same as inner joining. What I need is to try and use a case in the select like I would do in straight SQL:</p> <pre><code>CASE WHEN [position] IS NULL OR shuffleId != 1 THEN 9999 ELSE [position] END </code></pre> <p>I may have to completely rethink how do this. Don't suppose anyone has any suggestions of alternative methods?</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. UST.Rob
    1. USJames Hull
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POLinq to Entities custom ordering via position mapping table
      1. This table or related slice is empty.
      1. VTUpMod
    2. VO
      singulars
      1. POLinq to Entities custom ordering via position mapping table
      1. USJames Hull
      1. VTBountyStart
    3. VO
      singulars
      1. POLinq to Entities custom ordering via position mapping table
      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