Linq to Entities custom ordering via position mapping table
- by Bigfellahull
Hi,
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.
I then LEFT OUTER JOIN the position table ON news.newsId = position.itemId with a select case statement
CASE WHEN [position] IS NULL THEN 9999 ELSE [position] END
and order by position asc, articleDate desc.
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.
Now comes the bit I can't work out. How to implement the LEFT OUTER JOIN.
I have so far:
var query = SelectMany (n => n.Positions, (n, s) => new { n, s })
.OrderBy(x => x.s.position)
.ThenByDescending(x => x.n.articleDate)
.Select(x => x.n);
This kinda works. However this uses a INNER JOIN so not what I am after.
I had another idea:
ret = ret.OrderBy(n => n.ShufflePositions.Select(s => s.position));
However I get the error DbSortClause expressions must have a type that is order comparable.
I also tried
ret = ret.GroupJoin(tse.ShufflePositions, n => n.id, s => s.itemId, (n, s) => new { n, s })
.OrderBy(x => x.s.Select(z => z.position))
.ThenByDescending(x => x.n.articleDate)
.Select(x => x.n);
but I get the same error!
If anyone can help me out, it would be much appreciated!
