none
Help with aggregates and joins RRS feed

  • Question

  • Hello,

     

    I need a query that will have joins and aggregates in it. Let me explain the structures of the tables that the query will run on.

     

    There are 2 tables, one is called Entries and the other is Ratings.

     

    The fields of the Entries table are as follows:

    - Id, int

    - FriendlyUrl, nvarchar(250)

    - Title

    ....

     

    And the Ratings table is as follows:

    - Id, int

    - RatingKey (will be equal to FriendlyUrl field value of the Entries table)

    - Rate, int

    ...

     

    Ratings and Entries tables can be joined with the help of the FriendlyUrl-RatingKey columns. I didn't use ID columns and foreign key relationships because in the full database model, lots of different entities can be rated (like entries, news, even users)

     

    I need a query that will sort all the entries according to their scores (sum of all the Rate fields for the entry) but I couldn't manage to create the query in Linq. Can anyone help me?

     

    Thanks.

    Wednesday, August 6, 2008 7:59 PM

Answers

  • Hi Aimee

    The following should do the trick:

    from e in db.Entries
    orderby db.Ratings.Sum (r => e.FriendlyUrl == r.RatingKey)
    select e

    Or, if you want to see the total scores in the output:

    from e in db.Entries
    let score = db.Ratings.Sum (r => e.FriendlyUrl == r.RatingKey)
    orderby score
    select new
    {
    e.Title,
    e.FriendlyUrl,
    score
    }

    Joe
    Thursday, August 7, 2008 3:23 AM
    Answerer

All replies

  • Hi Aimee

    The following should do the trick:

    from e in db.Entries
    orderby db.Ratings.Sum (r => e.FriendlyUrl == r.RatingKey)
    select e

    Or, if you want to see the total scores in the output:

    from e in db.Entries
    let score = db.Ratings.Sum (r => e.FriendlyUrl == r.RatingKey)
    orderby score
    select new
    {
    e.Title,
    e.FriendlyUrl,
    score
    }

    Joe
    Thursday, August 7, 2008 3:23 AM
    Answerer
  • Great! Thank you. Smile

     

    Thursday, August 7, 2008 5:35 AM