Background:
Entity Framework 4, with SQL Server 2008
Problem:
I have a table Order
. Each row has a column Timestamp
.
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.
Question:
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 2009-09-24
.
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 First()
instead:
var query = (from oData in db.OrderDatas
where oData.Timestamp <= userTime
orderby oData.Timestamp descending
select oData).First();
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With