In Linq2SQL, how do I get a record plus the previous and next in the sequence in a single query?

Question

Given a date, what is the most efficient way to query the last record before that date, any record that equals that date, and the next one after that date.

It should be functionally equivalent to a query like this:

from asset in Assets
where asset.Id == assetId
select new {
    Previous = (from a in a.Orders where a.Date < myDate orderby a.Date descending select a).FirstOrDefault(),
    Current = (from a in a.Orders where a.Date == myDate select a).SingleOrDefault(),
    Next = (from a in a.Orders where a.Date > myDate orderby a.Date select a).FirstOrDefault()
}

As is, this query runs three queries, and presumably has to sort the dataset by myDate three times to do it.

Some similar questions:

• How do I get 5 records before AND after a record with a specific ID? (just uses two queries)
• How do I get records before and after given one? Not in Linq, and therefore hard for me to take advantage of (my team will get annoyed).

Answer 1

To provide the "most efficient" query depends on what you mean by efficient.

If you want a single query to the database, a single sort of orders by date and finally fast look-ups by date then I suggest the following might be the most efficient. :-)

var orders =
    (from a in Assets
     where a.Id == assetId
     from o in a.Orders
     orderby o.Date
     select o).ToArray();

var previous = orders.LastOrDefault(o => o.Date < myDate);
var current = orders.SingleOrDefault(o => o.Date == myDate);
var next = orders.FirstOrDefault(o => o.Date > myDate);

This should query the database once for the orders associated with the required asset Id, sort them by date, and return them as an array in memory. Since this is in memory it is now blindingly fast to look for the current, previous & next records for the specified date.

Answer 2

Does your Orders table have a sequential ID field? If so, you might be able to do it with:

from asset in Assets
where asset.Id == assetID
let current = asset.Orders.Where(x => x.Date == myDate).FirstOrDefault()
where current != null
let previous = asset.Orders.Where(x => x.id == current.id - 1).FirstOrDefault()
let next = asset.Orders.Where(x => x.id == current.id + 1).FirstOrDefault()
select new {
    Previous = previous,
    Current = current,
    Next = next
};

If it doesn't, then it'd be a bit more code:

from asset in Assets
where asset.Id == assetID
let current = asset.Orders.Where(x => x.Date == myDate).FirstOrDefault()
where current != null
let previous = asset.Orders.Where(x => x.Date < current.Date).OrderByDescending(x => x.Date).FirstOrDefault()
let next = asset.Orders.Where(x => x.Date > current.Date).OrderBy(x => x.Date).FirstOrDefault()
select new {
    Previous = previous,
    Current = current,
    Next = next
};

That should get compiled into a single SQL query that utilizes sub-queries. IE: the database server will execute multiple queries, but your client program is only submitting one.

Edit One other idea that would work if your Order table had sequential IDs:

var sample = (from asset in Assets
              where asset.Id == assetID
              let current = asset.Orders.Where(x => x.Date == myDate).FirstOrDefault()
              where current != null
              from order in asset.Orders
              where order.Id == current.id - 1
              select order)
             .Take(3)
             .ToArray();

var Previous = sample[0];
var Current = sample[1];
var Next = sample[2];