Linq to Entities custom ordering via position mapping table

Question

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.Positions.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.Positions, 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!

Answer 1

So after some playing about I have managed to get it working.

ret = ret.GroupJoin(entity.Positions, n => n.id, s => s.itemId, (n, s) => new { n, s })
                    .SelectMany(x => x.n.Positions.DefaultIfEmpty(), (n, s) => new { n, s })
                    .OrderBy(x => x.s.position)
                    .ThenByDescending(x => x.n.n.articleDate)
                    .Select(x => x.n.n);

However, this is still not completely right. I have no way of using only a specific positionid or articleType.

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?

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:

CASE WHEN [position] IS NULL OR shuffleId != 1 THEN 9999 ELSE [position] END

I may have to completely rethink how do this. Don't suppose anyone has any suggestions of alternative methods?

Answer 2

How about:

var query =
   from n in news
   let p = n.Positions.Select(p=>p.Position).FirstOrDefault() ?? 9999
   orderby p, n.ArticleDate
   select n;

Ps. the above assumes there is only 1 position entry for each news ... just seems weird if that were not the case.

---

Solution posted by OP in the comment below:

ret = ret
      .GroupJoin(tse.Positions, n => n.id, s => s.itemId, (n, s) => new { n, s }) 
      .SelectMany(x => x.s.DefaultIfEmpty(), (n, s) => new { n, s }) 
      .OrderBy(x => x.s.position == null || x.s.positionId != positionId ? 9999 : x.s.position) 
      .ThenByDescending(x => x.n.n.articleDate) 
      .Select(x => x.n.n);

Answer 3

All I can to suggest you is use of some stored procedure method - add it to your solution by new Linq-To-SQL class, and then use DataContext to get result of it. In this case you wouldn't use the query to database, and this is good for your solution.

Only problem is that result type of this procedure may be not compatible with your News class May be you will have to write some code to transform one representation to another.

If needed, I can provide some illustrations for my way of workaround.