0

I'm with a very strange problem.

I've a table with more than 800.000 records and 2GB mdf Database.

When I try to get the last records:
I'm only getting the records until one month ago, the last ones doesn't show up.

Dim Items = From Item In DB.Items _
            Where Item.CatID = CatID _
            Order By Item.PubDate Descending _
            Select Item Take 100

But if I limit the Select to the last IDs, then I get the expected result.

Dim Items = From Item In DB.Items _
            Where Item.CatID = CatID _
            And Item.ID > 600000 _
            Order By Item.PubDate Descending _
            Select Item Take 100

So, what's going on here.
Does Linq have a limit of the records it can query?

InfoStatus
  • 6,983
  • 9
  • 41
  • 53
  • What happens when you run the equivalent query in SQL? Linq2Sql should convert it to something like (Or turn on Linq2Sql tracing to get the exact query: "SELECT TOP 100 * FROM ItemsTable WHERE CatId = ORDER BY PubDate DESC" Does the query return what you expect? Also, just for general smartness, make sure you have a index on CatId and PubDate (DESC) if this query is a common one. – Talljoe Jul 01 '09 at 01:26
  • what's the pubdate of the 100th record in the two scenarios? – Rune FS Jul 01 '09 at 06:09
  • maybe I should have extended that comment a bit. The kind of scenario you describe could be casused by different culture on the platform where the query is running and the culture of the database. (In the case where the date is stored as a date and not a tick in the db) – Rune FS Jul 01 '09 at 06:10

1 Answers1

0

Perhaps this might do it:

Dim Items = From Item In DB.Items _
        Where Item.CatID = CatID _
        Order By Item.PubDate _
        Select Item Take 100

Since you were ordering by Item.PubDate Descending I removed the Descending so that you would take the first 100 records from the earliest records respective of the Item.PubDate field.

Andrew Hare
  • 344,730
  • 71
  • 640
  • 635