Entity Framework 4, with SQL Server 2008
I have a table
Order. Each row has a column
The user can choose some time in past and I need to get the
Order closest to the specified time, but that had occurred before the specified time. In other words, the last order before the specified time.
For example, if I have orders
2008-01-12 2009-04-17 2009-09-24 2010-11-02 2010-12-01 2011-05-16
and choose a date
2010-07-22, I should get the
2009-09-24 order, because that's the last order before the specified date.
var query = (from oData in db.OrderDatas where oData.Timestamp <= userTime orderby oData.Timestamp ascending select oData).Last();
This is closest to what I am trying. However, I am not sure how exactly does the Last operator work when translated to SQL, if it's translated at all.
Will this query fetch all data (earlier than
userTime) and then take the last element, or will it be translated so that only one element will be returned from the database? My table can hold very large number of rows (100000+) so performance is an issue here.
Also, how would one retrieve the closest time in the database (not necessarily the earlier time)? In the example of
2010-07-22, one would get
2010-11-02, because it is closer to the date specified than
In general, if you're concerned about how LINQ behaves, you should check what does happen with the SQL. If you haven't worked out how to see how your LINQ queries are turned into SQL, that should be the very next thing you do.
As you noted in your comment,
Last() isn't supported by LINQ to SQL so the same may be true for EF. Fortunately, it's easy to use
var query = (from oData in db.OrderDatas where oData.Timestamp <= userTime orderby oData.Timestamp descending select oData).First();
var query = (from oData in db.OrderDatas where oData.Timestamp <= userTime orderby oData.Timestamp descending select oData).Take(1);
It's the equivalent of TOP 1