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!