none
How do i do this OrderBy, in Linq2Sql? RRS feed

  • Question

  • Hi folks,

        I'm trying to retrieve some records for a table, but order those results by a field in a child/aggregation table.

     

    Bascially, 10 board posts but order by the most recent comment.

     

    here's the sql:

     

    Code Snippet

    select TOP(10) a.* from tblBoardPost a inner join tblBoardComment b on a.IdBoardPost = b.IdBoardPost

    order by b.DateModified desc

     

     

     

    cheers Smile

    Friday, July 11, 2008 3:15 AM

Answers

  • Oh, sorry I didn't spot this before. The orderby in LINQ (and SQL) defaults to ascending, so you are seeing the posts with the oldest comments. Add another "descending" keyword to the LINQ query - there should be two:

     

    var posts =
     (
    from b in ctx.BoardPosts
      
    orderby (from c in b.Comments
               orderby c.DateModified descending
               select c.DateModified).First() descending
      
    select b).Take(10);

     

    Does that work?

    Monday, July 14, 2008 6:40 AM

All replies

  • Is it OK for a record from tblBoardPost to appear more than once in the output? That's what your INNER JOIN will do in the SQL you've posted. You'll need a GROUP BY or sub-SELECT to remove duplicate posts.

     

    In LINQ, a query that would return 10 distinct posts ordered by most recent comment would look something like the following (depending on your L2S mappings).

     

     

    var posts =
     (
    from b in ctx.BoardPosts
      
    orderby (from c in b.Comments orderby c.DateModified descending select c.DateModified).First()
      
    select b).Take(10);
    Friday, July 11, 2008 4:43 AM
  • Another approach would be to order by the max DateModified from the subselect.

     

    Something similar to this post.

     

    -Larry

     

    Friday, July 11, 2008 10:15 AM
  • *dang* lost my big post. Ok, take 2 (and shorter version)

     

    Doesn't work for me Sad

     

    I'm getting results, but they are not correct .. or at least what i'm assuming is correct.

     

    Here's the sql the linq creates...

     

    Code Snippet

    SELECT [t0].[IdBoardPost], .. SNIP SNIP SNIP ... [t0].[DateModified]

    FROM [dbo].[tblBoardPost] AS [t0]

    ORDER BY (

        SELECT TOP (1) [t1].[DateModified]

        FROM [dbo].[tblBoardComment] AS [t1]

        WHERE [t1].[IdBoardPost] = [t0].[IdBoardPost]

        ORDER BY [t1].[DateModified] DESC

    )

     

     

     

     

    tblBoardPost is a one to many relationship with tblBoardComment.

     

    To confirm the results, i select all most recent comments from tblBoardComment, and manually compare that IdBoardPost to the linq results  and they are not the same.

     

    i just don't get it Sad
    Monday, July 14, 2008 1:46 AM
  • Could you post some sample data and what you want the query to return from that sample data? I might not understand what you are trying to do, as the SQL seems reasonable.  

     

    For example:

     

    Posts

    ID    PostTitle

    ---------------------

    1     Post 1

    2     Post 2

    3     Post 3

     

    Comments

    ID    PostID DateModified   

    --------------------------------------

    1     1      1/1/2008         

    2     1      2/1/2008

    3     2      3/1/2008

    4     2      4/1/2008

    5     3      5/1/2008

    6     2      6/1/2008

     

    That SQL should give you:

     

    PostID    PostTitle

    2         Post 2 <- most recent comment

    3         Post 3

    1         Post 1 <- oldest comment

    Monday, July 14, 2008 3:39 AM
  • Scott, your results are EXACTLY correct ... so your assumption is correct.

     

    Here are my sql results (what i expect .. and please excuse the duplicates .. i've just kept my query simple) and then the results i'm getting..

    Code Snippet

     

    SELECT a.IdBoardPost, a.DateModified, b.DateModified

    FROM tblBoardPost a INNER JOIN tblBoardComment b ON a.IdBoardPost = b.IdBoardPost

    ORDER BY b.DateModified DESC

     

     

     

    IdBoardPost PostDateModified        CommentDateModified

    ----------- ----------------------- -----------------------

    195         2008-07-14 11:56:31.577 2008-07-14 11:56:31.577  <-- *

    193         2008-07-14 11:25:39.900 2008-07-14 11:53:44.110  <-- *

    194         2008-07-14 11:50:49.913 2008-07-14 11:50:49.913  <-- *

    193         2008-07-14 11:25:39.900 2008-07-14 11:25:39.947  <-- *

    183         2008-06-23 17:02:11.147 2008-07-11 17:18:12.283  <-- ###

    183         2008-06-23 17:02:11.147 2008-07-11 17:16:34.533  <-- ###

    183         2008-06-23 17:02:11.147 2008-07-11 17:16:06.517   ...

    183         2008-06-23 17:02:11.147 2008-07-11 16:26:50.963   ..

    170         2008-06-12 16:37:16.670 2008-07-11 11:54:59.967   . etc

    192         2008-07-10 11:15:30.980 2008-07-10 11:15:31.010

    122         2007-10-05 05:05:27.250 2008-07-09 17:42:00.850

    191         2008-07-04 10:27:16.680 2008-07-04 11:35:49.070

     

    * = New post + a comment, ASAP

    ### = old post with a new comment.

     

    and result query and results

    Code Snippet

    SELECT [t0].[IdBoardPost],[t0].[DateModified]

    FROM [dbo].[tblBoardPost] AS [t0]

    ORDER BY (

    SELECT TOP (1) [t1].[DateModified]

    FROM [dbo].[tblBoardComment] AS [t1]

    WHERE [t1].[IdBoardPost] = [t0].[IdBoardPost]

    ORDER BY [t1].[DateModified] DESC

    )

     

     

     

    IdBoardPost PostDateModified

    ----------- -----------------------

    56          2007-08-24 09:33:58.187

    120         2007-10-04 01:11:55.060

    2           2007-03-28 11:57:05.123

    3           2007-03-28 21:42:08.187

    5           2007-03-29 23:33:58.687

    7           2007-04-02 03:51:28.593

    9           2007-04-06 05:55:59.950

    4           2007-03-29 04:10:16.090

    11          2007-04-16 04:43:11.653

    15          2007-04-17 17:29:33.280

    10          2007-04-06 06:03:15.967

     

     

    Sad

     

    EDIT: cleaned up 1st result grid for readability

    Monday, July 14, 2008 4:05 AM
  • Oh, sorry I didn't spot this before. The orderby in LINQ (and SQL) defaults to ascending, so you are seeing the posts with the oldest comments. Add another "descending" keyword to the LINQ query - there should be two:

     

    var posts =
     (
    from b in ctx.BoardPosts
      
    orderby (from c in b.Comments
               orderby c.DateModified descending
               select c.DateModified).First() descending
      
    select b).Take(10);

     

    Does that work?

    Monday, July 14, 2008 6:40 AM
  • ~ ~ W I N N A H ~ ~

     

    Please pass GO and collection $200.

     

    cheers mate! love ya work.

     

    -me-

    Monday, July 14, 2008 7:00 AM