How do I get the next item in a subset of an ordered table using linq?

Question

I have two tables:

Topic (
  TopicID: int, (primary key)
  TopicName: varchar
);

Example (
  ExampleID: int, (primary key)
  TopicID: int, (foreign key to Topic table)
  ExampleOrder: int,
  ExampleName: varchar
);

Using Linq, given an ExampleID, I want to get the next example in the same topic (with the same TopicID). Here's how it might be done in sql:

DECLARE @ExampleOrder int;
DECLARE @TopicID int;
SELECT @ExampleOrder=ExampleOrder, @TopicID=TopicID FROM Example WHERE ExampleID=@ExampleID;
SELECT TOP 1 ExampleID FROM Example WHERE TopicID=@TopicID AND ExampleOrder>@ExampleOrder ORDER BY ExampleOrder

Is there a simple way to do this in Linq? I know I can do this with two Linq queries, but I'm trying to get it done without extra round trips to the database.

Answer 1

int exampleID = 5;
  //now to write a query
var query =
  from ex in db.Examples
  let prior = db.Examples.Where(ex2 => ex2.ExampleID == exampleID).First()
  where ex.TopicID == prior.TopicID
     && ex.ExampleOrder > prior.ExampleOrder
  order ex by ex.ExampleOrder descending
  select ex
    //ok, let's run the query - result will have 0 or 1 item in it.
List<Example> result = query.Take(1).ToList();

Answer 2

public Example GetNextExample(IEnumerable<Example> examples, Int32 exampleID)
{
   Example example = examples.Single(e => e.ExampleId == exampleID);

   return examples.
      Where(e => e.TopicID == 
         examples.Single(e => e.ExampleId == exampleID).TopicID).
      OrderBy(e => e.ExampleOrder).
      FirstOrDefault(e => e.ExampleOrder > 
         examples.Single(e => e.ExampleId == exampleID).ExampleOrder);
}