locked
Linq to Entities custom ordering via position mapping table RRS feed

  • Question

  • Hi,

     

    So I would like to implement custom ordering to results using linq to entities. Usually I would use a mapping table which has itemIds and a position.

     

    I then LEFT OUTER JOIN and order by position, itemDate.

     

    So far using linq I have:

     

    query.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);

     

    Which kind of works. However, I have a number of collections for different pages identified by a collectionId. Using linq, how can select only the collection I want?

     

    In SQL, I would use a case in my select statement:

     

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

     

    Can this be replicated in linq to entities? If not, do you have any suggestions for implementing custom ordering?

     

    Thanks

    Wednesday, April 28, 2010 7:41 AM

Answers

  • Hello,

    Yes, you should be able to use things like the conditional and coalesce operators in the OrderBy part of your query to expressions something like that, i.e. (just guessing some things about your model):

         .OrderBy(x => x.s.position == null || x.s.collectionId != 1? 9999: x.s.position)

    When we translate that to SQL we will generally produce a nested query in which we will project your ORDER BY term.

    Hope this helps,
    Diego


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Thursday, May 6, 2010 5:36 AM