In Entity Framework, just the first row of a table is returned.

c# entity-framework select sql-server-2008

Question

Background:

SQL Server 2008 and Entity Framework 4

Problem:

The table I haveOrder There is a column in each row.Timestamp .

The user may choose a period of time in the past, and I must get theOrder closest to the time, however that had already passed when the moment came. In other words, the final order placed before to the deadline.

As an 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 date2010-07-22 I should purchase the2009-09-24 order since it is the final order placed before the deadline.

var query = (from oData in db.OrderDatas
            where oData.Timestamp <= userTime
            orderby oData.Timestamp ascending
            select oData).Last();

The closest to what I'm attempting is this. But if the Last operator is translated at all, I'm not sure how it works precisely in SQL.

Question:

Does this query return all data (before touserTime ) and extract the last element, or will it be translated such that the database returns only one element? Performance is a problem since my table has a very huge capacity (100000+ rows).

Additionally, how might the database's closest time (not necessarily the earlier time) be retrieved? As in the case of2010-07-22 one would acquire2010-11-02 due to the fact that it is closer to the designated date than2009-09-24 .

1
6
12/15/2011 9:52:53 AM

Accepted Answer

In general, you should look at what happens with the SQL if you're worried about how LINQ behaves. The very next thing you should do is figure out how to see how your LINQ queries are converted into SQL, if you haven't already.

Last() is not a feature of LINQ to SQL., as you said in your response, may apply to EF as well. Thankfully, it's simple to use.First() instead:

var query = (from oData in db.OrderDatas
             where oData.Timestamp <= userTime
             orderby oData.Timestamp descending
             select oData).First();
13
12/15/2011 7:57:22 AM

Popular Answer

Use this:

var query = (from oData in db.OrderDatas
         where oData.Timestamp <= userTime
         orderby oData.Timestamp descending
         select oData).Take(1);

It's on par with TOP 1.



Related Questions





Related

Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow
Licensed under: CC-BY-SA with attribution
Not affiliated with Stack Overflow