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

Question

Given a date, the most efficient way to request the last record before this date is any record that is equal to that date and the next after that date.

It should be functionally equivalent to a query like:

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 it is, this query launches three queries and, apparently, should sort the myDate dataset three times to do this.

Some similar questions:

• How to get 5 records before AND after a record with a specific identifier? (just using two queries)
• How to get records before and after receiving? Not in Linq, and so it's hard for me to use (my team will be annoyed).

Answer 1

" " , , .

, , , , , .: -)

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

, , . , , .

Answer 2

Orders ? , :

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

, :

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

SQL-, . IE: , .

, , :

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];

Answer 3

Other answers like SkipWhile etc. very very slow. Good luck ^^

//Current Record
var query
= (from item in db.Employee
   where item.UserName.Equals(_username)
   select item).SingleOrDefault();

//Next Record
var query
= (from item in db.Employee
   where item.UserName.CompareTo(_username) > 0
   select item).FirstOrDefault();

//Previous Record
var query
= (from item in db.Employee
   where item.UserName.CompareTo(_username) < 0
   orderby item.UserName Descending
   select item).FirstOrDefault();

Answer 4

Almost the same, but the SQL query plan may be different.

var q = 
 from asset in Assets
 where asset.Id == assetID
 select new 
 {
     Previous = asset.Orders.where(a => a.Date == asset.Orders.Where(x => x.Date < myDate).Max(x => x.Date)).FirstOrDefault(),
     Current = asset.Orders.Where(x => x.Date == myDate).FirstOrDefault(),
     Next = asset.Orders.where(a => a.Date == asset.Orders.Where(x => x.Date > myDate).Min(x => x.Date)).FirstOrDefault()
 };