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


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.


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();  


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);  }  

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »