none
LINQ - orderby value in another table RRS feed

  • Question

  • I'm not the best with sql and i'm just beginning to learn linq. I have three tables like this:
    Quote:
    ----------
    polls
    ----------
    pid (pollId)
    uid (userId)
    eid (eventId)
    title
    question

    ---------
    answers
    ---------
    aid
    pid
    vote

    --------
    events
    --------
    eid
    title
    desc
    This is what i'm trying to achieve: Get all rows from polls where poll.eid = event.eid order by total votes. In order to do this, for each poll I must count all the rows in the answers table which reference the pid (this will equal the total number of votes in the poll). I then must order the polls based on total number of votes found in the previous query. I'm trying to do this in the most efficient way, i'd appreciate your help in understanding how to create this query because right now it seems a little overwhelming to me.

    My current code:
    Code:
    var eventId = from events in db.Events
    where events.name.StartsWith(eventString)
    select events.eid;

    int id = (int)eventId.FirstOrDefault();

    var matchingPolls =
    from poll in db.Polls
    where poll.eid == id
    select poll;
    This works in a simplistic form but what I would like to do is add an orderby constraint based on the answers table as described above. I also need it to be as efficient as possible so this may mean reducing it to a single query if possible.

    Thanks for your help!
    Tuesday, December 15, 2009 5:53 PM

Answers

  • Hello,

    Welcome to LINQ to SQL forum!

    If I understand the problem correctly, there should be a foreign key between the answers and polls tables, correct?  So there should be a navigation property inside the poll entity that contains the answers collection.   If so, you can refer to the following query, I used the lambda expressions.

    ===================================================================
    var query = (from poll in db.Polls
                             join events in db.Events on poll.eid equals events.eid where events.name.StartsWith(eventString)
                             join answers in db.Answers on poll.pid equals answers.pid
                             select new
                             {
                                  poll.pid,
                                  poll.eid,
                                  votes = poll.answers.Count,
                                  eventName = events.name,
                                  poll.choices,
                                  poll.code,
                                  poll.question,
                                  poll.title,
                                  poll.url,
                                  userId = poll.UserId
                             }).OrderBy(p => p.votes);
    ===================================================================

    Also, if the property name in the anonymous type is the same as the value’s property name, it can be omitted. 

     

    Have a nice weekend!


    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Saturday, December 19, 2009 12:20 PM
    Moderator

All replies

  • Ok here I tried a join I feel like i'm getting closer but it is still not right.  Count doesn't work in this setup and I don't think it would be looping through all the answers to count the total votes for each poll anyways.  Any suggestions from here? I've never tried joins in linq before and have very little experience with joins in sql.

    [CODE]var query = from poll in db.Polls
                            join events in db.Events on poll.eid equals events.eid where events.name.StartsWith(eventString)
                            join answers in db.Answers on poll.pid equals answers.pid
                            select new
                            {
                                pid = poll.pid,
                                eid = poll.eid,
                                votes = answers.Count,
                                eventName = events.name,
                                choices = poll.choices,
                                code = poll.code,
                                question = poll.question,
                                title = poll.title,
                                url = poll.url,
                                userId = poll.UserId
                            };[/CODE]
    Tuesday, December 15, 2009 7:07 PM
  • Hello,

    Welcome to LINQ to SQL forum!

    If I understand the problem correctly, there should be a foreign key between the answers and polls tables, correct?  So there should be a navigation property inside the poll entity that contains the answers collection.   If so, you can refer to the following query, I used the lambda expressions.

    ===================================================================
    var query = (from poll in db.Polls
                             join events in db.Events on poll.eid equals events.eid where events.name.StartsWith(eventString)
                             join answers in db.Answers on poll.pid equals answers.pid
                             select new
                             {
                                  poll.pid,
                                  poll.eid,
                                  votes = poll.answers.Count,
                                  eventName = events.name,
                                  poll.choices,
                                  poll.code,
                                  poll.question,
                                  poll.title,
                                  poll.url,
                                  userId = poll.UserId
                             }).OrderBy(p => p.votes);
    ===================================================================

    Also, if the property name in the anonymous type is the same as the value’s property name, it can be omitted. 

     

    Have a nice weekend!


    Best Regards,
    Lingzhi Sun


    Please remember to mark the replies as answers if they help and unmark them if they provide no help.
    Welcome to the All-In-One Code Framework! If you have any feedback, please tell us.
    Saturday, December 19, 2009 12:20 PM
    Moderator