Implementing Next/Previous with LINQ to SQL

Question

I have the code below in an attempt to allow the user to "Step Through" the Case Notes in the DB by clicking Next or Previous on the WinForm. It will grab the First Case Note only. What am I doing wrong?

There has been numerous edits to this post, I apologize, but in following Jon Skeet's advice I was able to "fix" what was originally wrong but it still doesn't work.

Do I need to restructure my query to take into account the current note? If so, how do I do that?

    public static Guid NextCaseNoteID (int personID)
    {
        var context = new MatrixDataContext();

        Guid nextNoteID = (from caseNote in context.tblCaseNotes
                                                   where caseNote.PersonID == personID
                                                   orderby caseNote.InsertDate
                                                   select caseNote.CaseNoteID ).Skip(1).FirstOrDefault();

        return nextNoteID;
    }

---

---

This is what I ended up with, thanks to everyone who posted and followed my ill thought train of thought...

It seems to work well though I am now trying to prove if I need the Skip(1) still.... Thanks!!

for future reference

        public static Guid NextCaseNoteID (int personID, DateTime? insertDate)
    {
        var context = new MatrixDataContext();

        Guid nextNoteID = (from caseNote in context.tblCaseNotes
                                                   where caseNote.PersonID == personID && caseNote.InsertDate > insertDate
                                                   orderby caseNote.InsertDate
                                                   select caseNote.CaseNoteID ).Skip(1).FirstOrDefault();

        return nextNoteID;
    }

    public static Guid PreviousCaseNoteID(int personID, DateTime? insertDate)
    {
        var context = new MatrixDataContext();

        Guid nextNoteID = (from caseNote in context.tblCaseNotes
                           where caseNote.PersonID == personID && caseNote.InsertDate < insertDate
                           orderby caseNote.InsertDate
                           select caseNote.CaseNoteID).Skip(1).FirstOrDefault();

        return nextNoteID;
    }

Answer 1

Well, the most obvious problem is that you're creating an instance of an anonymous type. Try this:

public static IQueryable<Guid> NextCaseNoteID (int personID)
{
    var context = new MatrixDataContext();

    IQueryable<Guid> nextNoteID = (from caseNote in context.tblCaseNotes
                                   where caseNote.PersonID == personID
                                   orderby caseNote.InsertDate
                                   select caseNote.CaseNoteID).Skip(1).Take(1);

    return nextNoteID;
}

I'm not at all sure that it's really what you're after, but it's likely to at least compile...

Are you sure you don't want to return the actual GUID instead of an IQueryable<Guid>?

You might want to call FirstOrDefault() instead of Take(1)...

EDIT: Okay, so it does return a GUID... you say it's not working, but not how it's not working. If you want to fetch the next case note, you should quite possibly pass in the case note ID rather than the person ID, but it's not terribly clear...

Answer 2

I might sound like an idiot. But, why can't you use a datareader to do this thing (esp. when you are using linq to sql, which in turn calls SQL Server to do this)?

Let me know, if I have totally misunderstood the problem.

Answer 3

If want a next/previous method, shouldn't you be informing what is the current position now? And then skipping currentPosition + 1 for next and currentPosition - 1.

I usually uses Single() instead of FirstOrDefault(). But I think it will make no difference.